Problem solve Get help with specific problems with your technologies, process and projects.

Creating SQL Server Reporting Services templates

Tired of creating templates in SQL Server Reporting Services? SQL Server Business Intelligence Development Studio makes it possible to maintain reusable reporting templates in SQL Server 2005.

People are always looking for a way to add a little flair to things they do, but often find themselves stymied by the limits of what's possible. Take SQL Server Reporting Services 2005, for instance. One of its biggest missing ingredients — even as of version 2005 — is reusable templates and styles, something Microsoft acknowledged in their FAQ on SSRS (see question #4.5). Without anything to readily available, people have had to turn to home-brewing their own custom Reporting Services templates.

According to MSDN blogger Russell Christopher, there's a way to create templates in SSRS 2005, although it does requires a little work. Assuming you have the SQL Server Business Intelligence Development Studio installed, go to the following directory: \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject. There, you should see an .RDL document simply named, REPORT.RDL. This is the basic template for all SSRS projects, so it can be edited as needed to create a new basic template from which all future reports can be derived. When you do this, the new items should show up in the Installed Templates area of the Add New Item dialog.

It's also possible to create copies of the REPORT.RDL file, modify those, and drop them back into the directory. When the user chooses to create a new report, they'll then have the option to pick one of the other reports in that directory. Note that the IDE will need to be closed and reopened for the new templates to show up. They're not automatically detected while the program is still running.

There's a few limitations to the way this works. First, the above technique will only work with SQL Server BIDS, as opposed to the full conventional version of Visual Studio 2005. You can't use Visual Studio 2005's RDL builder (which generates files with the extension .RDLC) to work with these templates.

Another big limitation involves Report Builder, which is the ad-hoc client-side reporting system in SQL Server 2005. Its design allows a user to quickly generate reports without needing to know much about the database schema. Sadly, .RDL templates cannot be used in conjunction with Report Builder to accelerate development there — unless, according to Bob Meyers, you strictly follow several rules about the report design:

  • No page headers or footers
  • No rectangles
  • No lists
  • Use only one data region
  • Always bind the data to a report model query
  • The data region groups (including the Details group, which is mandatory) must match the groups in the query exactly

This might prove to be too limiting for many people, but if these things don't pose a problem, it can be a bit of a timesaver.

An additional approach, documented on the Microsoft community, talks about how to use a slight variant of this technique to create custom template elements. For instance, a corporate logo with no data source (since it's not needed) — for reuse in other projects.

Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a regular contributor to various sections of TechTarget as well as other publications. He hosts the Web site, where he posts regularly about Windows and has an ongoing feature guide to Vista for emigrants from Windows XP.

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.