We recently came across a request that seemed simple enough. But when we started building the template requested, we all learned a thing or two!
Problem: How can I run an automated monthly report without using any input variables?
Our client needed to run a report using the Javelin Reporting Portal to produce an automated monthly report on the first of the month containing data from the previous month.
So, for example, a report would be automatically generated on August 1 that included data from July 1-31, a report would be automatically generated on September 1 that included data from August 1-31, and so on.
The key issue for this client was that they couldn’t make use of any input variables. This report had to run on its own through Javelin and needed to include only the dates for the previous month.
Solution: Use a combination of Out tags, Set tags and the Windward DATESPAN function.
At first we weren’t sure why automatically generating this report was so problematic. You can simply set the start and end dates of a report using Set tags (or possibly use some If tags or even conditional formatting for some Out tags), right?
We ran into the first snag with the Set Tags. It’s easy to set the date for the end of the reporting period. Windward has an easy function called TODAY that will return 12:00AM for the day the report was run.
In Javelin, we set the date and time for the report itself to run (in this case, the first day of each month). Then in the Select Statement Wizard, we used a Set tag with the value =TODAY() as the variable for the end date of the data in the template (so that any data reported up until “yesterday” at 11:59PM is output in the final report). Here’s what it looked like:
But when setting the begin date, =TODAY() – 31 will only work for some months, and let’s not forget about February during Leap Years!
This function can’t be nested within our MONTH function either (because it accepts only numeric dates).
So instead of nesting different loops or If/Else clauses to create different functions depending on the month (or, ugh, year!), we used our DATESPAN function.
The DATESPAN function
The DATESPAN function lets you add or subtract an individualized set of days, months and or years to another date. It is written as DATESPAN(Y,M,D), with the year, month and day as integers.
For example, if today is 8/1/2016, and I add DATESPAN(3,2,1), the value that will be returned is 10/2/2019. Technically, it will be returned as “Sat Oct 2 00:00:00 MDT 2019” (even the right time zone!).
So to create our template, I performed these two steps:
- I set an Out tag with TODAY()-1 and set the formatting to list the month only (not the full date).
- I created a pair of Set Tags where the begin date is TODAY() – DATESPAN(0,1,0) and the end date is TODAY(). By subtracting one whole “month” from any date, we are guaranteed that Februaries during Leap Years won’t have you up at night wondering whether your report will have the right info.
NOTE: Remember that the date/time returned in our example by TODAY() will be 8/1/2016 00:00:00, so setting the filter in the Select Statement Wizard to be “less than” or “before” this date/time will give you everything up until 7/30 11:59:59PM.
We then scheduled the report (via Javelin) to run on the first of each month.
Here’s the template:
And the final output:
For More Information
Want to learn more about Windward’s functions and equations? Check out our wiki article Windward Equation Overview.
Author: Beth Billington
Beth enjoys using her writing and graphic design background for writing easy-to-follow help content. She loves learning – whether at work or at home – and spends her free time hiking, doing landscape photography, playing music, baking and pursuing all kinds of hand crafts.
Other posts by Beth Billington