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!
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,
LikeLike
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).
LikeLike
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
‘========================================================================================================================================================================
LikeLike
Thanks for sharing, Hendrik.
LikeLike