We are trying to create a report that will, when scheduled, pull the prior months expense data but for the first of the year, i need to have the report pull December 2020 data. I can manually set the filter to pull the year 2020 and the month 12, but when scheduled, i need the filter to execute automatically.
Any thoughts on how to easily accomplish this?
Thank you
Tom
Quote from: gats1527 on 21 Jan 2021 12:43:48 PM
We are trying to create a report that will, when scheduled, pull the prior months expense data but for the first of the year, i need to have the report pull December 2020 data. I can manually set the filter to pull the year 2020 and the month 12, but when scheduled, i need the filter to execute automatically.
Any thoughts on how to easily accomplish this?
Thank you
Tom
Hi,
Are you using a relational package or a dimensional package? The solution is different for each.
If it's relational, then something like:
(extract(month, current_date) = 1 AND [Your Year item] = extract(year, current_date)-1 AND [Your Month item] = 12) OR (extract(month, current_date) > 1 AND [Your Year item] = extract(year, current_date) AND [Your Month item] = extract(month, current_date)-1)
If it's dimensional, then use a dimensional expression in your report that uses a lag() function based on the current month member of the month level.
Cheers!
MF.
thank you. this is exactly what i needed the output to be
You might be able to do something simpler than this:
Quote(extract(month, current_date) = 1 AND [Your Year item] = extract(year, current_date)-1 AND [Your Month item] = 12) OR (extract(month, current_date) > 1 AND [Your Year item] = extract(year, current_date) AND [Your Month item] = extract(month, current_date)-1)
[Your Date Item] between _first_of_month (_add_months(current_date,-1)) and _last_of_month(_add_months(current_date,-1))It all depends on your data really and the structures you're using...many ways to skin a cat and all that :D