Tips ‘N’ Tricks – SSRS – Ensure Consistency across Multiple Reports using Report Templates
When working with SSRS Reports, we often develop multiple reports for the same customer or same application. In this scenario, it is a standard to keep the format/features of all the reports belonging to a particular application or particular customer same/consistent. And when multiple developers work on each of the different reports, there is a possibility of slight variation in the formatting like fonts, size, padding, alignment, and so on. To address this very challenge and to ensure consistency in format across different reports, we can make use of a feature of SQL Server Reporting Services (SSRS) called Report Template.
Follow the below listed steps to set up the Report Templates in SSRS:
Create a Report with all the formatting, standards, and conventions etc. as per the business requirements and save the Report (.rdl file). Let us say you call it as SSRS Report Demo Template.rdl.
Copy the .rdl file to the following location on all the developers computers:
<<Installation Directory>>:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
Now, when you try to add a New Report using the Add New Item option (Right Click on Reports folder in Solution Explorer, select Add | New Item…), you will see SSRS Report Demo Template.rdl as one of the available items as shown in the below screenshot.
Next time, whenever a developer wants to develop a report for this customer/application (which require the same exact format as the SSRS Report Demo Template), then they can just select this item in the Add New Item (instead of Report item) and give it a name as appropriate and click on Add. With this, they will see all the formatting/settings in this new report pre-built (copied from the SSRS Report Demo Template) and they can start building on top of this, thereby ensuring consistency across multiple reports.
Following are some of the most common settings which are usually captured in the Report Templates:
Standard Textboxes in the Page Header and Footer with appropriate text/expressions like Report Name, execution Time, Executed by User, Page Number, Disclaimer, Useful links to company website, and security page etc.
Font color, size, and alignment.
Background color, Margins, and Padding etc. for Textboxes and other similar report items.
Set of pre-defined variables/parameters for storing some standard values like font colors and background colors etc.
Any custom code which might be required across multiple reports.
Page Setting like Orientation, Paper Size, and Margins etc. which are very crucial for proper exporting and printing of reports. And this list goes on.
If you feel there is any other important settings that can be included in the Report Template, then please let me know by leaving a comment below.
Note: The above listed steps are based on SQL Server 2008 R2 Reporting Services installed on Windows XP.
Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.
Posted on May 23, 2012, in Best Practices, BIDS, BIDS Tips, Development Tips, SQL Client Tools, SQL Server, SSRS, SSRS Tips, Tips 'N' Tricks and tagged Best Practices, BIDS, Formatting, MSBI, Reporting Services, SQL Server, SQL Server Business Intelligence Development Studio, SSRS, Templates, Tips, Tips and Tricks. Bookmark the permalink. 7 Comments.