Reporting Services Item Templates

Download source code

Download binary (.EXE)

Problem

Last week, I needed to rebuild a couple of reports on SQL Server 2005 Reporting Services, and found I need item template support in Microsoft reports.  How to use item templates in Reporting Services?  Well, in short, you can't.

The report with which I was working is for the finance department. and is formatted like an accounting balance sheet, with data grouped into accounts, months, and then investment funds.  There were some problems with visibility and repeating switching logic; so, I decided to simply rebuild the report, rather than waste time trying to fix the mess.

Due to complications of the data structure and requirements, the report is composed of a list containing 13 surprisingly complex List items -- one for each month, plus a 13th for end-of-year accounting purposes.  Every copy of the control is identical, except for the 3-month abbreviation appears in field names (see image).  This means any tweaks made during testing must be repeated 12 times, either in the design interface or by using search and replace in XML, nether of which is convenient.

Before you start writing the comment about how I'm an idiot for not simply wrapping a single item in a parent list, please be aware this is not possible.  All monthly data for each account is delivered in one record.  To make matters worse, multiple records may exist, which must be selectively displayed for aggregation or not.  On a scale of 1 to 10, I rate this project a 3 Excedrin pills and 8 grimaces for annoyance.

Solution

This report is begging for a template!  Because the reports do not support item templates, I decided to simply create an application that consumes an item template, the character stub (text to search and replace), and the replacement strings.  The application offsets the generated items, so they don't appear stacked on top of each other, in the report.

The application is very basic, and rather self-describing:

1. Enter the stub (string that acts as a placeholder, which will be replaced)
2. Enter the strings that will replace the stub -- a new item will be created for each of these
3. Choose the offset style (horizontal, vertical, or none)
4. Paste the template XML
5. Click the Generate XML button

Accessing the report item XML is easy: right-click the report in Visual Studio's Solution Explorer window, select Open With..., and then select XML Editor in the Open With dialog. Here's a couple of editing tips:

• Arrange the design and XML views of the document side by side is beneficial.
• Collapse XML elements by clicking the [+] icons, to locate items faster
• Use Ctrl + { to jump between XML element opening and closing tags
• When (not if) the design interface displays a big, red X, where the design surface behind the report should be, don't worry -- simply re-open the design view, if you find the X annoying

Creating an item template is easy.  Simply stub in the field and item names (these screenshots use "XXX").  It is important to stub the names of internal items, too; otherwise, you will end up with duplicate item names, causing the report to fail.

The resulting XML produces a nice series of item elements.

As mentioned above, the generated items may be offset vertically (as seen in the image above), horizontally, or not at all (stacked on top of each other, on the Z axis).  The following screenshot illustrates the output.

Source code and compiled binary (.EXE) file for the tool may be downloaded, here.  This is not licensed software -- it is very basic and I'm not so pretentious to copyright it.

Download source code

Download binary (.EXE)