Listing Subscribed SSRS Reports including Parameters & their Values
One of my friends Company needs analysis on subscribed reports & their parameters & values using ReportServer data of Reporting Services 2005/2008.
Subscriptions (table) let you create subscriptions to the report. Subscriptions allow you to set up periodic delivery of reports to end users by e-mail or file share.
Subscriptions table has [Parameters] column type of [ntext] and actually (as per best of my knowledge), it stores XML formatted data.
There may be multiple Subscribed Reports, having multiple parameters, and each parameter may have single/multiple values.
So there was need to retrieve each Subscribed Report, with detail information including report Name, Owner, LastRunTime, Parameters & their value(s) are subscribed etc.?
This is easy enough to do with cursors & temp variable/table approach but I was looking solution in simple way, and found it easier using CROSS APPLY, STUFF with FOR XML PATH, and simple CTE, please see script:
WITH
[Sub_Parameters] AS
(
SELECT
[SubscriptionID],
[Parameters] = CONVERT(XML,a.[Parameters])
FROM [Subscriptions] a
),
[MySubscriptions] AS
(
SELECT DISTINCT
[SubscriptionID],
[ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')),
[ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)')
FROM
[Sub_Parameters] a
CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p)
),
[SubscriptionsAnalysis] AS
(
SELECT
a.[SubscriptionID],
a.[ParameterName],
[ParameterValue] =
(SELECT
STUFF((
SELECT [ParameterValue] + ', ' as [text()]
FROM [MySubscriptions]
WHERE
[SubscriptionID] = a.[SubscriptionID]
AND [ParameterName] = a.[ParameterName]
FOR XML PATH('')
),1, 0, '')
+'')
FROM [MySubscriptions] a
GROUP BY a.[SubscriptionID],a.[ParameterName]
)
SELECT
a.[SubscriptionID],
c.[UserName] AS Owner,
b.Name,
b.Path,
a.[Locale],
a.[InactiveFlags],
d.[UserName] AS Modified_by,
a.[ModifiedDate],
a.[Description],
a.[LastStatus],
a.[EventType],
a.[LastRunTime],
a.[DeliveryExtension],
a.[Version],
e.[ParameterName],
LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue],
SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName
FROM
[Subscriptions] a
INNER JOIN [Catalog] AS b
ON a.[Report_OID] = b.[ItemID]
LEFT OUTER JOIN [Users] AS c
ON a.[OwnerID] = c.[UserID]
LEFT OUTER JOIN [Users] AS d
ON a.MODIFIEDBYID = d.Userid
LEFT OUTER JOIN [SubscriptionsAnalysis] AS e
ON a.SubscriptionID = e.SubscriptionID; 
