SSRS - Nested Lookup Functions

Sometimes, you have no access rights in the database to create some views or getting the data from the multiple sources, then you have to use available resources/techniques in that specific tools.

You have to create a vendor detailed report in SSRS and you have three datasets for Sales Orders, Customer Info and Vendor Info where Vendor Info is not directly linked with Sales Orders dataset but Sales Orders dataset is linked with Customer Info dataset based on the common field “CustId” and Customer Info dataset is linked with Vendor Info dataset based on the common field “VendorId” as given below:

Orders OrdId OrdDate CustId ItemId ItemQty SalesAmount 201902 1/21/2019 101 201 4 80 201903 2/11/2019 102 202 5 125 201904 2/27/2019 103 204 8 220 201905 3/1/2019 104 201 5 100 201906 3/10/2019 101 206 4 80 Customer Info CustId Name Address VenderId 101 Ryan Arjun Delhi 103 102 Rosy Gray Noida 104 103 John Will Punjab 102 104 Kimmy Wang Mumbai 102 Vendor Info VenderId Name 101 Josh Wilson 102 Robert Ramsay 103 Vibhor McHill 104 Donald Trump

Now, you have to create a report to show the customer name and vendor name as given below

Vendor Details Report OrdId    OrdDate               CustomerName Vendor Name ItemId  Qty               Amt 201902  1/21/2019 Ryan Arjun          Vibhor McHill 201         4              $80.0 201903  2/11/2019 Rosy Gray            Donald Trump 202         5              $125.0 201904  2/27/2019 John Will              Robert Ramsay 204         8              $220.0 201905  3/1/2019 Kimmy Wang     Robert Ramsay 201         5              $100.0 201906  3/10/2019 Robert Ramsay Vibhor McHill 206         4              $80.0

There are very simple way/steps to accomplish these requirements -

Step1:  For example, your first look up will return a vendor ID (dsSalesOrders) from a customer order (dsCustomers).

Create a Hidden Text field on your Tablix report (TxtVendorId.value) that will hold your first lookup value.

If you don't want to show the first level value then you can hide it from the report.

Just right click on the TxtVendorId and choose expressions. In Expression window, just set the below expression -

= Lookup(Fields!CustID.Value,Fields!CustID.Value,Fields!VendorID.Value,"dsCustomers")

Step 2: But now you need to find the vendor name from a Vendor Master table (dsVendors). You can do another look up to the Vendor Master table using the ReportItems! TxtVendorId 1.value (Hidden field on the report) as the first value of the look up.

Right click on the Vendor Name details column and choose expressions. In Expression window, just set the below expression -

= Lookup(ReportItems! TxtVendorId.Value, Fields!VendorID.Value, Fields!Name.Value,"dsVendors")

That way you do not get the scope issue.

To Learn more, please visit our YouTube channel at - 
http://www.youtube.com/c/Sql-datatools
To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/
To Learn more, please visit our twitter account at -
https://twitter.com/macxima