Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theHydra
Starting Member
42 Posts |
 Posted - 2012-12-03 : 17:06:40
|
| Hi all,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.Thanks in advance for any suggestions. |
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-12-03 : 17:24:58
|
| You Could add the database2 and database 3 as as a linked server in sql server and just join those three tables in sql. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-04 : 01:02:48
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-04 : 16:47:01
|
| Thanks guys.Hopefully I'm not oversimplifying this, but isn't there an easier way to query multiple DB's from within SSRS? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-06 : 01:22:52
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Multiple Datasources