Tuesday, March 17, 2020

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


No comments:

Post a Comment