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
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.
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:
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
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
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
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 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.
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.