christian_t - Tuesday, April 4, 2017 1:52 AM

Hi Vinoth,

what I did is, to create a dedicated schedule that ended in the past e.g. 01.01.2017. For every subscription that I wanted to trigger when the data is ready I used that schedule.
Once the ETL that delivered the data for the report is ready I execute the procedure to fire the subscription.

Procedure in ReportServer-DB

USE [ReportServer]
GO

CREATE PROCEDURE [dbo].[usp_report_event_trigger]
    @report_path nvarchar(425) = N'',
    @report_action int = -1
    /*    ReportAction    EventType
        3                CacheInvalidateSchedule
        4                TimedSubscription
        5                RefreshCache
    */
AS
BEGIN
    SET NOCOUNT ON;
    /* Schedule for Dummy_Data_Driven_Abo */
    DECLARE @schedule_id uniqueidentifier = '308D512B-1E97-4EF6-BC9C-493AF0520267';
    DECLARE @exec_command nvarchar(MAX) = N'';
   
    SELECT
        @exec_command =
            @exec_command + N'EXECUTE ReportServer.dbo.AddEvent @EventType=''' +
            CASE
                WHEN rs.ReportAction = 4 THEN 'TimedSubscription'
                ELSE sc.EventType
            END +
             ''', @EventData='''+CONVERT(nvarchar(36), UPPER(rs.SubscriptionID))+N'''; '
    FROM dbo.Catalog AS c
    INNER JOIN dbo.ReportSchedule    AS rs ON (c.ItemID = rs.ReportID AND rs.SubscriptionID IS NOT NULL)
    INNER JOIN dbo.Schedule            AS sc ON (rs.ScheduleID = sc.ScheduleID)
    WHERE c.Path = @report_path
    AND (rs.ReportAction = @report_action OR @report_action = -1)
    /* if chachinvalidateSchedule or RefreshCache OR TimedSubscription with my dedicated Schedule */
    AND (@report_action != 4 OR rs.ScheduleID = @schedule_id)
   
    EXECUTE sp_executesql  @exec_command;

END


Fire the subscription

EXECUTE ReportServer.dbo.usp_report_event_trigger
    @report_path = N'/Report_Folder/Reportname',
    @report_action = 4;