Teaching good old SSRS a new trick…

Background

Not too long ago I had a client with a very simple, yet practical requirement: they wanted all of their SSRS reports to have standard headers and footers. Nothing ground breaking there, however, the twist that they put on this requirement was a very useful one in that they wanted to use a single template file to apply the headers and footers. Even more, they wanted this template to be easily updatable, meaning any update to the template would apply the changes to all reports simultaneously. Ah, now we’re on to something more interesting.

The original solution that I developed with a colleague to meet this requirement was based on local report definitions (.rdlc – or SSRS without the reportserver), but I thought it would be interesting, and hopefully helpful to others to offer a solution that could be applied against the full version of SSRS (stand alone and SharePoint Integrated). So, here we go…

Overview

A few points of information about SSRS architecture before we get to specifics (for those who might not already be familiar). The .rdl files that define report definitions are generally authored in either visual studio or report builder. These files are actually xml documents that reference a published schema provided by Microsoft (for SSRS 2012 and 2014, the schema is Report Definition Language (RDL) 2010: http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition/

SSRS also offers a robust API… and even better for the less .Net savvy such as myself, SSRS comes complete with a command line executable utility that will compile and run a “Visual Basic .NET code file, written against a proxy that is built on Web Service Description Language (WSDL), which defines the Reporting Services SOAP API”. This is called the rs Utility, or (rs.exe): http://msdn.microsoft.com/en-us/library/ms157252(v=sql.110).aspx

In the past, I have only used the rs Utility for scripting deployments and backups; but, why not use it to update report definitions? It already exposes the necessary web service method: SetItemDefinition.

Solution

The implementation is actually fairly straight forward (after I racked my brain for a couple of days). As I said before, the original solution ran within a custom app that implemented LocalReport. We simply updated the appropriate .rdlc XML elements at run time against the defined template document. Transposing to the full report server, we just need to identify the template document on the server, then apply the appropriate XML elements to all of the report files in a loop instead.

A couple of things to be aware of about your template and report .rdl files.

1) Make sure all of the items in your Custom.rdl file are named creatively, as in something that a report developer wouldn’t normally use. When the script runs, it will replace items in the header and footer with your report items in the template. SSRS requires that all items within a report definition have unique names, otherwise the definition is not valid. So, If you have a textbox in your template named Textbox1 and you try update all of your report files, there’s an excellent chance that you’ll end up with the following error:

I suggest naming each report item in the header like “CustomHeaderImageLeft20141114”.

2) Don’t use an embedded image in your template file. This is because the script just won’t work If you do, but also, if you’re applying the image to all of your report definitions then you’re better off using an external reference image anyways. The image itself can just be uploaded to your report server.

So, here’s the code for the script file (.rss):

'Script to apply custom header and footer from Custom.rdl file saved to ReportServer to all report files on ReportServer
' uses Mgmt2010 endpoint / executable against stand alone or SharePoint integrated instance
' Save file as .rss extension and run using rs.exe from command line.
'Authored by Jared Zagelbaum 11/2014    jaredzagelbaum.wordpress.com
Sub Main()
Dim reportDefinition As Byte() = Nothing
Dim doc As New System.Xml.XmlDocument
Dim nsmanager As New XmlNamespaceManager(doc.NameTable)
Dim templateHeader As System.Xml.XmlElement
Dim templateFooter As System.Xml.XmlElement
Dim reportHeader As System.Xml.XmlElement
Dim reportFooter As System.Xml.XmlElement
nsmanager.AddNamespace("rd", "http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition")
Dim items As CatalogItem() = rs.ListChildren("/", True)
'find template file
For Each item As CatalogItem In items
If item.TypeName = "Report" And item.Name = "Custom" Then
reportDefinition = rs.GetItemDefinition(item.Path)
Dim stream As New MemoryStream(reportDefinition)
doc.Load(stream)
Exit For
End If
Next
'load template header / footer into var
templateHeader = doc.SelectSingleNode("/rd:Report/rd:ReportSections/rd:ReportSection/rd:Page/rd:PageHeader", nsmanager)
templateFooter = doc.SelectSingleNode("/rd:Report/rd:ReportSections/rd:ReportSection/rd:Page/rd:PageFooter", nsmanager)
'iterate through catalog items and replace report headers with template
For Each item As CatalogItem In items
If item.TypeName = "Report" And item.Name <> "Custom" Then
reportDefinition = rs.GetItemDefinition(item.Path)
Dim stream As New MemoryStream(reportDefinition)
Dim outstream As New MemoryStream()
doc.Load(stream)
reportHeader = doc.SelectSingleNode("/rd:Report/rd:ReportSections/rd:ReportSection/rd:Page/rd:PageHeader", nsmanager)
reportFooter = doc.SelectSingleNode("/rd:Report/rd:ReportSections/rd:ReportSection/rd:Page/rd:PageFooter", nsmanager)
reportHeader.InnerXml = templateHeader.InnerXml
reportFooter.InnerXml = templateFooter.InnerXml
doc.Save(outstream)
reportDefinition = outstream.ToArray()
rs.SetItemDefinition(item.Path, reportDefinition, Nothing)
stream.Dispose()
outstream.Dispose()
End If
Next
End Sub

Save this script using .rss extension and execute from the command line on your reportserver from any directory.

Parting Thoughts

The great part about coding against the Mgmt2010 endpoint is that the script can run against stand alone or integrated SharePoint mode without change.  However, If you have an older version of SSRS, then you will have to alter the name space reference and xpath to the appropriate version / schema. There may / may not be different API calls involved as well.

One could get even more detailed with this script and add common items to anywhere in a report; datasets, etc. In those cases, it would also be better to create a reference to a shared dataset instead of scripting a new one into every single report.

I originally said that we were teaching SSRS a new trick, but the API / rs.exe utility has been around for several years now. Just like training a dog, most of the work is what the human has to learn. Hope this helped!