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