Selecting all values in an Impromptu Prompt 1

[ninfan]

Hello, everyone. I did a quick search and didn't see a similar question out there so here goes...

I have an Impromptu pick list with several dozen values. Users typically select 1 item or all items. If the user selects all items, Impromptu builds a giant "IN" statement that is very inefficient. I'm trying to figure out how to alter the final SQL when this occurs. If I could dynamically control the SQL I would do something like this:

IF 1 Item Selected THEN " WHERE Field = Value Selected"
ELSE IF all Items Selected THEN do nothing

Since my pick list represents all available values in the "Field" column I don't need the WHERE statement line for this at all if all items are selected.

Any great ideas out there? Thank you!

Troy

 

[griffindm]

Troy,

There are generally three ways of doing this. The first is to create a view based on the table that would be used to filter, and add the value " All" to the list. Note the leading space. This should move the All to the top of the list. Handle the selection of the ' All' value programatically in the filter statement. See the thread referenced below on how to use an If statement in the filter.

Another alternative is to use a type in with a default of '%', and use the 'Like' clause. Instruct the user to type the value or leave as '%' for All.

The last alternative is covered in depth in thread401-384839.

Hope this helps,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[ninfan]

Thank you, Dave. I will evaluate the options you suggest.

Troy

 

[DoubleD]

There is a third, more efficient way to do this.
Make a second prompt called ?Select All? asking if the person wants to include all values.
Select All will be a Type In with a default of N.
Your filter would look like:
(?Select All? in ('N','n') or Item in (?Item Picklist?))
This will pass no filter to the database when anything other then N or n is entered, but will pass the actual values selected when N or n are entered.

 

[griffindm]

DoubleD,

This is actually covered in the pointer thread referenced within the original thread I suggested to ninfan. Hsiao, the poster in that thread, was actually able to do a dynamic filter from either of two tables in the report without the extra prompt. I was impressed.

I got your suggestions on the FAQ. When I have the time I will incorporate your suggestion as well as the techniques listed that above thread into the FAQ. This question has been popping up more frequently lately.

Regards,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[benbotektips]

The simplest way I took care of this was to insert a row directly into the table from which the prompt was extracted, with ' ***ALL***' in the appropriate field.

ex. Customer table
id_num cust_name
0 ***ALL***
1 Moe
2 Larry
3 Curly
etc.

SQL:
insert into customer_table values 0, ' ***ALL***'

then, create a catalog constant (in a folder), called 'all value', value ' ***ALL***'. Finally, create a catalog prompt off cust_name.
Now, anytime you need to include all choices in that prompt, the filter condition would be 'cust_name in (?customer_prompt?) or all value in (?customer prompt?).