Categories
SSRS Uncategorised

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”
 We can add more than one expression to a single cell (using right-click -> Create Placeholder), so will add this expression in front of the Sales value.  This results in a Tablix design like this

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

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.