Problem
SSRS report subscriptions are a great feature for automatic report delivery on regular schedule. Unfortunately, there is no easy way to run a subscribed report on demand. If you need to run one of these reports on demand, you will need to dig into the Report Server database to find the right subscription. When it is needed to be run on demand more frequently, it becomes annoying and you lose valuable time.
Solution
This tip will show how this can be handled more elegantly by two new SSRS reports. One report will allow us to run individual subscriptions of reports and the second will allow to run all subscriptions handled by a schedule.
Background
The automatic execution of report subscriptions is handled by SQL Server Agent. SSRS creates a separate agent job for each shared schedule and each individual schedule. The jobs are named by the GUIDs of that particular schedules as shown below which are not very useful.
If you take a closer look at the job created by SSRS you will notice that in fact the job does not executes the report itself, but instead it calls the [dbo].[AddEvent] stored procedure from the report server database to add an event into the [dbo].[Event] table. Based on the subscription schedule there are 2 types of events - TimedSubscription or SharedSchedule.
exec [ReportServer].dbo.AddEvent
@EventType='TimedSubscription',
@EventData='be13bfa4-9c36-423f-90b8-bd449bfd7eb3'
exec [ReportServer].dbo.AddEvent
@EventType='SharedSchedule',
@EventData='3045b1a3-ce3b-430e-a5dd-99953d389d80'
The @EventType parameter of the [dbo].[AddEvent] stored procedure specifies a type of Schedule and the @EventData represents the GUID for the particular shared schedule or GUID of an individual subscription.
SSRS regularly checks the events in the reports server database and if it finds one of the above mentioned events, it automatically starts processing the subscriptions. In the case of a TimedSubscription the single subscription is processed and in the case of a SharedSchedule all subscription sharing that schedule are processed one by one.
Creating SSRS Reports to Launch Reports
We will design the reports so each of the reports will have three datasets. The first dataset will get information about the Subscriptions or Schedules. The second dataset will handle execution of the subscription or schedule and the third dataset will provide information about the launched subscription or schedule and that information will be shown on the report itself.
Our reports will use four different tables from the Report Server database.
- [dbo].[Catalog] - catalog table which contains information about all reports and folders in the SSRS Instance
- [dbo].[Subscriptions] - table containing information about all subscriptions
- [dbo].[Schedule] - containing information about all schedules
- [dbo].[ReportSchedule] - contains links among Schedules, Subscriptions and Reports
SSRS report to launch individual subscription reports
The first report to develop will handle launching individual subscriptions
The report contains two parameters SubscriptionToLaunch and LaunchedSubscritption. Available values of the SubscriptionToLaunch is populated by the ReportSubscriptions Dataset and is used to select a particular subscription for execution. The LaunchedSubscription has a default value populated by the LaunchSubscription Dataset, which handles the execution itself and is subsequently used by the LaunchedSubscriptionDetails Dataset, which returns details for the launched subscription and information from this data set are shown on the report itself.
ReportSubscriptions Dataset
As mentioned above, the ReportSubscriptions dataset is used to populate the SubscriptionsToLaunch parameter and the purpose of this parameter is to select a particular subscription for execution. This queries the [dbo].[Subscriptions] and [dbo].[Catalog] tables. From the subscription we will receive the [SubscriptionID] and subscription [Description] and from the [dbo].[Catalog] we retrieve the subscribed report [Name].
SELECT
S.[SubscriptionID]
,C.[Name] + ' (' + S.[Description] + ')' AS [DisplayName]
FROM [dbo].[Subscriptions] S
INNER JOIN [dbo].[Catalog] C ON S.[Report_OID] = C.[ItemID]
ORDER BY [DisplayName]
LaunchSubscription Dataset
This dataset handles the execution of the subscription by calling the [dbo].[AddEvent] stored procedure in ReportServer database. Because the [dbo].[AddEvent] stored procedure does not return any record set, we have to return some information so the Dataset can be used by reporting services. Because of this, the query contains a SELECT statement, which returns the SubscriptionID. The @EventData variable is mapped to the SubscriptionToLaunch parameter.
exec [dbo].[AddEvent] 'TimedSubscription', @EventData; SELECT @EventData AS [SubscriptionID]
LaunchedSubscriptionDetails DataSet
The third dataset provides information about launched subscriptions. Data from this dataset are used on the report to inform users about subscription and report details.
SELECT
S.[Description] AS [SubscriptionDescription]
,C.[Name] AS [ReportName]
,S.[DeliveryExtension]
FROM [dbo].[Subscriptions] S
INNER JOIN [dbo].[Catalog] C ON S.[Report_OID] = C.[ItemID]
WHERE
S.[SubscriptionID] = @LaunchedSubscription
Once you compose everything together, you can use the report for launching subscriptions. The final result will look like below.
SSRS report to launch shared scheduled reports
The second report mentioned is used to execute shared schedules. When a shared schedule is launched, all the subscriptions using that shared schedule will be launched one by one. The structure of the report is the same as the previous report with only slight differences in the queries.
ReportSchedules Dataset
In contrast to the ReportSubscriptions query for the previous report, we will query only the names of the shared schedules. We get this information from the [dbo].[Schedules] table. As this table contains records for both SharedSchedules and also for TimedSubscriptions, we limit the results only to get the shared ones. Also we get only schedules for which a record in [dbo].[ReportSchedule] exists as we do not want to execute shared schedules without a report subscription.
SELECT
[ScheduleID],
[Name]
FROM [dbo].[Schedule] S
WHERE
[EventType] = 'SharedSchedule'
AND
[ScheduleID] IN (SELECT [ScheduleID] FROM [dbo].[ReportSchedule])
ORDER BY [Name]
LaunchSchedule Dataset
The LaunchSchedule is similar to the LaunchSubscription query. The only difference is that instead of passing TimedSubscription as @EventType we will pass SharedShedule.
exec [dbo].[AddEvent] 'SharedSchedule', @EventData; SELECT @EventData AS [ScheduleID]
LaunchedScheduleDetails Dataset
Again similar to the LaunchedSubscriptionDetails dataset from the previous report, this will return details about the executed schedule. In contrast to the LaunchedSubscriptionDetails, this query can return multiple rows as one shared schedule can handle multiple report subscriptions.
SELECT
S.[Description] AS [SubscriptionDescription]
,C.[Name] AS [ReportName]
,S.[DeliveryExtension]
FROM [dbo].[ReportSchedule] RS
INNER JOIN [dbo].[Subscriptions] S ON RS.[SubscriptionID] = S.[SubscriptionID]
INNER JOIN [dbo].[Catalog] C ON RS.[ReportID] = C.[ItemID]
WHERE
RS.[ScheduleID] = @LaunchedSchedule
Once the report is composed, we can use it to launch subscriptions handled by shared schedules. The result will look like the image below.
Next Steps
- Reports presented in this tip provide only basic information. You can elaborate on the reporting database and update the reports to provide more information about the subscriptions and reports.
- Reports as they are presented here are suitable for administrators. You can create a set of stored procedures in the reporting database to get the subscriptions and schedules and also create procedures for launching subscriptions and returning necessary information. These procedures can process information about the currently logged in user and limit the execution to subscriptions and schedules created by the user who is running the report. Then you can provide such reports to end users to allow them on demand execution of subscriptions.
- You can create additional overview reports for the Schedules and Subscription which will allow you to group and sort the subscriptions. Then upon a click you can execute these reports providing the right parameters to launch a particular subscription.
- You can download the complete SSRS project with sample reports here.