Hi,
I am using Web Intelligence Rich Client, I want to use optional prompt. My requirement is like user has option to input 4 values, he need to enter any one.
WHERE
CITY =
DATE =
PRODUCT =
STORE =
he can enter one or more values, if he didn’t enter any value it should be excluded from WHERE clause.
Is there way I can do it?
Thanks,
a.vishnu (BOB member since 2020-07-31)
You can’t use optional prompts in FHSQL, for good reason. Optional prompts work by removing the entire condition from the query if a value is not selected. Since WebI does not manage the generation of the SQL in FHSQL queries, there’s no way for it to remove the condition.
If you require Optional Prompts, you’ll need to create a universe and do it the “normal” way.
joepeters 
(BOB member since 2002-08-29)
Hi,
Thanks for information, I will try your suggestion.
Thanks,
a.vishnu (BOB member since 2020-07-31)
you can also resolve this by using a CASE statement in the where statement that decodes the entered value and if NULL then 1 = 1 .
I picked up this technique from @Alan a while back. Basically, you put your Prompt in pairs of OR statements so that when you pick “ALL” that eliminates the comparison of the corresponding database column since ‘ALL’ = ‘ALL’ will always be true. The last parameter User:X sets the parameter order.
WHERE
(CITY = @Prompt('City', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:0) OR
'ALL' = @Prompt('City', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:0))
AND
(DATE = @Prompt('Date', 'D', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:1) OR
'ALL' = @Prompt('Date', 'D', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:1))
AND
(PRODUCT = @Prompt('Product', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:2) OR
'ALL' = @Prompt('Product', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:2))
AND
(STORE = @Prompt('Store', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:3) OR
'ALL' = @Prompt('Store', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:3))
Here are some documentation links related to the @Prompt function. You can adjust a lot of the properties of the Prompt function to suit your requirements.