SQL Server Reporting Services Linked Reports Example
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.
SolutionUsing 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.
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.
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.
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.
- 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.
About the author
View all my tips