Reporting Services offers two "out of the box" methods to manage subscriptions (subscriptions are scheduled report executions, controlled by Reporting Services):
- Each report has a "Subscriptions" tab (depending on permissions) which shows your/all subscriptions (again, depending on permissions) for that report only
- All your subscriptions can be managed using the "My Subscriptions" link in the top right-hand corner of Report Manager
There's one key thing missing from the two methods: how does an administrator or power user manage all subscriptions in Reporting Services?
The Query
I'm going to provide a starting point for a better way to manage Reporting Services Subscriptions - a query that looks at the ReportServer database directly and lists all Subscriptions.
There are plenty of ways this could be improved. Two improvements that spring to mind are copying the data out of the ReportServer database on a schedule so you're not querying live, OLTP-type data, and to further develop the query into a report. Maybe I'll deal with those enhancements in a future blog post.
Back to the query. I've tested it in SQL Server 2005 only, so I'll invoke my "Thomas Williams sez" rule to use at your own risk. And I should add another warning: since the tables aren't documented, they could be changed and this query could fail. Lastly, I haven't covered the SELECT permissions required to run this query here. You've been warned!
Some XML columns are involved so the query looks a little convoluted initially:
USE [ReportServer]
GO
;WITH [SUBJECT]([SubscriptionID], [SubjectLine]) AS
(
--CTE with Subject lines from e-mail Subscriptions from XML like:
-- <ParameterValues>
-- <ParameterValue>
-- <Name>TO</Name>
-- <Value>email@address.com</Value>
-- </ParameterValue>
-- <ParameterValue>
-- <Name>IncludeReport</Name>
-- <Value>True</Value>
-- </ParameterValue>
-- <ParameterValue>
-- <Name>RenderFormat</Name>
-- <Value>PDF</Value>
-- </ParameterValue>
-- <ParameterValue>
-- <Name>Subject</Name>
-- <Value>THE SUBJECT LINE</Value>
-- </ParameterValue>
-- ..
-- </ParameterValues>
SELECT I.[SubscriptionID],
--just get the subject line here
I1.rows.value('Value [1]', 'VARCHAR(500)') AS [SubjectLine]
FROM (
--if the Subscription is an e-mail, get the XML fragment which contains the subject line
SELECT S.[SubscriptionID],
--add a "root" element to create well-formed XML to the "ExtensionSettings"
--(in case it's NULL)
CONVERT(XML, N'<Root>' + CONVERT(NVARCHAR(MAX), S.[ExtensionSettings]) + N'</Root>') AS [ExtensionSettings]
FROM ReportServer.dbo.[Subscriptions] S WITH (NOLOCK)
WHERE --only get e-mail subscriptions
S.[DeliveryExtension] = 'Report Server Email'
) I CROSS APPLY
--pull out elements in the "ParameterValues/ParameterValue" hierarchy
I.ExtensionSettings.nodes('/Root/ParameterValues/ParameterValue') AS I1(rows)
WHERE --only get the Subject field
I1.rows.value('Name [1]', 'VARCHAR(100)') = 'Subject'
)
--get subscription data for all users
SELECT --unique ID for this Subscription
S.[SubscriptionID],
--is the subscription Inactive (<> 0)?
S.[InactiveFlags],
--XML fragment which contains PATH (if file) or TO (if e-mail)
--also has render settings like "render format"
S.[ExtensionSettings],
--e-mail subject (if an e-mail subscription)
[SUBJECT].[SubjectLine],
--when the subscription was modified
S.[ModifiedDate],
--internally put-together description of subscription
S.[Description],
--user-friendly message for what happened the last time the subscription ran
--which may be "New Subscription"
S.[LastStatus],
--is this a "TimedSubscription" or one-off
S.[EventType],
--XML fragment describing the timing and recurrence
S.[MatchData],
--the time the subscription was last run (may be NULL)
S.[LastRunTime],
--is this an e-mail ("Report Server Email") or file share ("Report Server FileShare")?
S.[DeliveryExtension],
--start date and end date for schedule
SC.[StartDate], SC.[EndDate],
--other schedule information (we could get exactly the schedule here, but needs
--to be re-assembled from multiple fields)
--???
SC.[Flags], SC.[RecurrenceType], SC.[State],
--report path and name
C.[Path], C.[Name],
--owner name
[U1].[UserName] AS [Owner],
--modified by name
[U2].[UserName] AS [ModifiedBy],
--URL direct to the subscription
[URL] = 'http://<YOUR_SERVER_HERE>/Reports/Pages/SubscriptionProperties.aspx?ItemPath=' + C.[Path] + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)),
--URL to the "Subscriptions" tab on the report (which can be used to delete the subscription)
[URL2] = 'http://<YOUR_SERVER_HERE>/Reports/Pages/Report.aspx?ItemPath=' + C.[Path] + '&SelectedTabId=SubscriptionsTab'
FROM --actual subscriptions
ReportServer.dbo.[Subscriptions] S WITH (NOLOCK) LEFT OUTER JOIN
--report details from Catalog
ReportServer.dbo.[Catalog] C WITH (NOLOCK) ON
S.[Report_OID] = C.[ItemID] LEFT OUTER JOIN
--Users (owner)
ReportServer.dbo.[Users] [U1] WITH (NOLOCK) ON
S.[OwnerID] = [U1].[UserID] LEFT OUTER JOIN
--Users (modified by)
ReportServer.dbo.[Users] [U2] WITH (NOLOCK) ON
S.[ModifiedByID] = [U2].[UserID] LEFT OUTER JOIN
--Subscription Schedules
ReportServer.dbo.[ReportSchedule] RS WITH (NOLOCK) ON
S.[SubscriptionID] = RS.[SubscriptionID] LEFT OUTER JOIN
--Schedules
ReportServer.dbo.[Schedule] SC WITH (NOLOCK) ON
RS.[ScheduleID] = SC.[ScheduleID] LEFT OUTER JOIN
--Subjects CTE from e-mail subscriptions
[SUBJECT] ON
S.[SubscriptionID] = [SUBJECT].[SubscriptionID]
The first bit of SQL in the "SUBJECT" common table expression gets the "Subject" line for e-mail subscriptions only. The default screens in Report Manager do not display the subject, but it came in handy for me so I've left it here.
Next, the query pulls all the relevant (and some not-so relevant) fields from the Subscriptions table, joined with the Catalog table, the Users table (twice, once for owner, once for "modified by"), the Report Schedule table and the Schedule table. These tables aren't documented at MSDN, as far as I can tell, but I've gathered together as much as I can find of column descriptions into comments in the query.
If you want this script to generate the correct URLs for your server, replace "<YOUR_SERVER_HERE>" with your server name. I do not recommend using the tables for changing data, but you can use the URL's generated by this query to change or delete existing subscriptions using the Report Manager interface.
Related Posts on Thomas Williams Tech Blog: Hiding and Showing Columns Based On a Parameter in Reporting Services, Quick Tip: Retrieving Report Definitions from the Catalog Table in the ReportServer Database, Reporting Services Heatmap, Leaving out Parameters on SQL Reporting Services
Tags: sql server, reporting services, subscription, reportserver, database
posted @ Monday, December 08, 2008 1:37 PM