By: Scott Murray | Comments (20) | Related: > Reporting Services Development
Problem
You have several report developers working on a large scale SSRS 2008R2 project and they are generating report after report. You would like all the reports to have the same "look and feel", at least on the header and footer sections, and even include some base queries for items commonly included as parameters for each report. How can you create such a template and make it appear in the New Project List?
Solution
Creating a report template is a several step process. First you need to gather your header and footer requirements, next, you will want to prepare a list of common parameters to be included in the template. Once the header and footer requirements and the parameter queries are documented, a basic report can be designed to address the requirements. Last, you will deploy the template to the appropriate report server template folder on the file system.
Gathering Requirements
Requirements gathering remains as one of the most important steps in creating a template. The template designer, the report developers, and the end user report consumers all need to brainstorm on what items should and should not be included in the header, footer, and body areas of every report. Some suggestions for the header include: a company or division logo, a facility, location, or company name, a report title, a time period for the report, and any parameter criteria that must be prominently placed at the top of each page. Next, in the process is scoping out the footer area. Some suggested items for this area include: page numbers (individual and in total), report run time and date, report path from the report server, report name, report criteria, and source information. Additionally, in the body of the report, either at the beginning or end of the report content, you may set aside an area for displaying extended report parameters or criteria. Your scope process should also include a default report size and orientation as these items will ultimately impact the placement of the other objects in the header and footer. One potential idea is to actually use Excel to prototype your initial template during the design phase; a sample of such a design is displayed below.
Template Design
Start the actual report template by opening up Business Intelligence Design Studio (BIDS), and then open either an existing Report Server Project or Create a new Report Server Project.
Next create a new blank report RDL file. Be sure to add a report header and footer by right clicking anywhere in the white area of the report and then selecting each of these items as shown in the below figure.
Based on your design specifications, go ahead and size your report and set the report orientation, as noted in the image below, in order to prepare for the addition of the rest of your design items. Pay very close attention to footer and header space you use, as these areas occupy this space on each and every page of the report. Also, be sure to use colors and contrasting colors which are easy to see both online and in print.
Next begin adding textboxes for the different header and footer parts; it is best to add individual text boxes. As you can see from the image below, the template additions are fairly detailed. We embed an image in the upper left corner of the header and then add four Title textboxes in the center of the header. Further, notice we insert a thick black line to distinguish between the header and the body of the report. Of course you could also use a large rectangle object around the entire header. Next a textbox is added at the bottom of the report body to display any minor criteria for the report; note this textbox only appears once whereas the page header and footer appear on every page. Finally, the footer area is defined by inserting several textboxes which contain SSRS built in "global" fields. The upper left textbox in the footer includes the Execution Time/Date while the upper right includes the current page and total pages. The next footer line includes a list of sources. The last footer line contains the built in fields for the Report Server, Report Folder, and Report Name. This line will display the exact report name that the user ran and the actual path from the report server.
As displayed in the below image, the Built-in fields can be added from the Built-in Fields list by dragging them directly from the list to the report footer.
With all the report template objects added, we can go ahead and preview the report to be sure it looks as expected.
Moving the Template to the Visual Studio Report Project Folder
Once your template is complete, you will need to manually copy the file to the appropriate SSRS ProjectItems directory. Below are the default or common locations where the templates are stored. If you changed the default installation directory for SQL Server, you will need to adjust accordingly.
(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
(SSRS 2008) - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
Now the next time you select Add -> New Item from BIDS Report Server Project, your new template will appear in the list.
Upon Selecting New Item, a dialogue box will open, similar to following image. Notice our new report template, HeadsUpSoccer_Report_Template appears in the list and is ready to be used. One important item to note, any changes to an existing template, will need to be reapplied to any previously completed reports.
Next Steps
- You can enhance your Template even further by adding a group of standard DataSources, Datasets, and Parameters which apply to all reports. Thus, you will not have to re-define common queries and their related parameters with each new report.
- Check out these related resources:
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
