LOOKUPVALUE Function

The LOOKUPVALUE function looks up a value in a row of another result set by the value of another column in the same row:

LOOKUPVALUE(column_number_from_the_query, [attribute_in_the_current_query|measure_in_the_current_query], column_number_of_the_new_query, optional_parameter, optional_parameter... , SELECT [attribute|measure],[attribute|measure]... FROM [ALL])

Optional parameters can be expressions, constants, columns from the main query, or a combination. Using parameters, the lookup query can depend on expressions derived from the main query itself. This technique allows multi-pass calculations.

Reference each parameter expression in the lookup query using %index syntax. The first parameter is numbered %0, the second %1, etc.

Note: Parameters require the lookup query to be issued for each row in the parent query and can affect performance. Use the display filter DISPLAY WHERE syntax to run these queries against the returned dataset in cache for improved performance.

LOOKUPVALUE can return a value from a data set that does not join with the data in the report. For example, the grayed-out attributes or measures in Designer or Visualizer which do not join with what is already on the report can be accessed with LOOKUPVALUE.

Example of LOOKUPVALUE Function

This example finds all products, with their corresponding category, the quantity sum for each product and a final column using LOOKUPVALUE that is the quantity sum for the entire category. In other words, for each product returned by the main query, the LOOKUPVALUE function returns its category's quantity. The report expression is:

LOOKUPVALUE(0, [Products.Categories], 1, SELECT [Products.Categories], [Order_Date: Sum: Quantity] from [All])

n the preceding LOOKUPVALUE example, the column number from the query is 0, meaning that the it performs the lookup in the first column in the lookup query. The next element is the attribute in the current query, which is [Products.Categories]. The report that this used function has [Products.Categories], and that is what the function looks for. Next is the column number of the new query, which has a value of 1. That means that the 2nd column from the lookup query is returned as the result of this function. In other words, take [Products.Categories] from the report and compare it to the first column in the lookup query, and return the [Order_Date: Sum: Quantity] for that Product Category.

The query for the Visualizer report from Advanced Tools > Advanced Tools > Queries:

SELECT TOP 100 USING OUTER JOIN LOOKUPVALUE(0, [Products.Categories], 1, SELECT
            [Products.Categories], [Order_Date: Sum: Quantity] from [All]) 'COL0' , [Order_Date:
          Sum: Quantity] 'COL1' , [Products.Categories] 'COL2' , [Products.Products] 'COL3' FROM
          [ALL]

Table 1. The Visualizer Report
Categories	Products	Quantity per Product	LOOKUPVALUE Quantity per Category
Desktops	iMango Desktop 22	15,349	74,485
	iMango Desktop 27	12,823	74,485
	Mango Mini 3.0 GHZ	3,348	74,485
	Mango Mini 3.5 GHZ	11,810	74,485
	Mango Server	5,211	74,485
	Mango Workstation	2,355	74,485
	Mango Workstation	10,003	74,485
	Mango Workstation	13,496	74,485
Sub-Summary		Σ 74,485	{} 595,880
MP3 Players	mangoNano 16	24,995	135,397

Tips:

You can use LOOKUPVALUE with packages.

LOOKUPVALUE(0, [PackageDimensionName.ColumnName],1, SELECT[Dimension.ColumnName],[measure] FROM [ALL])