Building SSRS Reports With Multiple Data Sources

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:

Dbo.CUSTOMER_TRANS

customer_id	trans_date	amount
1233	2012-03-01 00:00:00.000	100
2708	2012-04-05 00:00:00.000	150
3421	2012-07-22 00:00:00.000	120
8765	2012-08-27 00:00:00.000	109

The Oracle server would have Customer tables containing customer information like:

CUSTOMER_INFO

cust_id	Name	Email
1233	John	john@xyz.com
2708	David	david@xyz.com
3421	Michael	Michael@xyz.com
8765	Jack	jack@xyz.com

In order to fill data from these two sources into a single report, we would follow these steps:

1. Create and select data sources (that are already present on your report server):

2. Create a dataset using Sql server data source (db_Customer_SqlServer):

And similarly create a dataset using Oracle Data source:

3. 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.