We recently discovered that there was a bug involving Print Layout mode in the Report Viewer component and SQL Server 2005 Service Pack 2. I was suddenly confronted with the daunting prospect of altering 230+ SQL Server 2005 Reporting Services (SSRS) reports in VS2005, or at least assigning the task to someone. Knowing that SSRS report files are just XML, I suspected that there was an easier way. I had been looking for an excuse to do something with
Powershell and this was it.
After a quick inspection of a few files I was able to build the script below to make the necessary changes to avoid the Print Layout bug. There's definitely room for refactoring because the below script is fairly procedural. But time trumped elegance and Powershell saved a lot of time. The only problem I encountered was that my new elements were being created with an empty xmlns attribute. I'd seen this problem before, but couldn't remember the resolution initially. Google reminded me that I needed to add the new elements to the same namespace as the existing XML document.
$outputDir = "C:\[output directory]\"
$files = Get-ChildItem C:\[path to reports]\*.* -include *.rdl
Write-Host("Found " + $files.Length + " files in " + $files[0].DirectoryName + " directory.`n`r")
foreach ($f in $files)
{
$numberOfModifications = 0
Write-Host($f)
$fileContents = Get-Content $f
$xdoc = New-Object -TypeName System.Xml.XmlDocument
$xdoc.LoadXml($fileContents)
Write-Host("`tBody element")
# Determine whether Body.Style element exists. Apparently Body always exists.
if($xdoc.Report.Body.Style -eq $null)
{
Write-Host("`t`tCreating Body.Style element.")
$styleNode = $xdoc.CreateElement("Style","http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition")
$xdoc.Report.Body.AppendChild($styleNode)
}
else
{
Write-Host("`t`tBody.Style element found.")
$styleNode = $xdoc.Report.Body.Style
}
if(($styleNode -ne $null) -and ($styleNode.BackgroundColor -eq $null))
{
# Note that it's extremely important to qualify the namespace when adding a new element
$xn = $xdoc.CreateElement("BackgroundColor","http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition")
$xn.set_InnerXml("White")
$styleNode.AppendChild($xn)
# $xdoc.Save($outputDir + $f.Name)
$numberOfModifications += 1
}
else
{
Write-Host("`t`tNo modifications necessary.")
}
$styleNode = $null
$xn = $null
#
# PageHeader
#
Write-Host("`tPageHeader element")
if($xdoc.Report.PageHeader -ne $null)
{
$headerNode = $xdoc.Report.PageHeader
# Determine whether PageHeader.Style element exists.
if($headerNode.Style -eq $null)
{
Write-Host("`t`tCreating PageHeader.Style element.")
$styleNode = $xdoc.CreateElement("Style","http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition")
$headerNode.AppendChild($styleNode)
}
else
{
Write-Host("`t`tPageHeader.Style element found.")
$styleNode = $headerNode.Style
}
if(($styleNode -ne $null) -and ($styleNode.BackgroundColor -eq $null))
{
# Note that it's extremely important to qualify the namespace when adding a new element
$xn = $xdoc.CreateElement("BackgroundColor","http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition")
$xn.set_InnerXml("White")
$styleNode.AppendChild($xn)
$numberOfModifications += 1
}
else
{
Write-Host("`t`tNo modifications necessary.")
}
}
else
{
Write-Host("`t`tPageHeader element not found.")
}
$styleNode = $null
$xn = $null
#
# PageFooter
#
Write-Host("`tPageFooter element")
if($xdoc.Report.PageFooter -ne $null)
{
$footerNode = $xdoc.Report.PageFooter
# Determine whether PageHeader.Style element exists.
if($footerNode.Style -eq $null)
{
Write-Host("`t`tCreating PageFooter.Style element.")
$styleNode = $xdoc.CreateElement("Style","http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition")
$footerNode.AppendChild($styleNode)
}
else
{
Write-Host("`t`tPageFooter.Style element found.")
$styleNode = $footerNode.Style
}
if(($styleNode -ne $null) -and ($styleNode.BackgroundColor -eq $null))
{
# Note that it's extremely important to qualify the namespace when adding a new element
$xn = $xdoc.CreateElement("BackgroundColor","http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition")
$xn.set_InnerXml("White")
$styleNode.AppendChild($xn)
$numberOfModifications += 1
}
else
{
Write-Host("`t`tNo modifications necessary.")
}
}
else
{
Write-Host("`t`tPageFooter element not found.")
}
$styleNode = $null
$xn = $null
# Done with this iteration.
if($numberOfModifications -gt 0)
{
Write-Host("`tSaving file.")
$xdoc.Save($outputDir + $f.Name)
}
else
{
Write-Host("`tNo file changes necessary.")
}
$xdoc = $null
}
