How to use Lookup in SSRS
You may be faced with a problem similar to this, where the user wants to display a particular currency symbol that is different for each locality. It could be possible to alter the underlying dataset to return this information as well as the actual value, but it is entirely possible from within SSRS, as follows
Assume that we have a DataSet returning this information as DataSetSales
Country Sales --------- ------- Japan 404 UK 44 USA 1255
A good year for sales in America, not so much in the UK, I think we’ll all agree.
We can then create a new DataSet in the Report to return a list of Currencies for Countries, such as Results in this DatasSet – DataSetCurrency
Country Currency --------- ---------- USA $ Japan ¥ UK £
We can then use the LOOKUP function to ‘look up’ the value for the Currency in the Currency DataSet from the table showing the results of the Sales DataSet, as follows
=Lookup(Fields!Country.Value, Fields!Country.Value, Fields!Currency.Value, "DataSetCurrency")
This is saying
=Lookup |
Lookup |
Fields!Country.Value, |
The value of Country from the current dataset (DataSetSAles) |
Fields!Country.Value, |
Find it’s corresponding entry in the dataset I am about to identify (DataSetCurrency) |
Fields!Currency.Value, |
Find the value for the field ‘Currency’ for this Country record in the dataset I am about to identify (DataSetCurrency) |
"DataSetCurrency") |
In the dataset “DataSetCurrency” |
Note that the Blue expression is our Lookup described above
When run, the report renders as
Therefore this is correctly displaying the correct Currency for the correct Country.
You can join all sorts of data together like this – it’s just like using a JOIN in SQL. As long as both DataSets share a column that can be used as a key to get the values you want from the supplementary table (in this case, DataSetCurrency) you can use LOOKUP to join them together.
Leave a Reply