Use Linked Reports in SQL Server Reporting Services!

A couple of weeks ago I was asked to develop a number of reports in SQL Server Reporting Services 2005. Most of these reports display information from different websites that the client develops and hosts for their own clients, something like 25 different websites.

My client's original idea was to create a "template" report that can then be copied for each website separately.​ The problem with this approach is of course that if something needs to be changed, the change will have to be copied into all 25 reports.

My initial idea was to use a join in my sql queries to the Active Directory, so that based on the current user I could figure out which AD user groups the user belongs to and then, based on a naming convention, derive from the group names which websites the user should have access to. Well, that's sort of complicated. I couldn't even get a linked server to the Active Directory working, probably because of some permissions problems.

And then I decided to use my favourite approach: I found this pretty good book on the subject: http://www.amazon.com/Microsoft-Server-2005-Reporting-Services/dp/0072262397/, skimmed through its contents and found an elegant solution in one of the last chapters.

The idea is

1. to have website as a parameter in your report;
2. put the report in a hidden folder on the Report Server available only to users authorized to view the information across websites; we'll call it the base report;
3. create a folder for each website and set permissions on each folder;
4. from the base report create a linked report for each website in the corresponding website folder
5. in the linked report, go to the parameters configuration page, set the default value of our website parameter to the desired value and choose not to prompt the user for the parameter value.

To sum up, we now have a base report available to the cross-site admins and a website-specific linked report available to the website admins. If the report needs to be changed, in most cases we'll only need to update the base report!

Another useful best practice that I learned from the book is to create a template report containing the basic layout: the header, the footer, the logo, page numbering, etc. Then we can place this template .rdl report into the visual studio folder where Report Designer stores its templates (for VS2005, it can be C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject) . After that, when we add a new report to our Visual Studio report project, the template will appear in the list of installed templates for us to choose from. When we create a new report based on the template, we'll get all the formatting and features from the template and can then proceed to modify the report as desired:

Happy coding!