Posted on 2007-10-13
Last Modified: 2012-04-04
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?

Question by:misdevelopment
    LVL 18

    Accepted Solution

    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
    LVL 16

    Expert Comment

    PFrog is right.
    LVL 14

    Expert Comment

    you can probably use SUB REPORTS to use different data source in a single report...

    Expert Comment

    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?
    LVL 18

    Expert Comment

    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.

    Expert Comment

    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.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now