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"

Get query plan from cache

SELECT *, OBJECT_NAME(qplan.objectid), SUBSTRING(sqltext.text,st.statement_start_offset/2 +1,CASE
WHEN st.statement_end_offset = -1 THEN
LEN(CONVERT(nvarchar(max), sqltext.text)) * 2 ELSE st.statement_end_offset end
- st.statement_start_offset /2 )
AS query_text FROM
sys.dm_exec_cached_plans cplans INNER JOIN
sys.dm_exec_query_stats st ON st.plan_handle=cplans.plan_handle 
CROSS APPLY sys.dm_exec_text_query_plan (cplans.plan_handle,st.statement_start_offset,st.statement_end_offset) qplan CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) sqltext

Script to get query plan from cache. It gets execution plan for particular sql statements.

Reporting services database

Reporting Service databases are used to store objects definitions and their metadata. There are placed two databases – Reporting Services Database and Reporting Services Temporary database. Each instance of reporting services in native mode should have defined these two databases in configuration manager. See post Reporting Services, Basic configuration tips .

Reporting Services database

  • Here is stored information regarding reporting services repository like reports, schedules, data sources users etc. Bellow few tables you may interested in.

Reporting services temporary database

  • Here are stored data used for caching reports

The reporting services database structure is more than clear, based on name of tables. Even so I mention two tables you may be interested in. But as Microsoft states you shouldn’t use your custom queries directly against reporting services database. You should use API to get info you need, except of execution log you can access directly from database. See Microsoft web site for more info https://bit.ly/2Jxn9J6.

  • Catalog table – report server items – reports and theirs pats for example
    • here you can find Report Definitions too. But these data are informative only. Do not expect that you if you change report definition here it changes on Report Server too. For such a purpose you should use Reporting Web Services endpoints or Reporting Services Extensions or hack Chunk and Segment tables where are stored the data reporting services work with in binary form
  • ExecutionLogStorage – information regarding repots rendering, processing and data retrieving.
    • Here are few views pre-pared from Microsoft team to audit reporting services data
    • Dbo.ExecutionLog, dbo.ExecutionLog2, dbo.ExecutionLog3

In next few posts I would like to show you work how you can work with Reporting Service tables since it is good tool how to monitor performance of reports and auditing users access to report serves. Stay in touch.