How do I create a report that returns within a range of the user input?

From SmartWiki
Jump to: navigation, search

Q: How do I create a report that returns within a range of the user input?

A: You need to do the following steps to configure this.

1. Setup your report, and use the following on your desired date input field.

Report last2m.jpg

2. For Column 5, use the following code.

The code inside 1 is:
date_format([this],'%Y-%m')
The code inside 2 is:
date_format(date_sub(???, interval 2 month),'%Y-%m')

For Column 6, display your date field.

Using this syntax, upon the user input it will subtract 2 months.

The benefit of using syntax like this is that if you run this report in January, it will return results from the previous year.

*Note: Similar syntax can be used with the date_add function. You could just as easily broaden your translation to be in days to obtain a more accurate result. E.g. 60 days vs. 2 months.