Introduction

In SQL Server Reporting Service (SSRS), while scheduling a report subscription, we can use either shared schedules or report-specific schedules. Generally, the schedule uses the following regular definition:

• Hour
• Day
• Week
• Month

However, sometimes, we would like a subscription to be delivered on an irregular schedule. For example, we need to schedule a report to run on "last Monday in a full week before the new month". The generate time for year 2014 should be like below.
1/20/2014
2/17/2014
3/24/2014
4/21/2014
5/26/2014
6/23/2014
7/21/2014
8/25/2014
9/22/2014
10/20/2014
11/24/2014
12/22/2013

Solution

By design, when a subscription is created, a corresponding SQL Server agent job is created meanwhile. The SQL Server agent job has the same schedule with the shared schedule or report-specific schedule that is using by the subscription.

The corresponding SQL Server agent job calls stored procedure AddEvent to add an event in SSRS. Then the SSRS notification service fetches the event from SSRS database to deliver the subscription.

That, we can configure regular shared schedule or report-specific schedule based on the irregular schedule. The following table shows some samples:
    Expected Schedule                                                                      SSRS Schedule
    last Monday in a full week before the new month                      Weekly, Monday
    9AM every Monday/Tuesday, and 10AM every Wednesday        Hourly
    On a certain day based on business needed                              Daily

In this article, I will demonstrate how to configure an irregular schedule based on the requirement last Monday in a full week before the new month.

1. Create a function in SQL Server database. The function is used for checking whether the day is at the last full week of the Month.
   Use TestDB
   CREATE FUNCTION CustomSchedule()
   RETURNS INT
   AS
   BEGIN
       DECLARE @ret INT;
    SELECT @ret = CASE WHEN (DATEDIFF(DAY, GETDATE (), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) > 6) AND (DATEDIFF(DAY, GETDATE (), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) < 13 )  THEN 1 ELSE 0 END
       RETURN @ret;
   END;
   GO
2. Create a subscription to execute every Monday.
3. Open SQL Server Management Studio (SSMS) to connect to Database Engine ->expand SQL Server Agent->expand Jobs, double-click the Job.
4. Select Steps on the left pane on the Job Properties window.
5. Click Edit button and then change the command
   From
   EXEC [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='9be28f07-3784-4070-802a-b7ca0aec4c7c'
   To
   DECLARE @a INT
   SET @a=(SELECT TestDB.dbo.CC())
   IF (@a=1)
   BEGIN
   EXEC [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='9be28f07-3784-4070-802a-b7ca0aec4c7c'
   END
   Or
   IF((SELECT CASE WHEN (DATEDIFF(DAY, getdate(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) > 6) AND (DATEDIFF(DAY, getdate(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) < 13 ) THEN 1 ELSE 0 END)=1)
   BEGIN
   EXEC [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='9be28f07-3784-4070-802a-b7ca0aec4c7c'
   END
   Note: Change the @EventType and @EventData to the values in your job

Applies to

Microsoft SQL Server 2005
Microsoft SQL Server 2008
Microsoft SQL Server 2008 R2
Microsoft SQL Server 2012

• Edited by ForumFAQ 4 hours 45 minutes ago
• Edited by Elvis LongMicrosoft contingent staff, Moderator 3 hours 0 minutes ago edit