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:
|
Now, you have to
create a report to show the customer name and vendor name as given below
|
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