BI Tips: LookUp Functions

Sep 01, 2014 — Ashia Coleman

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

Topics: Mobile Industry, Business Intelligence

Recent Posts

Comments