Creating a Custom Report Template in SSRS

Introduction

When we start creating reports at the enterprise level, we need to ensure the consistency in the look and feel of reports throughout the organization. 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. Now the challenge is, at the enterprise level there might be several departments/developers creating reports, so how do you ensure report layout consistency?

Fortunately, 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 of the organization.

Creating a Custom Report Template

Creating a custom report template is no different than creating a new report; the only exception is the placement of the report template that you created in the appropriate location so that it appears as a template on the New Item/Report dialog box. For example, as you can see below, I have created a report with a header (containing company logo and Report Name) and footer (containing execution time and paging information):

Report with Header and Footer
Report with Header and Footer

Once we’re done with the report template layout, as above, we need to place it in the appropriate location, as shown below, so that it appears as a report template when creating a new report in the New Item/Report dialog box.

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

If you are not able to track the above location then Click on Start -> All Programs -> Microsoft SQL Server 2008/2008 R2/2012 -> SQL Server Business Intelligence Development Studio (BIDS)/ SQL Server Data Tool (SSDT), right click on it and select Properties, it will pop up the Properties window of BIDS or SSDT, in this window, Copy the path value in Target box. Next click on Start -> Run, paste the path here and click Ok to open the containing folder. The path of BIDS or SSDT will end with Common7\IDE folder; now go to PrivateAssembiles -> ProjectItems -> ReportProject and copy the custom report template that we created above.

Please note, you need this custom report template to be copied on all of the report development machines wherever you want to use it for new report creation.

Using Custom Report Template

Once you are done with placing custom report template at the appropriate location it will start showing up as a template on the New Item/Report Dialog box. To see it in action, right click on the Reports node in the Solution Explorer of the Report Project and then click on Add -> New Item as shown below:

Solution Explorer
Solution Explorer

As you can see the custom report template starts appearing in the New Item Report dialog box; select the template to use it, specify the name for your report and click on Add:

Select the template
Select the template

And here you see the report with the header and footer already added (basically coming from the template we chose when creating new report):

Report with Header/Footer from Template
Report with Header/Footer from Template

Next you can add additional reporting items or content to your report; for example, I want to see the product sales report grouped by product category and sub category and hence I have used the below query and designed the tablix as shown below:

USE   AdventureWorksDW2008R2
SELECT     DPC.EnglishProductCategoryName,   DPSC.EnglishProductSubcategoryName, DP.EnglishProductName, SUM(FRS.SalesAmount) AS SalesAmount
FROM   dbo.FactResellerSales FRS
INNER     JOIN DimProduct   DP ON   FRS.ProductKey =   DP.ProductKey
INNER     JOIN DimProductSubcategory   DPSC ON DP.ProductSubcategoryKey   =   DPSC.ProductSubcategoryKey
INNER     JOIN DimProductCategory   DPC ON DPSC.ProductCategoryKey   =   DPC.ProductCategoryKey
GROUP     BY DPC.EnglishProductCategoryName, DPSC.EnglishProductSubcategoryName, DP.EnglishProductName

Report with grouped categories
Report with grouped categories

Now you can preview your report and you can see the header and footer appear as expected (actually was derived from the template we selected when creating a new report) as shown below:

Report with content
Report with content

Report with content
Report with content

Report Parts as Report Header and Footer

We can use a custom report template for creating a report with a standard header and footer for all of the reports in the organization. But how do we ensure that any changes in either header or footer are updated wherever (as many reports as) it has been used? This is where the use of report part can save you from updating each report manually.

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.

Click here to learn more about Report Parts.

Conclusion

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 to maintain consistency in the report look and feel. With this, you can ensure consistent report layout across departments of the organization.

Apart from creating and using custom report template, we also looked at using report part in a custom template, which actually maintains a link and notifies all the reports that use it. This way you can ensure a change (for example in the report header or footer, change of company logo) propagates to all of the reports without actually modifying each report individually.

Resources

Creating and using Report Parts in Report Builder 3.0

Templates in SSRS 2008

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles