Query to get list of all SSRS Subscriptions with parameters

Question

I've be searching and trying for hours to get a query that gives me all current subscriptions from an SSRS serv with parameters and their values, so that they can be recreated on a new server (after pruning).

For example a simple report might be HoursByDepartment that takes three params:

@From =Today - 7 days (Default)
@To   = Today (Default)
@Dept = 2 (part of subscription)

What I want to get is something along these lines (or something that will let me create a report)

Report            ParamName   ParamValue    Default
HoursByDepartment From        Today-7days    True
HoursByDepartment To          Today          True
HoursByDepartment Dept        2              False

OR

Report             Param1Name   Param1Value   Param1Def   Param2Name   Param2Value    Param2Def     
HoursByDepartment  From         Today-7days   True        To           Today          True

I'm pretty good with XSl, so if i could get something like, I could work with it:

<subid>
    <report>
        <ParameterValues>
            <ParameterValue>
                <Name>MinAvailable</Name>
                <Value>10000</Value>
            </ParameterValue>
            <ParameterValue>
                <Name>OwnerIDs</Name>
                <Value>0</Value>
            </ParameterValue>
            <ParameterValue>
                <Name>ShowCosts</Name>
                <Value>False</Value>
            </ParameterValue>
            <ParameterValue>
                <Name>MinValue</Name>
                <Value>0</Value>
            </ParameterValue>
        </ParameterValues>
    </report>
</subid>

Answer 1

This script should get you off to a good start. This query will return one row for each parameter for each report subscription, or just one row for subscriptions that do not utilize parameters. You may have to re-work the script to get it in xml format if your preference is to transform it instead.

This is derived from Listing Subscribed SSRS Reports including Parameters & their Values (Not sure how much of the original I have changed, if anything.)

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;

However, if this is an upgrade from 2005 to 2008, you may want to consider using this tool. If you are removing SSRS from this server and moving to a different server using the same version, you may be better off moving the entire reportserver and reportservertempdb databases as explained by Microsoft here.

Answer 2

Below is a version of @dev_etter's query updated to return extended settings as well.

WITH
[Sub_Parameters] AS
    (SELECT 
        [SubscriptionID],
        [Parameters] = CONVERT(XML,a.[Parameters]),
        [ExtensionSettings] = CONVERT(XML,a.[ExtensionSettings])
     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)
    UNION
    SELECT --DISTINCT 
        [SubscriptionID],
        [ExtensionSettingName] = QUOTENAME(e.value('(Name)[1]', 'nvarchar(max)')),
        [ExtensionSettingValue] = e.value('(Value)[1]', 'nvarchar(max)')
    FROM [Sub_Parameters] a
    CROSS APPLY [ExtensionSettings].nodes('/ParameterValues/ParameterValue') t(e)
    )

, [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]
;

Answer 3

This is a great help to evaluate use of a specific parameter among the subscriptions of a given report, especially to see which subscriptions need to be updated when a parameter becomes mandatory. I took @Chumz' query because it is more legible than @dev_etter's and modified it to also show parameters for data-driven subscriptions where there's no <Value> but a <Field> in the Parameters XML:

WITH
[Sub_Parameters] AS
    (SELECT 
        [SubscriptionID],
        [Parameters] = CONVERT(XML,[Parameters]),
        [ExtensionSettings] = CONVERT(XML,[ExtensionSettings])
     FROM [Subscriptions]
    )

, [MySubscriptions] AS (
    SELECT --DISTINCT 
        [SubscriptionID],
        [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')),
        [ParameterValue] = ISNULL(p.value('(Value)[1]', 'nvarchar(max)'),p.value('(Field)[1]', 'nvarchar(max)')),
        [ParameterType] = CASE WHEN p.value('(Field)[1]', 'nvarchar(max)') IS NOT NULL THEN 'Query' ELSE 'Static' END
    FROM [Sub_Parameters] sp
    CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p)
    UNION
    SELECT --DISTINCT 
        [SubscriptionID],
        [ExtensionSettingName] = QUOTENAME(e.value('(Name)[1]', 'nvarchar(max)')),
        [ExtensionSettingValue] = ISNULL(e.value('(Value)[1]', 'nvarchar(max)'),e.value('(Field)[1]', 'nvarchar(max)')),
        [ExtensionSettingType] = CASE WHEN e.value('(Field)[1]', 'nvarchar(max)') IS NOT NULL THEN 'Query' ELSE 'Static' END
    FROM [Sub_Parameters] sp
    CROSS APPLY [ExtensionSettings].nodes('/ParameterValues/ParameterValue') t(e)
    )

, [SubscriptionsAnalysis] AS (
    SELECT
        ms.[SubscriptionID],
        ms.[ParameterName],
        ms.[ParameterType],
        [ParameterValue] =
              (SELECT STUFF((SELECT [ParameterValue] + ', ' as [text()] 
               FROM [MySubscriptions] 
               WHERE [SubscriptionID] = ms.[SubscriptionID] 
                   AND [ParameterName] = ms.[ParameterName] FOR XML PATH('')),1, 0, '')+'')
    FROM [MySubscriptions] ms
    GROUP BY ms.[SubscriptionID],ms.[ParameterName],ms.[ParameterType]
    )

SELECT
    s.[SubscriptionID],
    o.[UserName] AS [Owner], 
    c.[Name],
    c.[Path],
    s.[Locale], 
    s.[InactiveFlags], 
    m.[UserName] AS [Modified_by], 
    s.[ModifiedDate], 
    s.[Description], 
    s.[LastStatus], 
    CASE WHEN DATALENGTH(s.[DataSettings]) IS NULL THEN 'False' ELSE 'True' END AS [IsDataDriven],
    s.[EventType], 
    s.[LastRunTime], 
    CASE s.[DeliveryExtension] WHEN 'Report Server Email' THEN 'Email' WHEN 'Report Server FileShare' THEN 'File Drop' ELSE s.[DeliveryExtension] END AS SubscriptionType,
    sa.[ParameterName],
    sa.[ParameterType],
    LEFT(sa.[ParameterValue],LEN(sa.[ParameterValue])-1) as [ParameterValue]
FROM [Subscriptions] s
INNER JOIN [Catalog] AS c ON s.[Report_OID] = c.[ItemID]
LEFT OUTER JOIN [Users] AS o ON s.[OwnerID] = o.[UserID]
LEFT OUTER JOIN [Users] AS m ON s.[ModifiedByID] = m.[Userid]
LEFT OUTER JOIN [SubscriptionsAnalysis] AS sa ON s.[SubscriptionID] = sa.[SubscriptionID];