Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Linked Reports Example


By:   |   Last Updated: 2011-02-23   |   Comments (7)   |   Related Tips: > Reporting Services Subreports

Problem

Reports outputs that differ on parameter values are a very common report design scenario. A common business scenario is where each report output needs a different caching / security / storage configuration. In the absence of any mechanism to deal with this requirement, a workaround would be to create multiple reports and configure them individually. This would lead to duplication of the same report in multiple places and maintainability would become a big challenge. In SSRS, there is a smart way to deal with this issue and in this tip we will show you how.

Solution
Using Linked Reports is the solution to this problem. Linked reports can be seen as a shortcut to the original report. The layout and the data source of the report cannot be changed. The remainder of the report level settings can be individually configured on each report.

Please keep in mind that this tip expects a working level of knowledge on SSRS from the reader. If you are new to SSRS, check out this tutorial or these tips to start to get up to speed. To simulate the problem in question, I have created a report from the Contact table of the AdventureWorks database. I have added a report parameter to the report and deployed it to the report manager. On executing the report, it looks like the below screenshot.

using linked reports in ssrs

To show the value of the Linked Reports, we would create two reports, where we would set the parameter value as "Mr." for one report and "Mrs." for a second report. To address these items, click on the side of the report and select "Create Linked Report" from the context-specific menu of the report. Go the individual report, select "Manage" from the context-specific menu, and configure the parameters tab with respective values as shown in the below screenshot.

select create linked report from the context-specific menu

Execute each report and they should function as configured, with the default parameter values.

Now make a change to the original report. For example change the background color of the table and deploy the report. Execute the Linked Report to see if the change is reflected. You would find the results as shown in the below screenshot.


execute the linked report to see if the change is reflected

Meeting the requirement of creating context-specific reports with the flexibility to configure each report individually, can be accomplished by creating a Linked Report using a single parameterized report as the master report.
Next Steps
  • For the purpose of this demonstration, we have configured just the parameters. Check out what other settings can be configured on a linked report.
  • Check out whether you can create a linked report out of a linked report.
  • If you are new to SSRS check out this tutorial or these tips to start to get up to speed.


Last Updated: 2011-02-23


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 14, 2015 - 10:29:16 PM - Ashish Sharma Back To Top

Thanks Siddharth. Any ideas how to get around different security levels when a linked report has sub reports.


Monday, June 03, 2013 - 1:43:16 PM - KJT Back To Top

 

Are you aware of any issues regarding executing linked reports that require entering a default value for multiple parameters?

 

Thanks for your assistance.

 

 


Thursday, April 11, 2013 - 4:24:14 AM - srikanth Back To Top

Hi all,

 can You guy's explain briefly What is 'Report Builder' and why it is useful and where it is used?

 

Thnxs

srikanth

 

 

 

 

 

 

 

 


Thursday, March 07, 2013 - 4:38:27 AM - Avi Chawla Back To Top

Hi Siddhart,

Nice article. I there any way to create linked reports programatically and not through ReportManager. Like we delpoy the reports programatically.Similarliy is it possible to create linked reports programatically?

 


Monday, January 28, 2013 - 1:59:24 PM - ltaylor Back To Top

I have created a linked report for an exsisting report builder report in Report Manager.  When the original report is run and then selected to print it works as expected in Landscape.  When the linked report is run and printed in prints in Portrait.  Why?


Wednesday, May 23, 2012 - 6:16:37 PM - Emil Glownia Back To Top

Very nice tutorial. I might do similar blog post in SSRS 2012 and add it to our SSRS Tutorial


Thursday, March 03, 2011 - 4:45:50 AM - amol Back To Top

Excellent

-Amol
www.sqlsuperfast.com

 


Learn more about SQL Server tools