April 22, 2011 at 7:15 am
Hi all,
I was wondering how I can pull data from two different databases that sit on two different servers in SSRS?
April 22, 2011 at 7:23 am
Set up a second connection to the second database. IF you are wanting to combine them, it's more complicated. One way is to get a linked server between the instances and run a query on one that combines the data into one result set.
April 22, 2011 at 9:58 am
I have set up shared data sources to both databases on each server. I am needing to combine the two to create a dropdown for my parameter. I'll give you a brief overview of what I'm trying to do. All of my data has been pulled from databases "A" on server "A". In order to run my report I need to enter an agency parameter. To avoid any confusion or typos, I want to have a drop list to select the agency from instead of having them type it in. Here's my problem. The only column in database "A" is the agency number, however, the agencies don't typically know their number, they only know their name, but there's not agency name field in database "A" or anywhere on server "A". Server "B" has database "B" which has fields for agency name and number. So I want to join the two databases on agency number then have it display the agency name in the dropdown parameter for the user to select their agency name. Hope this makes sense.
April 23, 2011 at 10:00 am
Set up a view in Database A into Database B that looks for the names. Something like
create view AgencyNames
as
select name, id
from databaseb.dbo.AgencyNames
Now write a query in Database A (or a stored proc) that joins your view with your data and returns it to populate the drop down.
April 25, 2011 at 9:38 am
Okay, this will show how new to SQL Server I really am. You'll have to dumb it down for me. I'm not sure how to go about setting up the view. I guess I'm not sure what you mean when you say to set up the view in database a.
April 25, 2011 at 10:08 am
If you have a good connection to both servers/databases in SSRS you can stick to SSRS.
Make one dataset to be the parameter choice for Agency. Something like
SELECT AgencyID, AgencyName
FROM AgencyDatabase
In your Agency Parameter make the Value field = AgencyID and the Label field = AgencyName.
Then in your primary report dataset query you need a Where clause, something like:
WHERE AgencyID = @Agency
Where AgencyID is the AgencyID from your server/database A, and the @Agency is the value selected by your parameter that came from server/datatabase B.
One other note. If you have multiple parameters, the order of parameters can be important so you can change the order of parameters if needed.
By the way, although you don't need to do anything about it now, there are forums on SSC dedicated to Reporting Services questions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply