SQL Server Reporting Services Linked Reports Example

By:   |   Comments (7)   |   Related: > Reporting Services Development


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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




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

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


Monday, June 3, 2013 - 1:43:16 PM - KJT Back To Top (25256)

 

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 (23299)

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 7, 2013 - 4:38:27 AM - Avi Chawla Back To Top (22624)

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 (21758)

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 (17621)

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


Thursday, March 3, 2011 - 4:45:50 AM - amol Back To Top (13104)

Excellent

-Amol
www.sqlsuperfast.com

 















get free sql tips
agree to terms