Report data source
I need to create a report that will need to run against 2 different instances of sql server (schemas are the same, data is different). How do I approach this so I only have to maintain one report? To be honest, I'm a bit confused by the Embedded Connection vs. the Use Shared Data Source Reference in the Data Source Properties window.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TempDBA
Or if you have two servers A and B and want data from both and combine them to show as a single report, you can connect to server A. In server A create a stored procedure to fetch data from B via link server call, store the data in temp table, insert the data into temp table from A and then return the data from server A. This way it will be easier too as you don't need to combine data again in the SSRS
Here's another possibility. Make sure your report uses a shared data source. On your report server, create two data sources, one for each server. Now deploy your report twice (you'll need to make a copy of your .RDL with a different name and deploy that). Connect report 1 with data source 1 and report 2 (which is a copy of report 1) with data source 2.
Maybe not the most elegant solution (it all depends on your situation), but it does the works: you can get data out of your two databases while needing to maintain just one report.
A more elegant solution would be the dynamic connection string as PatelAlpesh mentioned. You could create a report parameter to allow the user to select the database (or server). And your data source connection string would need to be built using an expression (done through that "fx" button to the right of the connection string edit field). In this case you'd not be able to use a shared data source but you'll need to use the embedded one.
Maybe not the most elegant solution (it all depends on your situation), but it does the works: you can get data out of your two databases while needing to maintain just one report.
A more elegant solution would be the dynamic connection string as PatelAlpesh mentioned. You could create a report parameter to allow the user to select the database (or server). And your data source connection string would need to be built using an expression (done through that "fx" button to the right of the connection string edit field). In this case you'd not be able to use a shared data source but you'll need to use the embedded one.