Allow dynamic date filters to be user prompt

You can use a parameters for this. Create two parameters. One for today's date and one as int. We'll call them Today's Date and Days Offset.

1. Create a parameter in the view as numeric parameter, default it to 0 and call it something like "Days Offset".

2. Create a parameter and call it Today's Date

3. Back in the report create a simple calculated field that calculates the Invoiced Datetime + Days Offset. If you are using SQL Server Invoiced Date must be Datetime for this to work if it's not then create a calc field and cast it. If you are using PG then it must be cast as date. In the below calc field we're setting a flag 'Yes' if Invoiced Date + Days Offset > than Today's Date else 'No'. You can choose 0 / 1 or anything else but I have a pro tip at the bottom that will make you look like a star. You have to use simple formula type for this as you cannot calculate parameters using freehand techniques. You cannot subtract from the date parameter otherwise I would have rearranged the formula to a more user friendly notation.

4. Add the Offset calc field to Filters and set the operand it "Equal To" and define the value as Yes. If you want to use the pro tip do not define the value yet.

Pro Tip

Just in case you're wondering, you're done. It's that easy(sort of). The below is a smooth little trick I thought of in the last minutes of writing this.

5. Create a calc field called Use Offset and set it to 'Yes'

6. Add Use Offset calc field to Filters and make sure Offset is set to User Prompt and opperand is set to Equal To

7. In the Offset filter click specify parent and choose Use Offset as parent. Check Dependent values and Dependent display(if you wish).

8. Go to Entry style and choose yes as the default value.

9. If the Use Offset is set to Omit then the offset days won't work which means that you can feel free to add another date filter like date range for the invoiced date if you wish. Also, if you notice Yes is for the dates that are less than or equal that the offset. This means you can change the Offset calc field to say something like 'Invoices dates less than X days', 'Invoice dates greater than X days' but with it defaulted to Yes the report user won't have to think about anything else. Hope this helps.

Thanks Larry!

It is certainly possible to use parameters to solve these types of problems.

This enhancement request is specifically intended to make that process a bit simpler :)

Nathan

Hi,

Thanks for the tip, though I still don't fully understand the technic. How can i tell the my date field to be greater than the calculated date field?

I still need a way to set a unit (hours, days, week, months) and the ability to set the parameter to either + or - in time.

Thanks,

Yoav

Hi Yoav,

Yes, I think for your use-case this enhancement is your best bet.

Nathan

Is this enhancement still in motion? Are there any updates?