Report data source

Posted on 2012-09-19
Last Modified: 2012-10-24
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
Question by:MariaHalt

    Accepted Solution


    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
    LVL 21

    Assisted Solution

    by:Alpesh Patel
    You can create a connection string using expression for dynamic connectionstring. and You can use single source for multiple databases.
    LVL 25

    Expert Comment

    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
    LVL 37

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now