Tips and Tricks for Ensuring Consistency in SQL Server Reporting Services Reports

When working with SQL Server Reporting Services (SSRS) Reports, we often develop multiple reports for the same customer or the same application. In this scenario, it is standard to keep the format/features of all the reports belonging to a particular application or particular customer the 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. Now the challenge is, at the enterprise level there might be several departments/developers creating reports, so how do you ensure report layout consistency? Some methods are described here for ensuring consistency across all reports.

SSRS Report Template

When you create a series of reports, you’ll inevitably find report items that are common to all the reports. For example, the header of all reports should have the company logo and address whereas the footer should have other company information or page layout information across all reports in a consistent manner. Creating a template that contains common report items can save you time and eliminate some of the repetition that comes with report writing.

SQL Server Reporting Services (SSRS) allows us to create standard/custom report templates with the desired report layout and use the same custom template every time when creating a new report. With this, you can ensure consistent report layout across departments or the organization.

Creating a custom report template is the same as creating a fresh new report. But this report should be placed at an appropriate location in system so that it will available as a template for other reports. Here a sample design template is given below.

Method 1-Using Code inside SSRS Reports

This template contains execution time, user information and page number information in the footer, while the header contains organization information in report header. The template also has a table, because this template is specially designed for tabular reports. Code for changing the font color and background color of a text box is given below. This code will be placed in the code area of the SSRS Report.

Function AlternateColor(ByVal RowID As String) As String

If RowID =0 Then 
	Return "Whitesmoke"        
    Else
        Return "No Color"
    End If
End Function

Function HeaderColor() As String
   Return "LightGrey"        
End Function

Now each textbox of table is calling AlternateColor function for change textbox back color.

After completing the design of template layout, we need to place this template at the appropriate location, as shown below. In this example, “3PillarTabulerReportTemplate” has been put in the given location.

For SQL Server 2008 – C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2012- C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Now this report template will be available when creating a new report in the New Item/Report dialog box.

Method 2 – Using a Database Table

In the above template, alternate back color for table handled by code of SSRS report. The report theme can be changed easily by changing the code and returning different colors using function. Although, if an organization has created hundreds of reports based on the above template and all reports must be modified manually to change the look and feel, it can be a challenging and time-consuming task.

Here is another method for controlling a report’s look and feel using information from database in the report template.

Create a new table called “ReportStyle” in your SQL Server database. Column information for this table is given below in tabular format. Activestatus columns in the table will notify about active themes for all reports and this is a very important column for controlling look and feel.

Data in the table will look like below. Active status is 1 for “Gray” style by default.

Design the template and add dataset in it using below SQL statement, then define parameter in report and set default value from dataset result as given below.

SELECT TOP 1 ReportHeaderForeColor,

TableHeaderBackColor,

ToogleRowBackColor1,

ToogleRowBackColor2

FROM ReportStyle

WHERE Activestatus=1

In the previous method, table row back color was controlled by code of SSRS report, but in this template, parameter has been filled with background row color. Now set expression in background color property of all textboxes as given below.

=IIf((rownumber(nothing) mod 2)= 1 ,Parameters!ToogleRowBackColor1.Value,Parameters!ToogleRowBackColor2.Value)

Now save the template and put the .rdl file at the appropriate location as given in method 1. Report template will be available when creating a new report in the New Item/Report dialog box.

Here a new tabular report “Table Space Usage Report” has been created using “3PillarTabulerReportTemplateWithStylesheet.” Right now the “Gray” style applied on the report can be seen in the below image.

Now, the active status for “Blue” style has been set to 1, it means blue style is default style for all reports. Re-execute the report and I got different look of this report.

Using the above method, consistency for look and feel, fonts, margins and many more things can be managed in enterprise-level reporting solution. Any changes in look and feel can be deployed in one click and it will be applied on ALL reports, hence manual reformatting of reports is not required. Because all the functionality is encapsulated in SSRS template, developers will not have to worry about color combinations and font settings. It will also reduce development and time of reports.

Custom Template for Report Wizard

Some predefined style templates are in built in to the SSRS report wizard for good look and feel of reports, e.g. Ocean, Generic, Corporate, Slate, etc. Predefined templates are not suitable for our reports, when we think about an organization’s logo color, font preference, etc. in such cases we can also design style according to organization requirements, and can deploy those changes in the system.

The StyleTemplates.xml file can be edit in the C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles\en folder, where is the language you are using. This folder is located on the computer on which Report Designer is installed. To modify the styles that are applied through the Report Wizard, edit the file that is in the folder created for the language you are using.

Here, I added a new style “3Pillar” in this template as given below.

Now when we create a new report using Report Wizard then this new style will be available for use.

A report developed using the Report Wizard will look like the below report.

Report part

A report part is basically a report item that can be used in headers and footers of the custom report template; usage of report part maintains a link and provides an update notification mechanism to all the users of the report part. This means if the report part is updated on the report server and if the reports with that report part are opened, it will notify you about the updated report part. You can choose to keep the existing report part as is or accept/bring the new updated report part into the report, which means it will replace the current report part in the report (for example header or footer) with the latest/updated report part from the server. Now do a practical use of report part in next few paragraphs.

For example, in previous report, header and footer is not available. Adding header and footer in many reports will be time-consuming and will increase chance of inconsistency in different reports. It will be more difficult and time consuming to make little changes in the header because hundreds of reports are waiting for that small change. Really, we should go with approach which can provide consistency across the reports. Here, the new and dynamic approach is the report part. Please note, you can create and publish a report part either in both Business Intelligence Development Studio Report Designer or in Report Builder 3.0, but you can only search and use it in Report Builder 3.0. But still it is a powerful option for developers.

Open any report from report manager which has header and footer in it. Go to menu and click on the Publish Report Parts menu item as shown below; you need to be connected to the report server to publish the report part. You can change the report server URL and location to publish the report part by going to Options.

Now select ReportHeader and ReportFooter report parts, you can click on the Publish button on the above screen to start the publishing. The result of this will be displayed on the Results area. By default report parts are deployed on the Report Parts folder on the connected report server, which can be changed by browsing to a new location.

Now open existing report to use ReportHeader and ReportFoooter report parts in your report, simply drag it from the Report Part Gallery to appropriate report area.

Publish report on report server. Execute report and see the result, header and footer has been applied on report. Now again one interesting thing of Report Part, due to this amazing functionality, many developer like report part. If the new version of report part is available then it will notify to use latest, when developer open reports in report builder as display in below image. It is the choice of the developer to use the latest version or stay with the old one. Again good for ensure consistency.

I hope the above describe methods will really be helpful for ensuring consistency in SSRS reporting solution for other developer also.