Posted on

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
}
}
Posted on

SSRS Report PowerShell

I would like to follow my last post regarding PowerShell interaction with Reporting Services. I prepared few posts, which demonstrate PowerShell manipulation with Reporting Services. I will publish them continuously. In near future you can be looking forward to:

  • Download reports
  • Upload reports
  • Copying reports
  • Report serialization/de-serialization to XML
  • Create linking report
  • Create subscription
Posted on

SSRS PowerShell download report

In this post I will show you how to download Report form Report Server. This post is part of PowerShell series. You need to connect to Reporting Services endpoint see post Connect to Reporting Services with PowerShell

We use Reporting Services web method GetReportDefinition returning array of bytes. It will be encapsulated to DownloadReport function see script bellow.

  • $proxy – Web Service Proxy
  • $source – Report Server Report path {/Home/MyReports}
  • $destination – destination file path {c:\dir}
#downloadreport
function DownloadReport($proxy,$source,$destination){
$reportDefinition | Out-Null
try{
     $reportDefinition = $proxy.GetReportDefinition($source)
}
catch
{ Write-Host " Problem to Get Report Definition call " $Error[0]
}
try{
    [System.IO.FileStream] $fs | Out-Null 
    $fs = [System.IO.File]::OpenWrite($destination+"\"+$ReportItem.Name+".rdl")
                             $fs.Write($reportDefinition,0,$reportDefinition.Length)
                             $fs.Close()             
     }
 
catch
{ Write-Host " Problem With saving report definiton to file " $Error[0]
}
 
}

To support mass download of reports lets prepare another one function using previous function together with ListChildren webservice method.

function DownloadReports($proxy,$source,$destination){
 [Byte[]]                                 $reportDefinition | Out-Null

[SSRS.ReportingService2005.CatalogItem] $ReportItem       | Out-Null
$testpath = Test-Path $destination

if ( $testpath -eq $false )
{ 
Write-Host "Wrong path"
return 0
}

try{

foreach ($ReportItem in $proxy.ListChildren($source,$false))
{
If ($ReportItem.Type -eq [SSRS.ReportingService2005.ItemTypeEnum]::Report){
        DownloadReport $proxy $ReportItem.Path $destination
    }
}
}
catch
{ Write-Host " List children method problem " $Error[0]
}
 
return 1
 
}

See example bellow

$proxy = ConnectToWebService "C:\config.xml"
DownloadReports $proxy "/MyReports" "C:\dir_for_reports"