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.

3 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
  3. Great article on

    Hi,

    I do have a few questions. Where I am working now, we use dashboard reports a lot in MVC applicaiton. The dashboards are developed in a third party tool called highcharts. They want to be able to access the detail reports which is deployed on the report server. I am not sure how the whole integration piece works. I am developing the detailed reports in grids and deploying them on report server

    Reply
Leave a Reply

Related Posts

Jessica Hall Presents Webinar on Product Strategy Jessica Hall, 3Pillar's Senior Director of Product Strategy & Design, recently presented a Product Lunch webinar in conjunction with Product Colle...
Take 3, Scene 28 – Do You Have a Productive Product Mi... In Take 3, Scene 28, we talk to Massi Behbahani and Cindy Halim about what they learned at the Mind The Product conference, why empathy matters when w...
Money 20/20 Preview: What We’re Looking Forward to at ... Two 3Pillar team members will be attending Money20/20 in Las Vegas the week of October 23, 2017. Billing itself as the “World's Largest Payments &...
Credit Card Fraud Detection – An Insight Into Machine ... The importance of Machine Learning and Data Science cannot be overstated. If you are interested in studying past trends and training machines to learn...
Robert Malai on How Not to Use Docker at the Rise of the Dev... On June 17th, Robert Malai spoke at the Rise of the DevOps conference in Timisoara, Romania. Rise of the DevOps is a conference aimed at the growth of...

SUBSCRIBE TODAY


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