In general when we select the prompts in Cognos and run a report the query generated will be like
Select * from table where prompt1=X and prompt2=X and so on....
I have a requirment where I have 8 prompts and when all the 8 are selected them the first three of them should be selected as AND in the query but the remaining prompts should be a combination of OR Condition.
query is like
Select * from table where prompt1=X and prompt2=y and prompt3=u and (prompt4=c OR prompt5=f etc...
For Example here all the 5 remaining prompts have only two values Y and N.
so if I take a combination of two prompts as prompt4=Y and prompt5=N then all the records which have
prompt4=Y and prompt5=N and
prompt4=N and prompt5=N and
prompt4=N and prompt5=Y
Should Display.
Hi,
The SQL predicate is built based on the filter syntax you add to your report. If you have eight separate filter expressions, they will be ANDed together in the predicate. The trick is to combine them all into a single filter expression in your report, using the desired combinations of AND and OR in the expression (with relevant parentheses, of course!) :)
Cheers!
MF.
Thanks ... Was trying something similar but the requirements changed and no need to change the functionality now :) :)