Skip to content

SSRS Weekday EMail Subcription

April 16, 2010

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).

About these ads
8 Comments leave one →
  1. Hogan permalink
    July 2, 2010 6:28 AM

    Or you could just run it Tuesday – Saturday in the morning. On Monday the boss will see a report that was run on Saturday not on Monday, but so what? It is still the same report and it takes less code = less complex = easier to maintain.

Trackbacks

  1. How To: Report Authoring in SSRS 2005 – Part V (Pimp Your Parameters) « Marlon Ribunal's Note On SQL Server
  2. How can I Host a website i made(html) for free and be able to use the Html? | Host Rage
  3. SqlServerKudos
  4. DotNetShoutout

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 29 other followers

    %d bloggers like this: