SharePoint Reporting Services Subscription Management using PS Scripting

One of the more tedious things to manage in SharePoint are the SQL Reporting Services reports.  Going through item by item to configure the data sources, parameters and shared schedule processing can take a bit of time.  Fortunately, there is a PowerShell option for doing some management of reports in SharePoint, although it is not very well documented.

Let’s say you have a batch of SSRS reports that you have just added new subscriptions for, and you want to clear out the old subscriptions.  Rather than go through each report individually, you can just run a bit of PS and remove all the old entries.

First hop on the SharePoint management shell, and then set up a web proxy to the reports server:

$websvc = New-WebServiceProxy -Uri "http://spreportssite-local.contoso.com/_vti_bin/ReportServer/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential;

Next, pull the subscriptions for the site you want to manage:

$siteSubs = $websvc.ListSubscriptions("https://spreportssite.contoso.com/NYCOffice");

$siteSubs | select report, owner, status, lastexecuted, subscriptionid | format-table -auto

Then you will want to filter this down futher, to just get the subscriptions you want to remove:

$clearSubs = $siteSubs | select report, owner, status, lastexecuted, subscriptionid | where {$_.Status -ne "New Subscription"}

$clearSubs | select report, owner, status, lastexecuted, subscriptionid | format-table -auto

If the list looks good, then run the following to clear out all the old subscriptions:

foreach ($sub in $clearSubs) {$websvc.DeleteSubscription($sub.SubscriptionID)}
Posted in SharePoint 2013.

Leave a Reply

Your email address will not be published. Required fields are marked *