SSRS How to Get Report Cache Options With Powershell?

Question

In SSRS web interface, after clicking on a report and going to Manage --> Caching if a report is configured to "Always run this report against pregenerated snapshots" there is a Cache snapshots section with an option "Create cache snapshots on a schedule"

I have been messing around with PowerShell and trying to create a scriptthe finds all of the reports where this option is set, and output the schedule.

I have this script that iterates over each report with "Execution Type" of "Snapshot", but I believe I'm calling the wrong method (GetCacheOptions), as all it returns is False for each item:

Clear-Host 

$webServiceUrl = 'http://myReportServer.domain.com/reportserver/reportservice2010.asmx?WSDL'

$rs = New-WebServiceProxy -Class 'RS' -Namespace 'RS' -Uri $webServiceUrl -UseDefaultCredential

$reports = $rs.ListChildren("/Some Folder", $true) | Where-Object { $_.TypeName -eq "Report" }

$schedDef = New-Object RS.ScheduleDefinition
$expDef = New-Object RS.ExpirationDefinition

foreach ($report in $reports) {

    $execType = $rs.GetExecutionOptions($report.Path, [ref]$schedDef.Item)


    if($execType -eq "Snapshot") {
        $rs.GetCacheOptions($report.Path, [ref]$expDef.Item)
    }

}

Does anyone know what method needs to be called to get this information and how to call this method (i.e. what parameters needs to be supplied)?

EDIT:

Per guidance from accepted answer, I was able to make some edits (below) and I'm not getting the schedule information I desire:

.......
....
foreach ($report in $reports) {

    $execResult = $rs.GetExecutionOptions($report.Path,
        [ref]$ScheduleDefinitionOrReference)

    if ($execResult -eq "Snapshot") {
        if($ScheduleDefinitionOrReference.Item -is [RS.DailyRecurrence]) {
            Write-Host "$($report.Name):" -f Green
            Write-Host "`tSchedule Information:" -f Yellow
            Write-Host "`t`tEvery $($ScheduleDefinitionOrReference.Item.Daysinterval) Day(s)"
            Write-Host "`t`tStart Time: $($ScheduleDefinitionOrReference.StartDateTime)`n"
        }

    }

Reza Aghaei · Accepted Answer · 2017-12-04T18:39:05.040

2

You can use ReportingService2010.GetItemHistoryOptions and pass the ItemPath, and out bool KeepExecutionSnapshots, and out ScheduleDefinitionOrReference to the method.

ScheduleDefinitionOrReference will contain the ScheduleDefinition if you check Create cache snapshots on a schedule, otherwise its value will be NoSchedule.

Example

$svcUrl = 'http://the-host-name/ReportServer/reportservice2010.asmx'
$svc = New-WebServiceProxy -Class 'RS' -Namespace 'RS' -Uri $svcUrl -UseDefaultCredential
$reports = $svc.ListChildren("/", $true) | Where-Object { $_.TypeName -eq "Report" }

$KeepExecutionSnapshots = $false
$ScheduleDefinitionOrReference = New-Object RS.ScheduleDefinitionOrReference

foreach ($report in $reports) {
    $svc.GetItemHistoryOptions($report.Path, 
        [ref]$KeepExecutionSnapshots, 
        [ref]$ScheduleDefinitionOrReference)
    $report.Path
    $ScheduleDefinitionOrReference
}

edited Dec 04 '17 at 18:39

answered Dec 04 '17 at 18:17

Reza Aghaei

120,393
18
203
398

Not exactly what I was looking for, but you got me going in the right direction! Thanks! I was able to use the GetExecutionOptions() method, but I was misunderstanding how to properly use the [ref] variable. Included some updates to my answer just for anyone to see what/how I was able to get what I needed working. Thanks again! – Fernando Vega Dec 04 '17 at 22:18
FYI, won't let me award bounty for 18 hours. I will be sure to do so once I am able! – Fernando Vega Dec 04 '17 at 22:21
You're welcome. I tested `GetItemHistoryOptions`, as I described if you check *Create cache snapshots on a schedule* the ScheduleDefinitionOrReference parameter will contain the schedule object, otherwise its value will be NoSchedule. So I guess you can use it as well. I didn't checked GetExecutionOptions but based on the documentations, it does the trick for you. If the execution options for an item do not contain schedule information, the output parameter is the schedule otherwise the Item parameter is a NoScheduleobject. – Reza Aghaei Dec 05 '17 at 02:13

SSRS How to Get Report Cache Options With Powershell?

1 Answers1