"Or"-based filters

[ordotan]

Hi,

I'm trying to implement "Or" based filters mechanism between 4 multi-select value prompts - param1, param2, param3, param4

So I had created a checkbox prompt to select between "And"/"Or" options with param name - "pConditionType"

At first I had implemented it with the following filter :

[Sample Data item1] in ?param1?
?pConditionType?
[Sample Data item2] in ?param2?
?pConditionType?
[Sample Data item3] in ?param3?
?pConditionType?
[Sample Data item4] in ?param4?

This is working OK, only if some values were selected to all prompts. If for example only param1 & param3 were selected, the entire filter won't appear in the SQL, regardless of the selection between "And"/"Or"

So I had changed it to the following implementation:

(#promptmany('param1','string','1=1', '[Sample Data item1]  in (','',')')#)
?pConditionType?
(#promptmany('param2','string','1=1', '[Sample Data item2]  in (','',')')#)
?pConditionType?
(#promptmany('param3','string','1=1', '[Sample Data item3]  in (','',')')#)
?pConditionType?
(#promptmany('param4','string','1=1', '[Sample Data item4]  in (','',')')#)

In this way, even if the user select only param1 & param3 the filter will be generated and applied in the SQL due to the default values, and it works fine for "And" selection. However, when choosing "Or", the filter is translated to:

[Sample Data item1] in ('a','b','c')
OR
1=1
OR
[Sample Data item3] in ('1','2','3')
OR
1=1

This is wrong, because 1=1 is always true and the filter is meaning less. I can't change the default to 0=1, because then I'll have an issue with the "And" selection, as 0=1 is always false, and I'll get no results.


Do you have a work-around for this implementation, or can you suggest other way of supporting "Or" filtering?

Thanks!

[BigChris]

Haven't got time to test it, but would something like this work...

(?pcondition? = 'And' and ([Data1] = ?param1? or ?param1? is missing) and ([Data2] = ?param2? or ?param2? is missing)...)
or
(?pcondition? = 'Or' and ([Data1] = ?param1? or ?param1? is missing) or ([Data2] = ?param2? or ?param2? is missing)...)

[ordotan]

In your suggestion, param1,param2,ect become mandatory filters, and in my case I need them to be optional.

[BigChris]

I'd try setting defaults values of "N/A" for them, then the filter would be something like:

(?pcondition? = 'And' and ([Data1] = ?param1? or ?param1? ='N/A') and ([Data2] = ?param2? or ?param2?='N/A')...)
or
(?pcondition? = 'Or' and ([Data1] = ?param1? or ?param1? = 'N/A') or ([Data2] = ?param2? or ?param2? = 'N/A')...)

[ordotan]

It works now, thanks!

The only issue is that the SQL that is being generated from such filter looks complex and "ugly".
Just a short example from my implementation:(And I have more parameters to apply this filter on)
....
AND(
                        'OR' = 'AND'
                        AND(
                            "AGG_GI_d" . "CELL_REGION" IN('NLZ')
                            OR 'NLZ' = 'All'
                        )
                        AND(
                            "AGG_GI_d" . "APN_SERVICE" IN('Internet')
                            OR 'Internet' = 'All'
                        )
                        OR 'OR' = 'OR'
                        AND(
                            "AGG_GI_d" . "CELL_REGION" IN('NLZ')
                            OR 'NLZ' = 'All'
                            OR "AGG_GI_d" . "APN_SERVICE" IN('Internet')
                            OR 'Internet' = 'All'
                        )
                    )
....

I wish that there was a more solid solution from Cognos to handle "Or" filters.