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;

Rate

5 (7)

Share

Share

Rate

5 (7)