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.Data Soure Properties window
Who is Participating?

Shared Data Source means it lives outside of report and you can use it on multiple reports. For instance on database and 100 reports. If you have to change than you do that in one place.

Your question is unclear to me... Main reason is that often people need to use different connection when they need to deploy report to different environments and you can set them up separately.

Your question indicates you want to use the same environment and points to two different instances.... You may be able to use embedded connection (and use expression).... I haven't used this method but looking at screenshot expression is available so that is possible but you might have to maintain it for each report which in long term might get time consuming.

I also suggest to check our

SSRS Tutorial for beginners you won't find answer to your question but maybe you will find something interesting.

Hope that helps
Alpesh PatelAssistant ConsultantCommented:
You can create a connection string using expression for dynamic connectionstring. and You can use single source for multiple databases.
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
ValentinoVBI ConsultantCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.