Reporting services -订阅文件夹中的所有报表

Question

我在一个文件夹中有许多报告，它会随着时间的推移而增长。若要订阅所有报表，用户必须转到每个报表并创建订阅。大约有10个报告，这是可以管理的，但当有30个报告，并且需要向订阅添加新用户时，这就变得很困难。

如何创建某种批量订阅？我的意思是-轻松订阅文件夹中的所有报表，并将其发送给用户(无论在哪里-电子邮件或文件共享)。是否有一些管理选项，或者我应该编写一些脚本来实现它？

环境: W2K8 R2 (企业版)上的SQL Server2008 R2 +SSRS2008 R2 (标准版)

Answer 1

我在使用rs.exe的this link中找到了解决方案。从本质上讲，它是示例from BOL。我已经根据我的需要对其进行了更改，并且工作正常。用法：

rs.exe -i CreateSubscriptionTest.rss -s http://myreportserveraddresshere/reportserver

' CreateSubscriptionTest.rss
Public Sub Main()
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    Dim desc As String        = "Report description"
    Dim eventType As String   = "TimedSubscription"
    Dim scheduleXml As String = "<ScheduleDefinition><StartDateTime>2012-03-22T09:30:00</StartDateTime><WeeklyRecurrence><WeeksInterval>1</WeeksInterval><DaysOfWeek><Thursday>True</Thursday></DaysOfWeek></WeeklyRecurrence></ScheduleDefinition>"

    Dim extensionParams(7) As ParameterValue

    extensionParams(0)       = New ParameterValue()
    extensionParams(0).Name  = "TO"
    extensionParams(0).Value = "my.address@email.com"

    extensionParams(1)       = New ParameterValue()
    extensionParams(1).Name  = "ReplyTo"
    extensionParams(1).Value = "admin@email.com"

    extensionParams(2)       = New ParameterValue()
    extensionParams(2).Name  = "IncludeReport"
    extensionParams(2).Value = "True"

    extensionParams(3)       = New ParameterValue()
    extensionParams(3).Name  = "RenderFormat"
    extensionParams(3).Value = "MHTML"

    extensionParams(4)       = New ParameterValue()
    extensionParams(4).Name  = "Subject"
    extensionParams(4).Value = "@ReportName was executed at @ExecutionTime"

    extensionParams(5)       = New ParameterValue()
    extensionParams(5).Name  = "Comment"
    extensionParams(5).Value = "Some HTML code inside email's body<br><br>Go!"

    extensionParams(6)       = New ParameterValue()
    extensionParams(6).Name  = "IncludeLink"
    extensionParams(6).Value = "True"

    extensionParams(7)       = New ParameterValue()
    extensionParams(7).Name  = "Priority"
    extensionParams(7).Value = "NORMAL"

    Dim parameters() As ParameterValue

    ' If you need setup parameters

    'Dim parameter As New ParameterValue()
    'parameter.Name = "EmpID"
    'parameter.Value = "288"
    'parameters(0) = parameter

    'parameter.Name = "ReportMonth"
    'parameter.Value = "12"
    'parameters(1) = parameter

    'parameter.Name = "ReportYear"
    'parameter.Value = "2003"
    'parameters(2) = parameter

    Dim matchData As String = scheduleXml

    Dim extSettings As New ExtensionSettings()
    extSettings.ParameterValues = extensionParams
    extSettings.Extension       = "Report Server Email"

    Dim returnValue As String

    Dim reports() As String = { _ 
        "/MyReports/Executive/SalesYear", _ 
        "/MyReports/Executive/SalesMonth", _ 
        "/MyReports/Executive/SalesWeek"}

    For Each report As String In reports
        returnValue = rs.CreateSubscription(report, extSettings, desc, eventType, matchData, parameters)
        Console.WriteLine(returnValue)
    Next

End Sub 'Main

关键部分是定义带有报表名称的reports()变量。名称是完整的报表路径。

上面的示例为每个订阅创建新的计划。它在SQL Server代理中变得非常混乱，有很多报告计划，所以我将其更改为使用shared schedule。不同之处在于scheduleXml声明-您使用调度ID而不是XML字符串：

Dim scheduleXml As String = "924b9bb6-2340-4f5c-a897-465af7ff310e"

如何获取调度ID：

-- using T-SQL
SELECT
    ScheduleID,
    Name
FROM ReportServer.dbo.Schedule
WHERE
    EventType = 'SharedSchedule'

' using rss file: GetSchedulers.rss
' run with rs.exe -i GetSchedulers.rss -s http://myreportserveraddresshere/reportserver

Public Sub Main()
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    Dim returnValue As Schedule()

    returnValue = rs.ListSchedules()

    For Each sch As Schedule In returnValue
        Console.WriteLine(sch.ScheduleID & " - " & sch.name)
    Next

End Sub 'Main

Answer 2

通过电子邮件向所有报表用户发送订阅的最简单方法是在电子邮件组上设置订阅，然后根据需要在该电子邮件组中添加(和删除)用户。

按文件共享向所有报表用户发送订阅的最简单方法是在文件共享上设置订阅，然后根据需要授予(和删除)对该文件共享的访问权限。

我不知道有什么方法可以设置批处理订阅的等价物。但是，基于作为参数传递给查询的用户ID，应该可以编写存储过程来更新现有计划或在ReportServer数据库中创建新计划。

您可以查看访问ReportServer数据库here上的订阅表的查询示例。