I have a report that simply displays sales for a given amount of time.
What I'm trying to figure out is how to run this report against three different databases at three different locations.
So if each of the three DBs had 2M worth of sales, the report would show 6M.
I'm using SSRS and am trying to 'merge' these database connections somehow in the shared data sources section in Visual Studio, but am unable to get it to work.
The way I see this working is I'd create a new data source, that is actually querying all three at a time.
you can use SSIS to get data from other databases to centralised database where you want report to be run. Inside SSIS you write logic to pull and merge required data and populate single table in central database. Then in report add a connection to centralised database and pull required data to your report.
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
its possible if you use distributed query methods like openrowset or linked server. otherwise you need to use etl logic to get them onto a centralised db as i suggested
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Multiple Datasources
Posted - 12/03/2012 : 17:06:40
