SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Datasources
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

theHydra
Starting Member

42 Posts

Posted - 12/03/2012 :  17:06:40  Show Profile  Reply with Quote
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

101 Posts

Posted - 12/03/2012 :  17:24:58  Show Profile  Reply with Quote
You Could add the database2 and database 3 as as a linked server in sql server and just join those three tables in sql.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/04/2012 :  01:02:48  Show Profile  Reply with Quote
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/

Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/04/2012 :  16:47:01  Show Profile  Reply with Quote
Thanks guys.

Hopefully I'm not oversimplifying this, but isn't there an easier way to query multiple DB's from within SSRS?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/06/2012 :  01:22:52  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000