We're using BIDS to create our report projects for SSRS 2005. Many of our reports need to compare data from disparate systems. Without importing data or creating a central repository, how do we add MULTIPLE data sources to a report object?

Who is Participating?
A single report object can only display data from a single dataset.
A report can have many datasets, each retrieving data from different data sources.

If you need to compare data from different data soures within the same dataset, then your best bet is to have a SQL Server database act as a central point. It doen't have to store all the data, just create linked servers to your other datasources, then create a stored proc that can bring all the data from all data sources into a single place. You can then use a single dataset to populate a report item
PFrog is right.
Jai STech ArchCommented:
you can probably use SUB REPORTS to use different data source in a single report...
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

This won't work for me because the bulk of the data in my report comes from an IBM DB2 (OLE DB) connection, while I need lookup tables from a SQL Server table.  We cannot set up a linked server with the DB2 in this shop.  So is there a solution?
An SSRS dataset is just a query. This query can be virtually anything you want, querying any OLEDB or ODBC database (be it SQL Server, Oracle, Excel or DB2). It can be a basic query or a stored procedure.

A report can contain as many different report items as you want. i.e. you may want your report to contain two different tables, and a chart. Each of these report items can only retrieve data from a single dataset.

If your report has 2 tables, each table will be populated by a single query (dataset). Both tables could use the same dataset, or they could use different datasets. What you cant do is have a single report table accessing data from two different queries/datasets.

So, lets work through an example.

You want to display a company summary. You want to show total sales by region and total costs by region.

Firstly you could create two datasets:

   dsSales   (i.e. SELECT Region_Name, Sum(Sales_Value) AS Sales FROM tblSales GROUP BY Region_Name)

                        Region_Name    Sales
                        Region_1           100
                        Region_2           250
                        Region_3           75

   dsCosts   (i.e. SELECT Region_Name, Sum(Cost_Value) AS Costs FROM tblCosts GROUP BY Region_Name)

                        Region_Name    Costs
                        Region_1           50
                        Region_2           210
                        Region_3           30

You would then add two tables to your report

  tableSales  would use the dsSales dataset
  tableCosts  would use the dsCosts dataset.

This will give you a report showing two separate tables, each showing the appropriate sales or costs figure for each region.

Now, lets assume you wanted to show both sales and costs in the same table, SSRS cant do this for you as a single report item (table, chart, matrix etc.) can only reference one of your datasets. So, the only way to achieve this is to create another dataset, and combine the figures in a query. There are a number of different ways of doing this, the correct approach depends entirely on your source data structures. On way would be to use a UNION query like this

            SELECT Region_Name, Sum(Sales_Value) AS Sales, Sum(Cost_Value) AS Costs FROM
(           SELECT Region_Name, Sales_Value, 0 AS Cost_Value FROM tblSales GROUP BY Region_Name
SELECT Region_Name, 0 AS Sales_Value, Cost_Value FROM tblCosts GROUP BY Region_Name
            ) GROUP BY Region_Name

Or you could use sub queries such as

            SELECT Region_Name,
                        (SELECT Sum(Sales_Value) FROM tblSales WHERE tblSales.Region_Name = tblRegion.Region_Name) AS Sales,
                        (SELECT Sum(Cost_Value) FROM tblCosts WHERE tblCosts.Region_Name = tblRegion.Region_Name) AS Costs
            FROM tblRegion

When you do this, you will end up with a single dataset that contains both sales and costs. i.e.

                        Region_Name    Sales    Costs
                        Region_1           100       50
                        Region_2           250       210
                        Region_3           75         30

Now you can display this in a table.

My comment A report can have many datasets, each retrieving data from different data sources , I meant that if you have 3 different datasets in a report, one could be retrieving data from a SQL database, another could be retrieving data from a DB2 database, and one could be querying an Access database. i.e. a report can source data from any number of different data sources.

My other coment: If you need to compare data from different data sources within the same dataset, then your best bet is to have a SQL Server database act as a central point.

In the above example, it was easy to combine the two queries as both tables reside in the same database. However if the Sales were in a DB2 database, and the costs were in SQL Server, your best option would be to create a linked server within SQL Server, then create a SQL Server view (or stored proc) that sourced data from the appropriate DB2 and SQL tables and combined them into a single result set. Then, SSRS can use the results.

You say that you can not set up a linked server to DB2. This will make life very difficult, if not impossible for you.

You need to create a single query that accesses data from both SQL and DB2. The first thing you have to do is (ignoring SSRS for now) create the query in either SQL or DB2. This one query must return ALL the information you need from both databases. The only way I know of doing this in SQL Server is using linked servers. If you cant do that then youll have to find a way of doing it within DB2.

Only when you have a single query working, can you then pass this query to SSRS and use it in a report.

Sounds like youve got your work cut out!

If I can help any more please get in touch.
I have found a way with Custom Code to work with multiple data sets in the same report object (ie lists and tables); if you are still following this post let me know and I will eloborate.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.