SSRS Weekday EMail Subcription

One of the most useful features of SQL Server Reporting Services is EMail Subscription. Managers want their critical reports delivered to them via email for one reason: convenience. They want daily reports delivered to them first thing in the morning. Some want the daily frequency for some other reasons. What is common among these guys is that they don’t want to receive these daily reports on the weekend, which makes sense.

The Problem:

Here is the report requirement: to send a daily report via email (attached pdf) on weekdays only. All the data processed over the weekend (Friday, Saturday & Sunday) will be included in the Monday report as in the case of a 24/7 service application. Each report is a projection of the previous day’s processed data. So the Monday report will have the collected data from Friday to Sunday.

The Solution:

First, schedule the report to run on certain days, in this case Monday to Friday. I want to run the report at 5AM, before the bosses come in the morning.

Click to enlarge

The real trick about this solution is to set a default date on design time. In this particular situation the report has at least two date variables to capture the day’s processed data – a StartDate and an EndDate.

Since this is a daily  report that contain’s the previous day’s data, StartDate and EndDate will always be the previous date (“yesterday”). In this context the “whole day” is from 12AM to 11:59PM.

We can accomplish our goal by using date functions that are readily available with the SSRS development environment. Based on the logic of the requirement, we will have expressions like these on our date variables:

Click to enlarge

• StartDate

  =IIF(DatePart(DateInterval.Weekday, Today()) = 2, DateAdd(DateInterval.Day, -3, Today()), DateAdd(DateInterval.Day, -1, Today()))

• EndDate

  =IIF(DatePart(DateInterval.Weekday, Today()) = 2, DateAdd(DateInterval.Day, -1, Today()), DateAdd(DateInterval.Day, -1, Today()))

Basically, the logic we’re testing in the StartDate variable [ DateInterval.Weekday, Today())= 2] is check whether or not today is Monday. If it is, then let’s go back grab the data from Friday, Saturday, and Sunday (-3).  If it’s not Monday, then let’s get yesterday’s data (-1).  Same logic goes with the EndDate variable, which will always have “yesterday” as its value (that is Today()- 1).