Set variable Date Range Prompt in scheduled Intelligence Report

[jdavidson]

Hello, I use an Intelligence Report built in Cognos 11 which has a Date Range prompt (Posted Date from X to Y).  When I manually pull the report, I manually specify the date range each time.  I would like to schedule the report so is it possible to preset a variable date range?  For example, Posted Date from [today-10 days] to [today-3 days].  I'd appreciate any instructions for creating this type of date range prompt.  Thank you

[KevinD · Accepted solution]

@jdavidson hello there. I love giving people good news and I have some for you. Do get what you want, you need to create an expression. Here is an example:

[Posted Date] between _add_days (current_date, -10) and current_date

This expression will look at today's date and go back 10 days. So, just change 10 to whatever amount of days you want it to go back. So, each day you run this report will pull data from today's date and go back the specified number of days.

Give this a try and let me know if it works for you. Do you know how to use the Expression Editor to build a filter?

[jdavidson]

Thank you @KevinD, great news!  I've played with building filters just a bit, so if you can share instructions for using the Expression Editor (or direct me to a good resource) that would be much appreciated.  I'd like to learn how to do it correctly.

[jdavidson]

Hi again @KevinD.  I'm working on this today and found filtering instructions with Expression Editor in the Intelligence Training Manual beginning on page 129.  Is that the best resource?

[KevinD]

@jdavidson yes. Well, the best resource would be someone from our reporting team, but the training manual is the next best resource. LOL.

If you have any issues, let me know. Might be better to do it through private messages so we don't have a long conversational thread. That is if you end up need some guidance.

[BobSch]

So  I need to run a report on a schedule on the first day of each calendar month but need to run it for the Posted Date of the prior month on the 16th up to the last day of the previous calendar month. What would be the expression?