business intelligence consulting
 

Create a Custom Report Template for SQL Server Reporting Services (SSRS) 2012

by Hans Esquivel 16. January 2013 09:31

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

 

Cheers!

SQL Server | Reporting Services

Comments (2) -

Mary
Mary United States
4/14/2013 6:41:18 PM #

Information is not correct in Step 2 "Step 2 - Copy Report Template to ReportProject folder" . Visual Studio 10 does not create the ProjectItems or the ReportProject folders by default upon installation. Even if you create them afterwards and put the report template .RDL file there, you still cannot use the template because it doesn't appear in the list as it should in "Step 3 - Add a new report using the Report Template".

hesquivel
hesquivel United States
4/15/2013 7:04:44 AM #

Hi Mary,

If you're using Visual Studio 2010 development tools, you are correct - it will not configure your system as described in Step 2. However, if you look to the beginning of this article "This article assumes you have the following", we are using SQL Server Data Tools (SSDT) for our design and development of the reports. If you install SSDT or BIDS, the folders and other templates related to business intelligence will be installed to the location described in our article. I hope this helps.

Hans

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud