Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Report launcher to run SSRS report subscriptions on demand


By:   |   Last Updated: 2013-10-24   |   Comments (4)   |   Related Tips: > Reporting Services Administration

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.

SQL Server Agent Jobs

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

Scheduled Report Subscriptions Launcher

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]


LaunchSubscription parameter mapping

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.

Scheduled Report Subscriptions Launcher

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.

Scheduled Report Subscriptions Launcher

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.

Scheduled Report Subscriptions Launcher
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.


Last Updated: 2013-10-24


next webcast button


next tip button



About the author
MSSQLTips author Pavel Pawlowski Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 05, 2018 - 9:36:57 AM - Joseph Kunk Back To Top

How do you pass one or more parameters to the report? 


Friday, May 09, 2014 - 8:13:07 PM - Matt Back To Top

Yeah, that's what I did.  And now the reports each have just one parameter that suffices for the LaunchedSubscriptionDetails dataset or LaunchedScheduleDetails dataset, respectively, as well as the launch dataset, but that one last.  So the sproc only executes after the query runs, thus after the user clicks "view report".  


Wednesday, May 07, 2014 - 8:57:45 PM - Matt Back To Top

This is great and very useful however, I may have gotten some parameter assigning wrong but what I find is that upon selecting the first prompt, the stored procedure fires and the chosen subscription or schedule launches Without the user having pushed the "View Report" button.  In fact the report need never run; just selecting the prompt executes the AddEvent proc and launches the subscription or schedule.  

I'm going to try tomorrow rearranging the datasets (I hate that the datasets execute in order top-to-bottom and you cannot easily rearrange them, as you can with the prompts -- and that this is not prominently documented) so that the launch dataset is last and gets its parameter from the executed 'launched' query, instead of from the first prompt.  This way, I hope, the report must run for the sproc to excute and launch the subscription/schedule.  

If I've messed something up and am totally off base please let me know.

 

 


Thursday, October 24, 2013 - 8:26:43 AM - Scott Back To Top

Very Neat idea!


Learn more about SQL Server tools