Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 21, 2012

Creating a Custom Report Template in SSRS

By Arshad Ali

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM