Mobile Industry

BI Tips: LookUp Functions

By Ashia Coleman Sep 25, 2015

LookUp functions can be used to cross reference information across multiple datasets. These expressions cannot be used in the query designer, only in a tablix expression.

For example, let’s say you have two datasets:

  • Your tablix uses Dataset1 to gather its data, but you’d like to add Product Cost as a column. Since Product Cost lives in another dataset, you would have to use a lookup function to accomplish this.
  • We will call the dataset being used by your report “Home” and the other dataset “Away.”
  • If you don’t know which dataset your table is using, right-click on your table > Tablix Properties
  • In order to use a lookup function, you must have a common field (or key) on which to join the information.
  • In the example above, Product SKU is present in both datasets and can be used as our “key.”

Lookup expressions are written with the following syntax:

     =LookUp( HomeKey, AwayKey, AwayValue, “AwayDatasetName”)

My expression would be:

     =LookUp( Fields!ProductSKU.value, Fields!ProductSKU.value, Fields!ProductCost.value, “Dataset2”)

For more information on this topic: http://technet.microsoft.com/en-us/library/ee210531.aspx

Mobile Industry Business Intelligence