Reporting services - subscribe to all reports in folder

Question

I have a number of reports in a folder, and it will grow in a time. To subscribe to all reports user has to go to each one and create subscription. With about 10 reports it's manageable, but when there are 30 reports and a new user should be added to a subscription it's becoming difficult.

How can I create some kind of batch subscription? I mean - to make easy subscription to all reports in a folder and send it to the user(s) (no matter where - e-mail or file share). Is there some administrative option or should I write some script to achive it?

Environment: SQL Server 2008 R2 + SSRS 2008 R2 (Standard Edition) on W2K8 R2 (Enterprise Edition)

Answer 1

I found the solution following this link where rs.exe is used. Esentially it's example from BOL. I've changed it to suit my needs and it works. Usage:

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

The key part is to define reports() variable with report names. Name is the full report path.

The above example creates new schedule for each subscription. It's getting very messy in SQL Server Agent with a lot of reports schedules, so I changed it to use shared schedule. The difference is in scheduleXml declaration - you use schedule ID instead of XML string:

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

How can you obtain the schedule 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

The simplest way to send a subscription to all reports users by e-mail is to set up the subscription on an e-mail group, and add (and remove) users from that e-mail group as desired.

The simplest way to send a subscription to all reports users by file share is to set up the subscription on a file share, and grant (and remove) access to that file share as desired.

I don't know of any way to set up the equivalent of a batch subscription. However, it should be possible to write a stored procedure to update existing schedules or create new schedules in the ReportServer database, based on a user ID passed to the query as a parameter.

You can see examples of queries that access the subscription tables on the ReportServer database here.