tsql - 查询以获取带有参数的所有SSRS订阅的列表

我一直在搜索并尝试了几个小时，以获取一个查询，该查询可以为我提供来自SSRS服务的所有当前订阅以及其参数和值，以便可以在新服务器上重新创建它们(修剪后)。

例如，一个简单的报告可能是带有三个参数的HoursByDepartment:

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

我想要得到的是类似的东西(或者可以让我创建报告的东西)

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

或者

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

我对XSl相当满意，所以如果我能得到类似的东西，我可以使用它:

<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>

最佳答案

该脚本应该使您有个良好的开端。对于每个报表订阅，此查询将为每个参数返回一行，对于不使用参数的订阅，该查询将仅返回一行。如果您更喜欢转换脚本，则可能必须重新编写脚本以将其转换为xml格式。

这是从Listing Subscribed SSRS Reports including Parameters & their Values派生而来的(如果有的话，不确定我更改了多少原始文件。)

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;

但是，如果这是从2005年到2008年的升级，则可能需要考虑使用using this tool。如果要从该服务器上删除SSRS并使用相同版本移动到其他服务器，则最好将整个reportserver和reportservertempdb数据库移动为explained by Microsoft here。