Monitoring Reporting Services Subscriptions

It is always a good idea to follow how automated processes you have created on your server are running.  That is why recently I’ve designed a couple reports that monitor Reporting Services subscriptions and have the ability to restart subscriptions if need be.  I hope you find this article useful and adapt the source code to best fit your needs.

Subscription Monitoring Report

The first report I need to design will monitor my subscription executions.   Follow the steps below to create your own but feel free to make changes where appropriate for your environment.

1.       Create a New Shared Data Source to the ReportServer database and call it ReportServer

2.       Create a New Shared Data Source to the MSDB database and call it MSDB

3.       Create a New Report and call it Subscription Monitoring Report

4.       Add the ReportServer Shared Data Source to the report in the Report Data window

5.       Add a Dataset that uses the following query:

SELECT CAST(R.ScheduleID as Varchar (100))as ScheduleID

      ,Description

      ,Status

      ,Format

      ,E.Parameters

      ,TimeStart

      ,TimeEnd

  ,Replace(Substring(DataSettings,Charindex('<CommandText>',CAST(DataSettings as Varchar(8000))),Charindex('</CommandText>',CAST(DataSettings as Varchar(8000)))-Charindex('<CommandText>',CAST(DataSettings as Varchar(8000)))),'<CommandText>','')As Query

      ,DateDiff(s,TimeStart, TimeEnd) as RunTime

  FROM Subscriptions S

  join ReportSchedule R on R.SubscriptionID=S.SubscriptionID

  join ExecutionLogStorage E on E.ReportID=R.ReportID

  Where RequestType = 1

  and TimeStart between GetDate()-30 and Getdate()

6.       Drag a Table from the toolbox onto the design surface

7.       Drag the ScheduleID field into the Row Group pane above the (Details) group

8.       Right-click on the Grouping bar in the table and select Insert Row -> Inside Group – Above.  Then shift the ScheduleID field up one row.

9.       Add three columns inside the new ScheduleID group to the right of the ScheduleID column in the Table.  Populate the three new columns with the Description, Query, and Format fields.

10.   Add the fields Status, Parameters, Time Start, Time End, and Run Time to the (Detail) group on the third row.

11.   Add three columns inside the new ScheduleID group to the right of the ScheduleID column in the Table.  Populate the three new columns with the Description, Query, and Format fields.

12.   Add the fields Status, Parameters, Time Start, Time End, and Run Time to the (Detail) group on the third row.

13.   Make the (Detail) group Toggle by the ScheduleID by opening the (Details) group properties visibility to be hidden by default and toggled on ScheduleID.  Also, make the third row visibility toggle the same way you just configured the grouping.

Manually Run Subscription Report

1.       Create a New Report and call it Manually Run Subscription

2.       Add the MSDB Shared Data Source to the report in the Report Data window

3.       Add a Dataset that uses the following query:

Select Name, S.Description

from msdb.dbo.sysjobs Sys

join ReportServer.dbo.ReportSchedule R on Cast(R.ScheduleID as nvarchar(128)) = Sys.Name

join ReportServer.dbo.Subscriptions S on R.SubscriptionID=S.SubscriptionID

4.       Add a second Dataset that uses the following query:

Exec sp_start_job @jobname

5.       Drag in a textbox to the design surface so the user knows what the report does.  Write an expression like this:

=”Running this report will start the SQL Agent job called “+Parameters!jobname.Value+”.”

6.       This will create a Parameter called @jobname.  Change the available values of the Parameter to use the content of the first dataset created.

7.       Now go back to the Subscription Monitoring Report and create an action on the ScheduleID textbox to go to the Manually Run Subscription Report

Next you can add any addition formatting you would like before deploying.  For example, it would be useful for the user to see the ScheduleID column underlined and in blue text so it would look like a hyperlink.  The final result may look something like this (I’ve added a quick chart):

Subscription Monitoring Report

Manually Run Subscription Report

Here are the completed reports that you can download.

If you have any other additional questions about subscriptions or reporting in general please email me at dknight@pragmaticworks.com or post to our forum.