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

SSR1
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

SSR-2

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

SSR-3
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.

SSR-4
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.

ColumnNameDataTypeDescription
StyleIDSmallintPrimary Key for the table
StyleNamevarchar(50)Name of theme/style
ReportHeaderForeColorvarchar(50)Header contecnt forecolor
TableHeaderBackColorvarchar(50)Table header backcolor
ToogleRowBackColor1varchar(50)Odd row backcolor
ToogleRowBackColor2varchar(50)Even row backclor
ActivestatustinyintFlag for identify active theme

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

SRS-01
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

SSR-6
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)

SSR-7

 

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.

SSR-8
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.

SSR-9

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.

SRS-02

SSR-10
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.

SSR-11

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

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

SSR-13
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.

SSR-15
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.

SSR-16
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.

SSR-17
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.

SSR-18

 

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

Tejpal Baghel

Tejpal Baghel

Technical Lead

Tejpal Baghel is a Technical Lead working at 3Pillar Global. Tejpal has 8 years of experience in data warehousing and business intelligence solution development using MSBI technology. He has enterprise business intelligence solution development experience in healthcare, financial management, B2B integration and revenue forecasting. Prior to joining 3Pillar, he has worked in Tata Consultancy Services (TCS) and Accretive Health. Tejpal is a graduate in Computer Science from Agra University in India. He is actively involved in social activities and believes in the power of politics for building a better nation.

2 Responses to “Tips and Tricks for Ensuring Consistency in SQL Server Reporting Services Reports”
  1. Marc S. on

    Great article! I especially like the DB/parameter/stylesheet part, perhaps we will adapt the idea for our reports.
    Regarding the Report Parts it’s a shame that you still can’t consume them in BIDS / SSDT, it would be a great tool to provide consistent layout for all our report headers and footers (got >100 reports for 2 platforms and around 20 projects).
    Kind regards, Marc S.

    Reply
  2. J Murrell on

    Excellent article, I’m going to play around with this idea this weekend. I’m hoping that we can adopt Method 2 and additional project specific styles since we finally have started adding new report dev’s.
    Thanks,

    Reply
Leave a Reply

Related Posts

Take 3, Scene 23: The Influence and Evolution of Blockchain Michael Lisse and Derek Tiffany join us in the studio for this episode of Take 3 to talk about the influence and evolution of blockchain in the financ...
The Innovation Engine from TechCrunch Disrupt New York For a very special episode of The Innovation Engine, we're bringing you a number of interviews from the floor of last week's TechCrunch Disrupt New Yo...
Take 3, Scene 22: Minimizing Time to Value On this episode of Take 3, we dive into the concept of Minimizing Time to Value with Andy Zipfel and Paul Doman, two members of 3Pillar's Client Servi...
Inside the Refraction Space, with Rachael Stott On this episode of The Innovation Engine, we bring the podcast inside the Refraction headquarters to talk again with Rachael Stott on how place and sp...
Health & Wellness is a Reactive Industry, but Technolog... Skyrocketing healthcare spending is having far-reaching consequences on the American economy. It contributes to keeping wages stagnant, and crowds out...

SUBSCRIBE TODAY


Sign up today to receive our monthly product development tips newsletter.