Reporting Services – Page 11 – SSRS, Reporting Services, SQL Server, Business Inteligence, Pentaho, T-SQL

August 7, 2019August 7, 2019

SSRS linked report

Linked reports are very nice feature of Reporting Services, I found it very uses full since it gives you possibility to make many reports from your base report by changing parameters default values. It like Windows shortcut when you create child report from your parent report. This child, linked report does not have its own definition. Definition is taken from parent report, but you can set different parameters and it gives you lots of possibilities for implementation scenarios.

You can make for example different naming convention and structure in your reporting services repository when you can prepare different organisation structure for reports based on its usage or you can develop reports with predefined parameters based on user needs. In organisation structure each user could have different requirements for final view of report.

Linked Reports are created in SSRS Report Manager.

Goal

Create Linked report to Sales Report with default Region code, Australia.

Go to Reporting Services Report Manager to path where is situated report you use like base report. Click on arrow next to the right to the Report name. You can use one of report we created in previous post SSRS Cascading Parameters

Select Create Linked Report from appeared menu.

Name Linked Report in next window. You can fill Description too.

By confirming previous step, you can see Linked Report in Reporting Services Manager added. See picture bellow.

Go to linked report properties, click on right arrow on Linked Report name, select manage and go to parameters.

Linked report - parameters — Picture 5 – Linked report – parameters

Set default value for the Linked Report parameter RegionCode. You can see that if we would like to set default parameter value for GetProvince parameter, we are not allowed since it has default value defined by Query. IN case we would like to use linked reports, we should think about parameters in advance.

Linked report - parameter default — Picture 6 – Linked report – parameter default

See Linked Report on picture bellow.

Be careful when changing definition of base report. Some changes like changing parameters can invalidate Linked Report and you have to create it again.

August 7, 2019August 7, 2019

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"

August 3, 2019March 21, 2020

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.