SSRS multiple data sources

Question

I am using SSRS 2008 to create a report. Is it possible to create a report from multiple data sources?

Thanks.

Answer 1

With SQL Server Reporting Services 2008 R2 you can use the lookup functions to lookup a piece of data from a second dataset: http://blog.datainspirations.com/2010/03/19/sql-server-2008-r2-reporting-services-look-up-look-down-look-all-around-part-i/ Very handy.

From older versions of SSRS, you can create a subreport that occupies a cell in a table: the subreport can be called with a different parameter for every row, effectively "joining" to the second dataset. http://technet.microsoft.com/en-us/library/ms160348.aspx

Answer 2

Yes, you can use multiple Data Sources. Although you can't join the data. For instance, you can't have a table with fields from both Data Sources. BUT you can have a Data Source from Oracle and a Data Source from MS SQL. You can use a List to group things together.

Generally, you'll want to use linked servers if you can and do the work in your query.

Answer 3

You can present data from multiple data sources in a single report, merging datasets from different sources however, is not possible, unless you use some technique to merge this data from the database side, i.e. using a stored procedure. Also if your stored procedure returns multiple result sets, you have to do things like adding them into a table and returning the combined data as a single result, its not terribly difficult, but to your originally stated question, you can use multiple data sources in a report quite easily.