Update all SSRS reports to apply consistent header / footer on demand

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:

rs exec header 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!

Advertisements

4 thoughts on “Update all SSRS reports to apply consistent header / footer on demand

  1. Hi there Jared,

    I’m trying to use the code above to change an embedded logo ( image ) for all the SSRS reports that we have within a native instance to point to an external image.

    I have the following challenges ( bet a lot of people normally do ). All the reports were migrated from an SSRS 2008 R2 instance to SSRS 2012. The image is not located in the same location ( report XML hierarchy ) in every report. I have an additional challenge in that the image field is not always called the same but shouldn’t make any difference as it is the only image in 99% of the reports ( I can manually update the few where it is not ).

    My problems are the following. I thought I could use normal XPATH to find the node within the report definition by something like this :

    Dim ReportImage as System.Xml.XmlElement

    ReportImage = doc.SelectSingleNode(“/Image”, nsmanager)
    or
    ReportImage = doc.SelectSingleNode(“/rd:Image”, nsmanager)

    But in both cases is does not fill the ReportImage variable with the XML for the node.
    Unless I do the structure exactly correct ( Report/ReportSections/ReportSection/Tablix/ etc../etc…/ ) I do not get the node Xml.

    Do you know if it is at all possible to use normal XPATH rules ( RDL is nothing more than XML document really ) to locate the Image tag, or is my only alternative to use the API ( or a BCP command ) to extract the Report definition to a temporary file on disk, read that as a normal XMLdocument, use XPATH without the nsmanager(Report Namespace) and do the XSLT transformation and then use the API to upload / replace the current definition ( in SSRS ) with the newly changed one ?

    Your feedback in this regard will be greatly appreciate.

    Kind regards,

    Like

    • Hi Hendrik,

      Thanks for your question. The solution I would suggest is to add some error handling to the script and alter the element variables to replace just the image element instead of the entire header / footer element. The image element could be in the body as you described as well, so the schema definition should adhere to the following:

      templateImageFooter = doc.SelectSingleNode(“/rd:Report/rd:ReportSections/rd:ReportSection/rd:Page/rd:PageFooter/rd:Image”, nsmanager)
      templateImageHeader = doc.SelectSingleNode(“/rd:Report/rd:ReportSections/rd:ReportSection/rd:Page/rd:PageHeader/rd:Image”, nsmanager)
      templateImageBody = doc.SelectSingleNode(“/rd:Report/rd:ReportSections/rd:ReportSection/rd:Body/rd:ReportItems/rd:Image”, nsmanager)

      Do a try / catch on each and the first one that you find is the one to replace (assuming you will be updating the reports with more then one image manually as you mentioned in your comment).

      Like

  2. Hi there Jared,

    I eventually wrote this, with your work as inspiration.

    I hope it can help other as well

    ‘Orignal idea taken from this blog post : https://jaredzagelbaum.wordpress.com/category/reporting-services/
    ‘Authored by Jared Zagelbaum 11/2014 jaredzagelbaum.wordpress.com
    ‘Authored by Hendrik Groenewald 07/2015 h c g r o e n e w a l d [at] g m a i l . c o m
    ‘Concept extended to be any node replacement, and in addition also copy all children nodes, and all children nodes of target node that are not replaced by template / source node
    ‘ PLEASE NOTE : de is given to the default XML namespace [ prefix ] therefore any default nodes need to have //de: as prefix in the XPATH search

    ‘========================================================================================================================================================================
    Sub CopyNodes(ByRef TargetDocument As System.Xml.XmlDocument, _
    ByRef TargetNode As System.Xml.XmlNode, _
    ByRef SourceNode As System.Xml.XmlNode, _
    ByVal TargetNameSpaceUri As String, _
    ByVal NodeLevel As Integer _
    )

    ‘Console.Writeline(“”)
    ‘Console.Writeline(“================================================================================”)
    ‘Console.Writeline(“Inside CopyNodes procedure “)
    ‘Console.Writeline(“TargetDocument : ” + TargetDocument.Name)
    ‘Console.Writeline(“TargetNode : ” + TargetNode.Name)
    ‘Console.Writeline(“SourceNode : ” + SourceNode.Name)
    ‘Console.Writeline(“SourceNodeType : ” + SourceNode.NodeType.ToString())
    ‘Console.Writeline(“TargetNameSpaceUri : ” + TargetNameSpaceUri)
    ‘Console.Writeline(“NodeLevel : ” + NodeLevel.ToString())
    ‘Console.Writeline(“================================================================================”)

    If NodeLevel 1 Then ‘Not the Target Root Node

    Dim TargetChildNode As System.Xml.XmlNode = TargetDocument.CreateNode( SourceNode.NodeType, SourceNode.Name , TargetNameSpaceUri )

    For Each attr As System.Xml.XmlAttribute in SourceNode.Attributes
    Dim newAttr As XmlAttribute = TargetDocument.CreateAttribute(attr.Name)
    newAttr.Value = attr.Value
    TargetChildNode.Attributes.Append(newAttr)
    Next ‘For Each attr As System.Xml.XmlAttribute in SourceNode.Attributes

    ‘Console.Writeline(SourceNode.Name.ToString() + ” has children : ” + SourceNode.HasChildNodes.ToString() )
    If SourceNode.FirstChild.NodeType.ToString() = “Text” Or SourceNode.FirstChild.NodeType.ToString() = “CDATA” Then
    TargetChildNode.InnerText = SourceNode.InnerText
    End if

    ‘Console.Writeline(“Append Child Node to TargetNode : ” + TargetNode.Name)
    TargetNode.AppendChild(TargetChildNode)

    For Each childNode As System.Xml.XmlNode in SourceNode.ChildNodes
    If childNode IsNot Nothing And _
    ( childNode.NodeType.ToString() = “Element”) _
    Then
    CopyNodes(TargetDocument, TargetChildNode, childNode, TargetNameSpaceUri, NodeLevel + 1 )
    End If
    Next

    Else

    If Not SourceNode.HasChildNodes Then
    TargetNode.InnerText = SourceNode.InnerText
    End if

    For Each attr As System.Xml.XmlAttribute in SourceNode.Attributes
    Dim newAttr As XmlAttribute = TargetDocument.CreateAttribute(attr.Name)
    newAttr.Value = attr.Value
    TargetNode.Attributes.Append(newAttr)
    Next ‘For Each attr As System.Xml.XmlAttribute in SourceNode.Attributes

    For Each childNode As System.Xml.XmlNode in SourceNode.ChildNodes
    If childNode IsNot Nothing And _
    ( childNode.NodeType.ToString() = “Element”) _
    Then
    CopyNodes(TargetDocument, TargetNode, childNode, TargetNameSpaceUri, NodeLevel + 1 )
    End If
    Next

    End If

    End Sub

    ‘========================================================================================================================================================================

    Sub CopyRemainingChildNodes ( ByRef TargetDocument As System.Xml.XmlDocument, _
    ByRef TargetNode As System.Xml.XmlNode, _
    ByRef SourceNode As System.Xml.XmlNode, _
    ByVal TargetNameSpaceUri As String, _
    ByVal NodeLevel As Integer _
    )

    Dim NodeExistsInTarget As Boolean = False

    For Each SourceChildNode As System.Xml.XmlNode in SourceNode.ChildNodes

    NodeExistsInTarget = false

    For Each TargetChildNode As System.Xml.XmlNode in TargetNode.ChildNodes
    If TargetChildNode.Name = SourceChildNode.Name Then
    NodeExistsInTarget = true
    End if
    Next

    If Not NodeExistsInTarget Then
    ‘Console.Writeline(“Node found in Source that is not in Target : ” + )
    CopyNodes(TargetDocument, TargetNode, SourceChildNode, TargetNameSpaceUri, NodeLevel)
    End If

    Next

    End Sub

    ‘========================================================================================================================================================================

    Sub Main()

    Dim ScriptLoggingLocation as String = “E:\SSRS_Updates\ReportEditing_Update_Logo.log”
    Dim ScriptLoggingReportsWithoutImages As String = “E:\SSRS_Updates\ReportsWithoutImages.log”

    Dim SourceItemName As String = “Capfin_Report_Template” ‘Source Report Name
    Dim SourceNodeDefinition As String = “//de:Image[@Name=””LogoName””]” ‘XPATH for SourceNode

    Dim DestinationItemsDefinition As String = “”
    Dim DestinationNodeDefinition As String = “//de:Image[@Name=””Image1″” or @Name=””Image2″”]”

    Dim docDestination As New System.Xml.XmlDocument

    Dim docSource As New System.Xml.XmlDocument
    Dim nsmanagerdocSource As New XmlNamespaceManager(docSource.NameTable)

    Dim sourceNode As System.Xml.XmlNode
    ‘Dim destinationNode As System.Xml.XmlNode

    Dim reportDefinition As Byte() = Nothing

    ‘ReportItems
    Dim items As CatalogItem() = rs.ListChildren(“/”, True)

    ‘Find Source Document and Node
    For Each item As CatalogItem In items
    If item.TypeName = “Report” And item.Name = SourceItemName Then
    Console.Writeline(“Found template”)

    reportDefinition = rs.GetItemDefinition(item.Path)
    Dim stream As New MemoryStream(reportDefinition)
    docSource.Load(stream)

    ‘Add all Report NameSpaces to SourceNameSpaceManager

    Dim SourceNameSpace_Prefix as String = String.Empty

    For Each attr As System.Xml.XmlAttribute in docSource.DocumentElement.Attributes
    if attr.Name.Contains(“xmlns”) Then
    ‘Console.Writeline(attr.LocalName)
    ‘Console.Writeline(attr.Name)
    ‘Console.Writeline(attr.Prefix)
    ‘Console.Writeline(attr.InnerText)
    ‘Console.Writeline(“==============”)
    ‘Console.Writeline(“”)

    If attr.LocalName = “xmlns” Then
    ‘SourceNameSpace_Prefix = String.Empty
    SourceNameSpace_Prefix = “de”
    Else
    SourceNameSpace_Prefix = attr.LocalName
    End If

    nsmanagerdocSource.AddNamespace(SourceNameSpace_Prefix,attr.InnerText)
    End If
    Next

    Console.Writeline(“Source Node Search Criteria : ” + SourceNodeDefinition)

    ‘Load SourceNode into XmlNode
    sourceNode = docSource.SelectSingleNode(SourceNodeDefinition, nsmanagerdocSource)

    Exit For
    End If ‘If item.TypeName = “Report” And item.Name = SourceItemName Then
    Next ‘For Each item As CatalogItem In items

    If sourceNode IsNot Nothing Then

    Console.Writeline(“SourceNode Found and loaded”)

    ‘Loop through all Reports in Calalog
    Console.Writeline(“Loop through all Destination Items / Reports”)

    For Each item As CatalogItem In items
    If item.TypeName = “Report” _
    And item.Name SourceItemName _
    Then

    Console.Writeline(item.Path)

    reportDefinition = rs.GetItemDefinition(item.Path)

    Dim stream As New MemoryStream(reportDefinition)
    Dim outstream As New MemoryStream()

    docDestination.Load(stream)

    Dim nsmanagerdocDestination As New XmlNamespaceManager(docDestination.NameTable)

    Dim DestinationNameSpace_Prefix as String = String.Empty
    Dim DestinationDefaultNameSpaceUri as String = “”

    For Each attr As System.Xml.XmlAttribute in docDestination.DocumentElement.Attributes
    if attr.Name.Contains(“xmlns”) Then

    If attr.LocalName = “xmlns” Then

    ‘Console.Writeline(attr.LocalName)
    ‘Console.Writeline(attr.Name)
    ‘Console.Writeline(attr.Prefix)
    ‘Console.Writeline(attr.InnerText)
    ‘Console.Writeline(“==============”)
    ‘Console.Writeline(“”)

    ‘DestinationNameSpace_Prefix = String.Empty
    DestinationNameSpace_Prefix = “de”
    DestinationDefaultNameSpaceUri = attr.InnerText
    Else
    DestinationNameSpace_Prefix = attr.LocalName
    End If

    ‘Console.Writeline(“Adding Namespace to manager ” + DestinationNameSpace_Prefix )
    nsmanagerdocDestination.AddNamespace(DestinationNameSpace_Prefix,attr.InnerText)

    ‘Console.Writeline(“Namespace Added : ” + DestinationNameSpace_Prefix )
    ‘Console.Writeline(“”)

    End If ‘if attr.Name.Contains(“xmlns”) Then
    Next ‘For Each attr As System.Xml.XmlAttribute in docDestination.DocumentElement.Attributes

    ‘Create ReplacementNodeTemplate
    Dim ReplacementNodeTemplate As System.Xml.XmlNode = docDestination.CreateNode( sourceNode.NodeType, sourceNode.Name , DestinationDefaultNameSpaceUri )

    CopyNodes(docDestination, ReplacementNodeTemplate, sourceNode, DestinationDefaultNameSpaceUri, 1)

    ‘Locate all Matching Destination Nodes
    For Each DestinationNode as XmlNode in docDestination.SelectNodes(DestinationNodeDefinition, nsmanagerdocDestination)

    Dim DestinationNodeValue_Text As String = “”

    For NodeCounter As Integer = 0 To (DestinationNode.ChildNodes.Count-1)
    if DestinationNode.ChildNodes(NodeCounter).Name = “Value” Then
    DestinationNodeValue_Text = DestinationNode.ChildNodes(NodeCounter).InnerText
    Exit For
    End If
    Next

    ‘If DestinationNodeValue IsNot Nothing Then
    ‘ Dim DestinationNodeValue_Text = DestinationNodeValue.InnerText

    If DestinationNodeValue_Text = “capfin_logo” _
    or DestinationNodeValue_Text = “CapfinLogo” _
    or DestinationNodeValue_Text = “CapfinLogo_New” _
    or DestinationNodeValue_Text = “CapfinLogo2” _
    or DestinationNodeValue_Text = “SmartLogo” _
    Then

    ‘ScriptLoggingLocation
    Console.Writeline(item.Path + ” : Matching Node Found : ” + DestinationNode.Attributes(“Name”).Value.ToString())
    File.AppendAllText(ScriptLoggingLocation, DateTime.Now.ToString() + ” : ” + item.Path + ” : Matching Node Found : ” + DestinationNode.Attributes(“Name”).Value.ToString() + Environment.NewLine)

    ‘Identify ParentNode – Replace DestinationNode with ReplacementNode
    Dim DestinationParentNode As System.Xml.XmlNode = DestinationNode.ParentNode

    Dim ReplacementNode As System.Xml.XmlNode = ReplacementNodeTemplate

    ‘Copy Target Children Nodes, which are not part of Source Node
    CopyRemainingChildNodes(docDestination, ReplacementNode, DestinationNode, DestinationDefaultNameSpaceUri, 2)

    ‘Console.Writeline(DestinationParentNode.Name)
    Console.Writeline(item.Path + ” : Replace DestinationNode with ReplacementNode : ” + DestinationNode.Attributes(“Name”).Value.ToString() )
    DestinationParentNode.ReplaceChild( ReplacementNode ,DestinationNode)

    ‘If The SourceNode is an Image with External Source, replacing an embedded image within report, remove embedded image, check if last Embedded image,if so remove ParentNode as well
    If ReplacementNode.Name = “Image” And ReplacementNode.ChildNodes(0).InnerText = “External” And DestinationNode.ChildNodes(0).InnerText = “Embedded” Then

    Dim ImageEmbeddedSourceName as String = “”

    For NodeCounter As Integer = 0 To (DestinationNode.ChildNodes.Count-1)
    if DestinationNode.ChildNodes(NodeCounter).Name = “Value” Then
    ImageEmbeddedSourceName = DestinationNode.ChildNodes(NodeCounter).InnerText
    Exit For
    End If
    Next

    Console.Writeline(DestinationNode.Attributes(“Name”).Value.ToString() + ” : ImageEmbeddedSourceName : ” + ImageEmbeddedSourceName)

    Dim EmbeddedImageNode_Criteria AS String = “//de:EmbeddedImage[@Name=””” + ImageEmbeddedSourceName + “””]”

    Dim EmbeddedImageNode As System.Xml.XmlNode = docDestination.SelectSingleNode(EmbeddedImageNode_Criteria, nsmanagerdocDestination)

    If EmbeddedImageNode IsNot Nothing Then

    If EmbeddedImageNode.ParentNode.ChildNodes.Count > 1 Then

    Console.Writeline(item.Path + ” : Only Removed Embedded Image : ” + ImageEmbeddedSourceName)
    ‘File.AppendAllText(ScriptLoggingLocation, item.Path + ” : Only Removed Embedded Image : ” + ImageEmbeddedSourceName + Environment.NewLine)

    EmbeddedImageNode.ParentNode.RemoveChild(EmbeddedImageNode)
    Else
    Console.Writeline(item.Path + ” : Removed Embedded Image + Parent Node : ” + ImageEmbeddedSourceName)
    ‘File.AppendAllText(ScriptLoggingLocation, item.Path + ” : Removed Embedded Image + Parent Node : ” + ImageEmbeddedSourceName + Environment.NewLine)

    Dim EmbeddedImageNodeParentNode As System.Xml.XmlNode = EmbeddedImageNode.ParentNode

    EmbeddedImageNode.ParentNode.RemoveChild(EmbeddedImageNode)
    EmbeddedImageNodeParentNode.ParentNode.RemoveChild(EmbeddedImageNodeParentNode)

    End If

    End If

    End if

    End if
    ‘End if
    Next ‘For Each DestinationNode as XmlNode in docDestination.SelectNodes(DestinationNodeDefinition, nsmanagerdocDestination)

    docDestination.Save(outstream)
    reportDefinition = outstream.ToArray()
    rs.SetItemDefinition(item.Path, reportDefinition, Nothing)

    stream.Dispose()
    outstream.Dispose()

    End If ‘If item.TypeName = “Report” And Item.Name SourceItemName Then
    Next ‘For Each item As CatalogItem In items

    Else
    Console.Writeline(“SourceNode is Nothing”)
    End if ‘If sourceNode IsNot Nothing Then

    End Sub
    ‘========================================================================================================================================================================

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s