MULTIPLE DATA SOURCES IN SQL REPORTING SERVICES

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

   dsSalesAndCosts
            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
            UNION
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

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