LOOKUPVALUE Function

LOOKUPVALUE 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.

Important: 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. See Display Operations.

Tip: LOOKUPVALUE can return a value from a data set that doesn't join with the data in the report. For example, the grayed-out attributes or measures in Designer or Visualizer that don't join with what is already on the report, can be accessed with LOOKUPVALUE.

Example LOOKUPVALUE Function

The following 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])

In the preceding LOOKUPVALUE example, the column number from the query is 0, meaning that the it will do 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 function will be used in will have [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 will be 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 report, from Visualizer 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]

The Visualizer report:

Tips:

• You can use LOOKUPVALUE with packages.
  LOOKUPVALUE(0, [PackageDimensionName.ColumnName],1, SELECT[Dimension.ColumnName],[measure] FROM [ALL])