Report Studio Criteria for Date Range = Current Month # minus 1

[jeffreygreen]

I run reports every month, for the PREVIOUS month. I always run them between the 9th of the month and the 15th of the month.
Currently I have a prompt for Month Number, but with the number of reports I am beginning to run, I would like to be able to have the date range criteria (filter) be the Current Month - 1.

So on April 9th, when I run March's reports, I would like Month # = 4-1

How can I do this in Report Studio? Is it even possible in Query Studio?

Thanks

 

[blom0344]

extract(month,current_date)-1 would yield 4-1 = 3

To correct for crossing of years you would need to write an expression that copes with month 12:

CASE
WHEN
EXTRACT(MONTH,CURRENT_DATE) = 1
THEN 12
ELSE EXTRACT(MONTH,CURRENT_DATE)-1
END

In the end you should extent this for the combination of months and years.

Finally, when you build this into the framework you could use it both in RS and QS



Ties Blom