Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a Reporting Service Report Template


By:   |   Read Comments (19)   |   Related Tips: > 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 Markup

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.

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

Header and Footer Add

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.

Report Properties

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.

Report Properties

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.

Built In Fields

With all the report template objects added, we can go ahead and preview the report to be sure it looks as expected.

Report Preview

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

Template Move

Now the next time you select Add -> New Item from BIDS Report Server Project, your new template will appear in the list.

Add New Item

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.

New Template Appears in List
Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, March 01, 2017 - 9:51:35 AM - AA Back To Top

 

 Very interesting, and well defined.

Thanks


Monday, August 29, 2016 - 9:53:13 AM - zymos Back To Top

Thank you Scott for the great post.

In addition, below is the location for
(SSRS 2012) - C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

 

Zymos.

 


Friday, February 14, 2014 - 10:38:59 AM - scott murray Back To Top

Unfortunately not.


Friday, February 14, 2014 - 9:43:11 AM - Debbie Back To Top

Is there a way to choose this template when building a report using the wizard?


Thursday, October 31, 2013 - 8:50:09 AM - Scott Murray Back To Top

Not for report builder... Only BIDS


Thursday, October 31, 2013 - 5:16:52 AM - Vijay Back To Top

Hi Scott,

Above post is very good.

I have one question - when creating any new report from Report Server, after clicking on Report Builder could I open my default Template rather than opening blank report.

 

Thanks,

Vijay


Wednesday, August 21, 2013 - 8:55:41 AM - Kris Back To Top

You are GURU. I enjoyed reading this article. I wish you produce some more article like this.Thanks for educating the community. Thanks a lot


Monday, August 12, 2013 - 9:06:44 PM - Scott Murray Back To Top

Mary,

 

I know it has been a little while but you may want to take a look at : http://www.sqlservercentral.com/blogs/sqldownsouth/2012/06/27/how-to-create-reporting-services-2012-report-templates/

 


Wednesday, April 17, 2013 - 12:23:44 AM - Mary Back To Top

 

Not able to get this to work with SQL Server 2012, even after creating the missing portion of the ProjectItems\ReportProject path


Wednesday, December 12, 2012 - 9:24:43 PM - srinivasskc Back To Top

Hi Good Morning.. In my project,while i was testing,i found that in report viewer..
when we export to word.. the column names on first page wont appear in different pages? (Can u tell me why? )
and when i choose for the print in the report viewer.. why it doesnt gets the all the columns in one page and in a landscape mode..? (E.g:It shows 10 columns in 4 pages,then again same follows...)

Any one explain me this ?


Thursday, December 06, 2012 - 6:24:49 PM - Kevin Back To Top

Manas- I don't know how you can make it stop processing on the front end, but you could show a textbox with an error message using something like this:

IIF(Parameters!MultiSelect.Count > 6, "Too many selected", "")

Or in the display box use the same thing and use true/false to hide or show the box.

On the SQL side, count the number of commas in the incoming parameter and use that to stop the report from processing if there are too many.

Hope that helps.


Tuesday, August 14, 2012 - 4:04:23 AM - Manas Back To Top

Hi i have a report there is a parmater ,around 25 values is there ,My qst is if the user select more than 5 or 6 the report need to show a message and the report need to stop.. once the user will select less than 5 or 6 need to work prperly..


Wednesday, April 18, 2012 - 6:39:56 AM - Joshua Back To Top

Good one! Triend and its working

 


Friday, April 13, 2012 - 5:25:06 PM - Mindy Back To Top

Nice Job!  Great detail!

 Thank you!


Friday, April 06, 2012 - 3:44:40 PM - Stephanie Back To Top

great post, exactly what i was looking for!


Thursday, April 05, 2012 - 7:15:13 AM - Scott Murray Back To Top

Yes, you can save the file anywhere on your c:\ drive.  That is just an example.


Thursday, April 05, 2012 - 2:38:39 AM - Sidd Back To Top

Excellent! Explained so nicely.


Wednesday, April 04, 2012 - 9:35:43 PM - Adrian Back To Top

Excellent post.

What is the difference, though, if I save a report anywhere on my C drive and then use the Add - Existing Item menu?

Isn't this the same?

 


Wednesday, April 04, 2012 - 9:28:21 AM - Ranga Back To Top

Scott,

You had explained each and every part in detail. Nice job.

Ranga

 


Learn more about SQL Server tools