2019-05-29 | Send SSRS subscriptions on the first Monday of each fiscal period
Sometimes users have a very specific idea for when they want their report subscriptions to be sent out. For example, I had a request for a subscription to be sent out on the first Monday of each fiscal period. Fiscal periods are essentially month-long date ranges that don't necessarily line up with the actual beginning and end of a month, and they are used for various accounting mumbo jumbo.
Standard SSRS subscriptions give us a good amount of scheduling options. For example, I could make a subscription that goes out on the first Monday of each month fairly easily.
However, that doesn't quite fulfill the request, because sometimes the beginning of a fiscal period will actually be on the Monday of the previous month, or the second Monday of the current month. Instead, I decided to pull out the big guns and create a data-driven subscription solution.
Data-driven subscriptions allow you to write a SQL query that runs with each execution, and you can use the results as a dataset for your report. For example, the dataset could provide a list of values that you want to be passed as a parameter for the report to run, or it could populate the 'Subject' field of the subscription email.
Here's the solution I went with:- The data-driven subscription runs every single day.
- The query returns a list of email addresses that we use as the 'To' field of the email.
- If the query is run on the first day of a fiscal period, it returns the regular list of email addresses.
- If the query is run on a day which is NOT the first day of a fiscal period, it returns NULL, so no one is emailed.
This way, the data-driven subscription runs each day, but the query will only provide it with a list of email addresses if it's the correct day to send the email. Our logic is limited only by what we can write in SQL.
Here's a sample of the actual code used to determine whether the query is being run on the first Monday of a fiscal period. The comments will go into detail about what's being done, but the important part is the WHERE clause at the end, which prevents the query from returning any email addresses unless the date matches the date we're looking for.
DECLARE @ExecutingDate date = CAST(GETDATE() AS date) -- The date which we are checking to see if we should send emails for. Usually today's date, can be manually populated to test easily.
DECLARE @SendDayOfWeek int = 2 -- Monday. The day of the week on which emails should be sent. Index starts at 1/Sunday.
DECLARE @FirstSendDateInFiscalPeriod date; -- The first date in the fiscal period which falls on a @SendDayOfWeek (Monday). Could be the same day that the fiscal period starts on.
-- Populate @FirstSendDateInFiscalPeriod.
SELECT
@FirstSendDateInFiscalPeriod = CASE
-- If the fiscal period starts on the @SendDayOfWeek (Monday), use that value as our @FirstSendDateInFiscalPeriod.
WHEN DATEPART(dw, tblFiscalPeriod.StartsOn) = @SendDayOfWeek THEN CAST(tblFiscalPeriod.StartsOn AS date)
-- Otherwise, do some witchcraft to determine the first @SendDayOfWeek (Monday) that comes after the start of the fiscal period.
ELSE DATEADD(DAY, (DATEDIFF(DAY, ((@SendDayOfWeek + 5) % 7), tblFiscalPeriod.StartsOn) / 7) * 7 + 7, ((@SendDayOfWeek + 5) % 7))
END
FROM
tblFiscalPeriod
WHERE
FiscalPeriodID = dbo.GetFiscalPeriod(@ExecutingDate)
SELECT
VALS.*
FROM
(
SELECT
[To] = 'timmy@site.com'
UNION SELECT
[To] = 'johnny@site.com'
UNION SELECT
[To] = 'spike@site.com'
) VALS
WHERE
-- Only return results if we're executing this on the first send date for the fiscal period, which we calculated earlier.
-- Otherwise, no results are returned, so no emails are sent.
@ExecutingDate = @FirstSendDateInFiscalPeriod