?
Solved

MULTIPLE DATA SOURCES IN SQL REPORTING SERVICES

Posted on 2007-10-13
6
Medium Priority
?
12,411 Views
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?

Thanks,
0
Comment
Question by:misdevelopment
6 Comments
 
LVL 18

Accepted Solution

by:
PFrog earned 2000 total points
ID: 20070619
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
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20071010
PFrog is right.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20071453
you can probably use SUB REPORTS to use different data source in a single report...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Expert Comment

by:jensays
ID: 21054226
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?
0
 
LVL 18

Expert Comment

by:PFrog
ID: 21058727
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.
0
 

Expert Comment

by:GeekInOnLife
ID: 37806769
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 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