How to modify a report filter using CognosScript?

[DoubleD]

I've done a fair amount of CognosScript and VB to automate Impromptu reports, but I'm stuck on this one.
Users have a report that they want to run manually once in a while. But when we run it through code, I need to be able to pass it some codes to filter on. They do not want a prompt added, so I was thinking I could open the report, edit the Filter, retrieve the records, and close it again.

Unfortunately, I've looked through the Impromptu Macro book and ScriptEditor book and couldn't find any solutions.

Has anyone done this before?
griffindm,
I'm hoping you've got some input.

I am what I am based on the decisions I have made.

DoubleD

 

[griffindm]

DoubleD,

Not sure about the requirements (I know you've stated them, but I'm not sure I'm following them ). It sounds like you do have a prompt in the report for use with your macro, but the end users find no utility in that prompt when they run it interactively. Do I have that right?

Obvious question is why not give them a different named version of the report without the prompt?

I will sometimes add a second prompt that controls the behavior of another prompt in the filter. As in, "if anything is in the second prompt, ignore the first prompt". This is done using conditional logic in the filter statement, which can have an impact on performance, but is handy to use when the data set is small or well indexed relative to the report parameters.

I hope this helps. I really appreciate the work you've been doing here helping the Cognos users. Keep it up!

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

 

[DoubleD]

Sorry about the confusion. I'll try to clarify a little.
I have a report that users need to sometimes run manually. It contains a bunch of prompts. Additionally, when they need to filter on Shipper or Receiver Codes, they manually edit the filter to do so. The reason a prompt is not included is the fact that they're may be thousands of codes to choose from.
Building a prompt is not an option as a type-in will not allow you to enter multiple values

Separately, I have automated the run of this report. They have an entry screen where they put in all the appropriate info, then my process runs a series a reports.

I need to be able to pass a list of Shipper codes to the report (only if the person provides a list). So what I'd like to do is edit the filter to add an IN statement.

The reason I don't create a separate report is the type-in limitation and the problem with maintaining two versions of an essentially identical report.

My recent thought has been to provide a Yes No prompt that asks if they want to filter on Shipper Codes, and have a text file read in if they select Yes.

Will this work? I'll be playing with the idea over the next couple days.

I am what I am based on the decisions I have made.

DoubleD

 

[griffindm]

DoubleD,

I feel your pain. You can pass a multi-value prompt from macros or VB if you define the report prompt as a catalog prompt. Admittedly, you would have to increase the picklist limit to a couple K to see all your values, but the macro/VB would work regardless. Pass the multi-value prompt as a csv within the string, where pipes "|" separate the prompts.

If the code column is indexed there should not be a significant effect on report performance. If not it could become a dog.

I'll look to see if you can use another prompt value to bypass the processing of the IN prompt totally, but this may depend on the database type you are using. Could you give me that info?

Regards,

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

 

[DoubleD]

I'm using Oracle for this one.
Currently I'm playing with a Yes/No prompt, then a FilePicklist. Using an If statement in the filter.

My biggest concern with a Catalog prompt is performance. When the user opens the report, that picklist is going to attempt to populate (bad thing). Especially since not all the values that would result would be valid. Unless I based the shipper filter on another report with some other codes in it. Which again impacts performance.

Aargh. If I could just modify the filter expression through code this project would be easy.

I'm also working with the If statement to see if I can get the report to ignore the IN statement and ReportPicklist if the Yes/No prompt is NO.

I am what I am based on the decisions I have made.

DoubleD