SQL Server Reporting Services (SSRS) allow users to create reports using data from a variety of sources, including relational databases, multidimensional databases, web services, XML, and other document types. Using SSRS, we can present data from multiple data sources in a single report.
Methods to achieve this include:
- Using sub reports
- Using Openrowset queries.
- Using Report Builder functions like Look Up, Look Up set, and Multi look up.
In this post we will look at Look Up and Multi Look Up functions.
Consider a typical scenario in which we have two different database instances:
- MS SQL Server Instance – This instance holds the records for customer sales.
- Oracle Server Instance – This instance holds the records for customer information.
The SQL server would have customer table with records like:
The Oracle server would have Customer tables containing customer information like:
In order to fill data from these two sources into a single report, we would follow these steps:
- Create and select data sources (that are already present on your report server):
- Create a dataset using Sql server data source (db_Customer_SqlServer):
And similarly create a dataset using Oracle Data source:
- Add a table to the report and select the SQL server dataset:
In the report add any columns that you want to display:
You’ll see that names and emails are not populated from the SQL server dataset. To fill out names and emails we have to refer Oracle server dataset.
Right click the name cell and go to values. This will open an expression builder screen.
Write an expression along the lines of this:
=Lookup (Fields!customer_id.Value, Fields!cust_id.Value, Fields!Name.Value, ds_Customer_Oracle)
Basically the syntax is:
=Lookup (source expression, destination expression, result expression, dataset)
We can perform the same actions for the email value, therefore when we run the report we will get the desired outputs.
The Look Up instead returns a single value only; in this case it will return a name corresponding to a customer ID that it pulls from the SQL dataset. Similarly, Lookupset return an array of values for a particular condition.
If you’re interested in learning more comment below or contact us at firstname.lastname@example.org