In this article we'll show you how to create a custom Report Template for use with SQL Server Reporting Services.
What is a Report Template?
A Report Template is a custom defined .RDL file which you save to the ReportProject folder of SQL Server Data Tools (SSDT). The custom Report Template will render in the list of available Report Templates to choose from within SSDT. The benefit is Reusability and Brand Standards for authoring business specific reports.
What we'll cover in this session:
- Define a new custom Report Template
- Copy Report Template to ReportProject folder
- Add a new report using the Report Template
This article assumes you have the following:
- Installed SQL Server 2012 or 2008 R2, with Reporting Services.
- Experience with SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS). Note: For this session we'll be using SSDT.
- Experience with authoring reports using: Reporting Builder 3.0, SQL Server Data Tools (SSDT) or BIDS.
Note: A download of the demo is available at the end of this session.
Let's get started!
Step 1 - Define Custom Report Template
1) Open SQL Server Data Tools and select File >> New Project...
2) From within the Installed Templates pane, expand Business Intelligence node and click Reporting Services.
3) From within the template pane, select Report Server Project.
4) Give the project a name and set the Location.
5) Click OK.
6) From the Solution Explorer pane, right click Reports >> Add >> New Item...
7) Select Report from the list of templates in the middle pane.
8) Provide a meaningful name for the report and click Add. For example, 'RT SalesMarketing.rdl'
9) Right click on the report design surface and select Insert >> Page Header
10) Right click on the report design surface and select Insert >> Page Footer
11) From within the Header section of the report, place a company logo and other corporate branding you desire.
12) Save your work and close the report.
Note: Keep your SQL Server Data Tools project session open.
Step 2 - Copy Report Template to ReportProject folder
1) Open Windows Explorer and navigate to the location of your project files.
2) Find the sample RT SalesMarketing.rdl file (or whatever you named it), right click it and click Copy.
3) Now navigate to <drive>:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
4) Paste your copy of the RT SalesMarketing.rdl to this location.
Step 3 - Add a new report using the Report Template
1) Go back to your Report Template project.
2) Right click on the Reports folder and click Add >> New Item...
3) You should see your newly created .rdl file in Report Template list.
4) Select RT SalesMarketing and give it a meaningful name.
5) Click Add to close the template window.
6) You can now begin adding data source and objects to your report.
You have completed creating a custom Report Template for SQL Server Reporting Services, using SQL Server Data Tools (SSDT).
Download: Report Template Demo