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

Question

我一直在搜索并尝试几个小时来获取一个查询，该查询为我提供了来自 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>

Answer 1

这个脚本应该让你有一个好的开始。此查询将为每个报告订阅的每个参数返回一行，或者为不使用参数的订阅返回一行。如果您更喜欢转换它，您可能需要重新编写脚本以将其转换为 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。 .