How do you combine multiple result sets in SSRS?

Question

What's the best way to combine results sets from disparate data sources in SSRS?

In my particular example, I need to write a report that pulls data from SQL Server and combines it with another set of data that comes from a DB2 database. In the end, I need to join these separate data sets together so I have one combined dataset with data from both sources combined on to the same rows. (Like an inner join if both tables were coming from the same SQL DB). I know that you can't do this "out of the box" in SSRS 2005. I'm not excited about having to pull the data into a temporary table on my SQL box because users need to be able to run this report on demand and it seems like having to use SSIS to get the data into the table on demand will be slow and hard to manage with multiple users trying to get at the report simultaneously. Are there any other, more elegant solutions out there?

I know that the linked server solution mentioned below would technically work, however, for some reason our DBAs will simply not allow us to use linked servers.

Answer 1

SSRS 2005 allows you to have multiple datasets for a report. Each dataset can refer to a different datasource, one can come from a SQL DB another can be a ODBC source etc.

In the report designer view in Visual Studio go to the "Data" tab and add new data sources pointing to your different databases. Once you are done, when designing the report for each element you have to explicitly specify which dataset the data is coming from.

If the above does not work, you can write managed code, refer to http://msdn.microsoft.com/en-us/msdntv/cc540036.aspx for more helpful information and videos.

Answer 2

You could add the DB2 database as a linked server in sql server and just join the two tables in a view/sproc in sql. I've done it, it's not hard and you'll get data in realtime.

Answer 3

I know that you can add two different data sets to a report, however, I need to be able to join them together. Anybody have any ideas on how to best accomplish this?

Answer 4

You could attach both the MSSQL tables and the DB2 tables to a Jet database and bind your report to the Jet database. I don't know the implications of the single threaded nature of Jet, or how much work would be delegated to the backing stores.

Answer 5

We had to do something similar (i.e. inner join 2 data sources from different servers). I believe the best way is to write your own custom Data Extension. It's not very difficult and it would give you the ability to do this and more.

Answer 6

U could create a linked server that would access the database directly or if you didn't want to strain the database during business hours, you could create a job to copy the data you need overnight.