Report Template Using SQL Server Reporting Service (SSRS)

Introduction

The SQL Server product includes a service called "SQL Server Reporting Services (SSRS)". SSRS is a full-featured application that provides report design, development, testing and deployment of reports using the Business Intelligence Development Studio (BIDS) developer tool.

When we are creating reports using reporting services for the organization, we need to first decide how the reports look and we need to maintain the consistency in the look and feel of the reports throughout the organization. For example the report header should have a company logo, address and other company related information whereas the footer should have information like page number, report created date and so on information across all the reports in a consistent manner.

SSRS allows us to create custom report templates with a predefined report layout and to use the same custom template every time when creating a new report. It helps us to maintain consistency across the organization level.

Creating a Custom Report Template

A report Template is nothing but a partially completed report (RDL file for server report and RDLC for client report). We can add our own report template in a BIDS report project template items folder. Creating a custom report template is the same as simply creating a report in BIDS.

The following is the procedure to create a custom report template.

Step 1

Create a new report project or open any existing report project using the Business Intelligence Development Studio (BIDS). BIDS is available with SQL Server 2005, 2008 and 2012.



Step 2

Create new reports by just right-clicking the report folder and add a new item.





It creates a new blank report (RDL or RDLC file). Now we can design a template report just as a normal report but without any dataset or data item. Normally the developer adds a header and footer in a report template.

In this example, I have added a header and a footer. We can add a logo, some background image and we can also a common global report variable like Page numbers, total pages and so on.





Step 3

Save this report.

Step 4

Copy this report file (rdl or rdlc) to the Visual Studio project items folder.

The project items folder is different for different versions of SQL Server. The path of the project items folder as below.

• For SQL Server 2005
  
  C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject.
  
  
• 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.

How to use a create report template in our project

The process of creating a template is very easy. This template can be used in a way similar to how a normal report is created. Just right-click on the report folder then select Add new --> new item.

In the next screen, we can see our own create template as well. Select this template to get the same look and feel as the template.


Output

Here we can see the report with header and footer that are already added to the template.



Summary

SSRS allows us to create a custom report template with a desired report layout and using this custom template, we can create a new report and maintain consistency in the report look and feel. We can ensure consistent wth the report layout.