Using dynamic date intervals fro a scheduled report 1

[ITUngaBunga]

Cognos Impromptu (Version 7.1.724.0)

Hi

I'm creating a macro to open, run and export an Impromptu report - no problems here.
My problem lies with the report itself - I'm trying to set the query criteria so it filters on the field [InvoicedOn] to be between Date-9 and Date-3

Example being - my report opens on a Monday 15th Jan 2007, it will be filtered for records that have an InvoicedOn date of between 06/01/2007 and 12/01/2007.

I'm having great difficulty getting this work in my report - coming from an Access background I'm inclined to beleive that this is soooo simple I'm really not doing something right!
When I build the query criteria using any of the Date functions, it hardcodes the date in the criteria. I'm wanting the criteria to be 9 days and 3 days from the days date - I'm just not getting this....

:-((

Can this be actually done this way, or do I have to have the macros supply the criteria to the report?

Can anyone point me in the direction to achieving my dynamic date criteria.

Thanks in advance.

Phil

 

[drlex]

Phil,
You need to use the prompt manager to allow input of a date (or two). This will allow you to pass a date (or two) to the report from the macro
Example steps. 'Filter' tab of report, "Prompt Manager..." should be an option on the left hand side). Set your filter so that [invoice date] between [Prompt Manager date input] and [Prompt Manager date input]+9.
In your macro, pass the date to the report using a comma and value after the path
i.e.
Set objImpRep = objImpapp.OpenReport("\\acmefile01\cognos\Reports\Recent Invoices.imr", "2006-05-01")

The format of the string/variable ought to match the input format of the report (.ini settings of cognos and system settings affect this).

N.B. Should you want to set both start and end date in the report prompt, you'll need a pipe character ('|') between the two dates, not a comma:
Set objImpRep = objImpapp.OpenReport("\\acmefile01\cognos\Reports\Recent Invoices.imr", "2006-05-01|2006-05-10")

Hope that helps.
lex

soi la, soi carré

 

[ITUngaBunga]

Hi drlex

Many thnaks for the response - this seems to be the solution to my quandary!

Again, thanks for taking the time to give me the guidance.

Regards

Phil

 

[drlex]

Phil,
You're welcome. Thanks for the recognition.
BTW, if the date range is always calculated in respect to the current date, you could dispense with prompts and use the date functions ( now() or today()) with add-days) to generate the required range.
lex

soi la, soi carré