Automatically Populate Date Prompts

Posted by lm on April 25, 2009 under Corvu |

In CorVu, you can automatically populate your date prompts, based on a pre determined formula.

Firstly, determine the format of the date required (and I am assuming it’s already been set up in the Knowledge Library).  This can be seen by simply putting a prompt on the field and clicking a date.  In this example, I’ll assume it’s DD/MM/YYYY.

Next set up date prompts on the report as normal.  Then go Data/Prompts/Work With Prompts.  Select the date prompt and on the right of the prompt name, set the drop down to be ‘Auto Populate’.  This activates the formula box below.  Remember, if you have a too and from prompt, you will need to set up the auto populate for each prompt.

In the formula box, you enter the formula to produce the date that you want, in the format required.  Some examples are given below.

Today’s Date:
The formula is today()
and with the formatting, the formula is
format(today(),’DD/MM/YYYY’)

Yesterdays Date:
The formula is today()-1
and with the formatting, the formula is
format(today()-1,’DD/MM/YYYY’)

Last Month:
Here I am setting 2 prompt formula’s, one for the Start Date and For the End Date, i.e. the first and last days of the previous month.

Start of Previous Month
format(firstday(firstday(today(),’cm’)-1,’cm’),’DD/MM/YYYY’)
Here I find the first day of the current month, minus one to get the last day of the previous month and then find the first day of that month.  This is greatly helped by the CorVu function, ‘firstday’.

End of Previous Month:
format(firstday(today(),’cm’) -1,’DD/MM/YYYY’)
Here all I need to do is to find the first day of the current month, minus one to get the last day of the previous month

Easy!  Thanks for reading, and if you have more time to kill, why not follow me on Twitter, where I am known imaginatively as LMOTTRAM.