Use filter on Catalog in Impromptu Macro?

[MPRC]

Can one place a filter on a catalog using an Impromptu macro? I would like to filter from the first day of the current year to the last day of the previous month.

Can this be done? If so, how? If not, any suggestions?

Thank you.

 

[griffindm]

Why do you want to do this within a macro? If it is a run-time requirement, then use a prompt within the report. If it is a full-time permission restriction, then enter it into the macro manually. While you can manipulate the catalog via a macro, it is complicated and may be overkill unless there is a valid reason for it.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
Magic with Data
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[MPRC]

I am very grateful for your response and thank you. I am relatively new at this and have learned most of what I know by reading your suggestions to other users.

I created a macro for our Finance Department. The macro creates hundreds of reports and saves them in multiple places in "pdf" format. All works very well. Now. . . .
They would like me to include another report that requires a filter such that it will include data from 1 January of current year to last day of previous month. There appears to be a problem in setting the filter in the report.
Any way I can set the filter in my macro for them?
Any way I can find date functions that Impromptu supports?

 

[griffindm]

Two ways of doing this. One is a dynamic filter in the report done without prompts. The other uses string-based prompts passed from a macro.

The first case is easier, but presumes the report is ALWAYS run within the year you are reporting on (i.e. if the report is run on Jan 2, 2007, you want data for 2007, not the prior year). Create a filter in the report using a calculation to derive the first day of the current year, as in "First of Year" defined as make-datetime(Year(Now()),1,1) and the last day of the prior month, defined as first-of-month(Now())- 001 00:00:00:000. This will work without requiring a prompt or catalog filter.

The second approach is more flexible, and involves passing the same defined values (first of year, last day of prior month) defined in the macro, to the report as strings, using string prompts that are compared to the data using a string-to-date conversion function. Define date filters in Impromptu based on string prompts (this will involve date or datetime-to-string conversions or possibly string-to-date or datetime conversions if done backwards).

Hope this helps.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
Magic with Data
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com