LOOKUPROW Function

LOOKUPROW looks up a value in a row of another result set using a specific row index, and returns the value in a specified column in that row.

LOOKUPROW(index_of_row_to_return, index_of_column_to_return, optional_parameter, optional_parameter,... , BQL_query)

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.

Note: LOOKUPROW 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 LOOKUPROW.

Example of LOOKUPROW function using a parameter

In this example, to return the overall quantity per year, and the top category and its quantity for each year, the first measure is Quantity and the first attribute is Time.Year. The top category per year is a result of this LOOKUPROW attribute expression that uses a query parameter, Time.Year :

LOOKUPROW(0,0,[Time.Year], SELECT [Products.Categories], [Order_Date: Sum: Quantity] FROM [ALL] WHERE [Time.Year]=%0 ORDER BY [Order_Date: Sum: Quantity] DESC)

In the preceding LOOKUPROW example, the first 0 indicates that it is returning an item from the first row in the lookup query. The 2nd 0 refers to which column from the lookup query to return, in this case it is the first column so the expression returns [Products.Categories]. [Time.Year] is a parameter being passed from the main query. Note that in the lookup query the data has been ordered by the Sum of Quantity descending, so the Category with the largest quantity will be the first row. In summary, this expression returns the Product Category with the largest Quantity for each year in the main query.

The top quantity of the top category per year uses the following LOOKUPROW measure expression. The difference is that the measure comes first in the lookup query.

LOOKUPROW(0,0,[Time.Year], SELECT [Order_Date: Sum: Quantity], [Products.Categories] FROM [ALL] WHERE [Time.Year]=%0 ORDER BY [Order_Date: Sum: Quantity] DESC)

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

SELECT TOP 100 USING OUTER JOIN LOOKUPROW(0,0,[Time.Year], SELECT [Order_Date: Sum: Quantity], [Products.Categories] FROM [ALL] WHERE [Time.Year]=%0 ORDER BY [Order_Date: Sum: Quantity] DESC) 'COL0' , LOOKUPROW(0,0,[Time.Year], SELECT [Products.Categories], [Order_Date: Sum: Quantity] FROM [ALL] WHERE [Time.Year]=%0 ORDER BY [Order_Date: Sum: Quantity] DESC) 'COL1' , [Order_Date: Sum: Quantity] 'COL2' , [Time.Year] 'COL3' FROM [ALL]

Table 1. The Visualizer Report
Year	Quantity of All Categories	LOOKUPROW Attribute - Name of Top Category	LOOKUPROW Measure - Quantity of Top Category
2011	150,028	MP3 Players	36,190
2012	215,021	Mobile Phones	79,330
2013	330,081	Tablets	140,035
2014	249,462	Tablets	106,222
	Σ 944,592		{} 361,777