misdevelopment
asked on
MULTIPLE DATA SOURCES IN SQL REPORTING SERVICES
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,
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL_SERVER_DBA
PFrog is right.
you can probably use SUB REPORTS to use different data source in a single report...
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.
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.
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.