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

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!

Answer 1

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

Answer 2

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