Complex Filter !!!

[slim]

Hi Cognoise friends,

I need some help to implement the filter logic as described below:

Background:
There is 'Type' prompt which has 2 values: Type1 and Type2
There is a 'Category' prompt which have 5 values: Cat1, Cat2, Cat3, Cat4, Cat5

Given:
'Type' is radio button prompt. So users can select either Type1 or Type2.
'Category' is checkbox prompt. So users can select any 1, 2, 3 , 4 or All Categories if they wish.

Problem:
The underlying query needs to filter the data according to following condition only when 'Type1' is selected:
for Cat1 and Cat2: use filterCondition1 and filterCondition2
for Cat3 and Cat 4: user filterCondition1 and filterCondition3
for Cat 5: no filters or do nothing.

I have tried many combinations eg: nested if condition, nested case condition, separating query in multiple sub queries then union-ing them. But it still didn't work :(

I would appreciate any suggestions for this issue and let me know if you need any further information to devise a logic.

Many thanks in advance,
Slim.

[Francis aka khayman]

Are you using dimensional or relational?

[slim]

Dimensional

[Francis aka khayman]

based on the information you gave, this is all I can come up with...

set([Cat1];[Cat2];[Cat3];[Cat4];[Cat5])

[Filtered Cat1] = filter([Cat1];[filter condition1] and [filter condition2] and ParamValue('Type') = 'Type1')
[Filtered Cat2] = filter([Cat2];[filter condition1] and [filter condition2] and ParamValue('Type') = 'Type1')

... use the same logic for the other 'Cats'

[slim]

Hi khayman,

How do I use these filter you mentioned in Report Studio 10.2 ?

Thanks,
Slim

[Francis aka khayman]

sorry, i think there are some error...it should have been:

set([Filtered Cat1];[Filtered Cat2];[Filtered Cat3];[Filtered Cat4];[Filtered Cat5])

and

[Filtered Cat1] =  filter([Cat1];[filter condition1] and [filter condition2] and ?parameter_type? = 'Type1')

Procedures:

1. drag a data item, name it Filtered Cat1
2. use the expression above, substitute the correct filter conditions.







same procoess for all filtered category