A single prompt for selecting a specific month

Thursday 6 March 2014 @ 11:47 pm

A user wanted some help improving a report parameter. They want the user to select a specific month using a single prompt. Currently they have to maintain a list of values that looks like this:

2013 Dec
2014 Jan
2014 Feb
2014 Mar

This list not only has to be maintained each year, but it has to be converted from a string into a date range to be used as criteria. I suggested that instead they use a regular DATE parameter. I think the simplest way is to make the prompt into a DATE prompt and tell the user to pick the last date of the target month. I don’t always tell the user that they can pick ANY date in that month and the method works the same.

The method is to write 2 formulas that turn the date prompt into the first and last day of that month. Then you use these two formulas in your selection formula. So, it is still one parameter, and one that is fairly intuitive. And because the calendar is automatically populated, you don’t have anything to maintain.

Here are the two formulas (note that the second refers to the first):

//First Day
{?DateParam} – Day ({?DateParam} ) +1

//Last Day
Date (DateAdd (‘m’, 1, {@First Day} )) -1

Then your selection formula could be:

{Your.DateField} in {@First Day} to {@Last Day}

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro