SSRS PowerShell deserialize report to XML

Useful script/function to get report definition to XML. You can modify the xml and serialize it back to Report Server – so easy. Go to Connect to Reporting Services with PowerShell, to get script that connect to SSRS Web Service.

Parameters:

• $proxy – proxy Web Service
  
• $path – report path
  

function DeserializeReportDefinitionToXml($proxy,$path)
 {

[System.Xml.XmlDocument] $rdlFile = New-Object System.Xml.XmlDocument;  
[byte[]] $reportDefinition = $null          ;     
[System.IO.MemoryStream] $memStream              | Out-Null;
$reportDefinition                             | Out-Null;
 try{
    $reportDefinition = $proxy.GetReportDefinition($path);
}

catch{   Write-Host "Get report definition problem " $Error[0]
}
 
try{ 
$memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition));
}
catch{
    Write-Host "Problem saving to memory " $Error[0]
}
 try{
$rdlFile.Load($memStream) ;
}
catch{
    Write-Host "Problem load from memory" $Error[0]
}
return $rdlFile
 }

Let’s try. Write-down hierarchy of groups of your Tablix. Use bellow mentioned function, set proxy and Report path (Report should contain Tablix). Be careful of xml namespace used in function. It depends on version of your Report Server.

function TablixRowHierarchy($proxy,$path){
[System.Xml.XmlDocument] $xml | Out-Null;
$xml=DeserializeReportDefinitionToXml $proxy $path ;
[System.Xml.XmlNamespaceManager] $_nsmgr | Out-Null;
 
try{
  $_nsmgr = $xml.nameTable -as [System.Xml.XmlNamespaceManager];
  $_nsmgr.AddNamespace("a", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
}
catch{
 Write-Host "problem create namespace" $Error[0]
}
[int] $level1 = 0;
 
foreach ($node in $xml.SelectSingleNode("/a:Report/a:Body/a:ReportItems/a:Tablix/a:TablixRowHierarchy",$_nsmgr).SelectNodes9"//a:TablixMembers",$_nsmgr))
{
 foreach ($val in $node.SelectNodes("a:TablixMember/a:TablixHeader/a:CellContents/a:Textbox/a:Paragraphs/a:Paragraph/a:TextRuns/a:TextRun/a:Value",$_nsmgr))
{
   Write-Host "Node value " + $val.InnerText 
 }
Write-Host "Node name " + $node.Name
}
}