' All' from report picklist (prompt)

[stewm]

I have a number of prompts that are driving off lookup tables in the databases. Within Oracle I created a UNION SELECT ' All' for these. This works dandy if I'm coming off those tables. However how do I get an ' All' option if I'm using a report prompt, for cascading purposes, with certain criteria that goes against a table without the UNION SELECT and only brings back a column of records that contain certain criteria. Is there a way to fool the filter into inserting a value on table of those brought back from the database? In particular report picklists. I don't want to go off the normal lookup table because there are a lot of records on there that are not on the Orders table and if picked the user wouldn't get anything.

Mark Stewart
Consultants Club Corp.

 

[stewm]

Actually I could probably just base my criteria on the Orders tables but display the results from the lookup table and add an 'or ' All' to the filter. In theory should work.

Mark Stewart
Consultants Club Corp.

 

[griffindm]

Mark,

Check out conditional filters in FAQ401-2137. Using this method you can either pick the contents of the prompt, or all rows. Performance can vary though, especially when done on columns that are not indexed or on very large tables.

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