locked
Creating a report subscription to run on the 5th BUSINESS day. RRS feed

  • Question

  • I have a report that needs to send out on the 5th Business day, right now i have it running on the 5th of the month but it needs to change so that it does not send out on the weekends.  How do i schedule it to send out on the 5th business day?

    Thanks!

    Tuesday, January 27, 2015 3:08 PM

Answers

  • We have created subscription with One-time Schedule and then created another agent job to execute on 1st working day.

    Step 2: execute below to get code that will actually be executed by SQL agent for subscription.

    SELECT 
        'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' 
    + CAST(RS.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
               
        FROM ReportServer.dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs SJ
                ON Convert(varchar(500),RS.ScheduleID) = SJ.name
                JOIN ReportServer.dbo.ReportSchedule RSC
                ON SJ.name = Convert(varchar(500),RSC.ScheduleID)
                JOIN ReportServer.dbo.Subscriptions SU
                ON RSC.SubscriptionID = SU.SubscriptionID
                JOIN ReportServer.dbo.Catalog CA
                ON SU.report_oid = CA.itemid
    			Join ReportServer.dbo.Users U
    			on U.UserID = CA.CreatedByID
    	where CA.Name like 'Test' --Add your report name here

    Step 3 : Create another SQL job  to execute daily and add below to job step

    DECLARE	@Today DATE = GETDATE(), @Workingday Date
    
    SELECT	@Workingday = CASE DATEDIFF(DAY, '19000101', theDate) % 7
    		WHEN 0 THEN DATEADD(DAY, 4, theDate)
    		WHEN 6 THEN DATEADD(DAY, 5, theDate)
    		ELSE DATEADD(DAY, 6, theDate)
    	END
    FROM	(
    		SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @Today), '19000101')
    	) AS d(theDate)
    
    
    IF @Today = @Workingday
    BEGIN
    
    	EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='A30C87A9-4AF1-43FA-978A-2AFD05215' --Add output from previous query
    
    END;
    Make sure you test above solution as you might have to tweak a bit to work in your environment.


    Regards,
    Vishal Patel
    Blog: http://vspatel.co.uk
    Site: http://lehrity.com


    • Edited by VSPatel Tuesday, January 27, 2015 3:59 PM
    • Proposed as answer by Vicky_Liu Wednesday, January 28, 2015 3:16 AM
    • Marked as answer by Vicky_Liu Monday, February 2, 2015 1:23 AM
    Tuesday, January 27, 2015 3:58 PM
  • You can tweak the job properties to add a prefix step to do check and execute the actual report delivery step based on your custom schedule

    see

    http://visakhm.blogspot.in/2014/05/sql-agent-tips-configure-custom.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Vicky_Liu Wednesday, January 28, 2015 3:17 AM
    • Marked as answer by Vicky_Liu Monday, February 2, 2015 1:23 AM
    Tuesday, January 27, 2015 4:28 PM

All replies

  • We have created subscription with One-time Schedule and then created another agent job to execute on 1st working day.

    Step 2: execute below to get code that will actually be executed by SQL agent for subscription.

    SELECT 
        'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' 
    + CAST(RS.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
               
        FROM ReportServer.dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs SJ
                ON Convert(varchar(500),RS.ScheduleID) = SJ.name
                JOIN ReportServer.dbo.ReportSchedule RSC
                ON SJ.name = Convert(varchar(500),RSC.ScheduleID)
                JOIN ReportServer.dbo.Subscriptions SU
                ON RSC.SubscriptionID = SU.SubscriptionID
                JOIN ReportServer.dbo.Catalog CA
                ON SU.report_oid = CA.itemid
    			Join ReportServer.dbo.Users U
    			on U.UserID = CA.CreatedByID
    	where CA.Name like 'Test' --Add your report name here

    Step 3 : Create another SQL job  to execute daily and add below to job step

    DECLARE	@Today DATE = GETDATE(), @Workingday Date
    
    SELECT	@Workingday = CASE DATEDIFF(DAY, '19000101', theDate) % 7
    		WHEN 0 THEN DATEADD(DAY, 4, theDate)
    		WHEN 6 THEN DATEADD(DAY, 5, theDate)
    		ELSE DATEADD(DAY, 6, theDate)
    	END
    FROM	(
    		SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @Today), '19000101')
    	) AS d(theDate)
    
    
    IF @Today = @Workingday
    BEGIN
    
    	EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='A30C87A9-4AF1-43FA-978A-2AFD05215' --Add output from previous query
    
    END;
    Make sure you test above solution as you might have to tweak a bit to work in your environment.


    Regards,
    Vishal Patel
    Blog: http://vspatel.co.uk
    Site: http://lehrity.com


    • Edited by VSPatel Tuesday, January 27, 2015 3:59 PM
    • Proposed as answer by Vicky_Liu Wednesday, January 28, 2015 3:16 AM
    • Marked as answer by Vicky_Liu Monday, February 2, 2015 1:23 AM
    Tuesday, January 27, 2015 3:58 PM
  • You can tweak the job properties to add a prefix step to do check and execute the actual report delivery step based on your custom schedule

    see

    http://visakhm.blogspot.in/2014/05/sql-agent-tips-configure-custom.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Vicky_Liu Wednesday, January 28, 2015 3:17 AM
    • Marked as answer by Vicky_Liu Monday, February 2, 2015 1:23 AM
    Tuesday, January 27, 2015 4:28 PM