Sending a Report in a scheduled email every two weeks

This post will explain how you would send a report at a frequency that is not covered by the Send Scheduled Email options (Daily, Weekly, Monthly), such as once every two weeks.

This is possible using calculated columns within the report itself, which check the week of the year, and the day of the week.

Why would I want to do this?

There may be reports that you want to send more often than once a month but feel that sending them weekly is too often, such as:

• Activity Vs Target
• Pipeline Progression

How do I create this?

Step 1. Edit the Report

The first step we must take is to edit the report itself, so that it will only show the data on a specific day of every other week. In this example we will do it for every other Monday.

Create a Calculated Column in the Details tab, using the formula

            WEEKOFYEAR(CURDATE()) % 2

The WEEKOFYEAR function labels each week of the year with a number from 1 - 53. The above function will let you know if the current week is an odd or even numbered week, by outputting a 0 on even weeks and a 1 on odd weeks.

Now create another Calculated Column with the Formula

            DAYOFWEEK(CURDATE()) = 2

The DAYOFWEEK function labels each day of the week with a number from 1-7, where 1 is a Sunday. The above formula will output a 1 if the current day of the week is a Monday, and a 0 otherwise. If you want your scheduled email to go out on a Friday, the formula would instead be DAYOFWEEK(CURDATE()) = 6

When you refresh the preview you will see your new columns, and depending on what the date is when you view the report, you should see all 1 or all 0s in each field. For example, if it is Monday on week 22 then both Columns will display a 1, if it is Thursday on week 3 then both Columns will display a 0.

Step 2. Create the Criteria.

You will need to add new Criteria to restrict the report to only data when the current day is on the correct day and in the correct week.

For the report to only send on specific days of the week, add a criteria for the column with the formula DAYOFWEEK(CURDATE()) = 2, it needs to equal 1

Depending on which week you are starting this on, you can choose if you want the report to send in odd or even weeks. If you want this report to send in evenly numbered weeks, add a criteria for the column with the formula WEEKOFYEAR(CURDATE()) % 2, it needs to equal 0.

For oddly numbered weeks, it needs to equal 1.

Now if you press Refresh preview you will see either a blank table or a completely populated one; if there have been no changes this is because the Criteria added match today’s date.

Step 3. Create Scheduled email.

Now save the Report and create a New Scheduled Email via Start > Reporting > New Scheduled Email.

In the example below, the email is going to be sent on a Monday, so we want the system to run the schedule every Monday. However, due to the criteria in the report, the report will be blank every other Monday; we can therefore untick the ‘Send if empty’ checkbox to stop the report from sending every other Monday.

More complicated criteria examples:

• Sending every 3 weeks:

Change the WEEKOFYEAR formula to WEEKOFYEAR(CURDATE()) % 3. This will output the numbers 0 to 2 depending on the week number, e.g. if it is week 3, 6 or 9 then it will output 0, if it is 4, 7 or 10 then it will output 1, and if it 5, 8 or 11 then it will output 2. You can then amend the criteria accordingly.

• Sending on more than 1 day of the week:

Simply amend the DAYOFWEEK formula to include OR statements, e.g. DAYOFWEEK(CURDATE()) = 2 OR DAYOFWEEK(CURDATE()) = 4 to send on Mondays and Wednesdays.

• Sending on the 10th day of the month:

You would only need one calculated column rather than two for this. Add a column with the formula DAY(CURDATE()) = 10, which will output a 1 on the 10th and a 0 otherwise, then add a criteria for this column to equal 1.