Dimensional Filter on Tuple

[Dinok]

I have been trying to avoid a detail filter on a crosstab using OLAP.  The crosstab is :

                   2001     2002     2003
prod 1           20         30         22
prod 2           30          5            7
prod 3           11          22         12

I only want numbers over 20, so the reult should be :

   QTY          2001       2002      2003
prod 1                          30         22
prod 2          30
prod 3                           22

A detail filter works fine, but best practice and all that!

I have tried the filter function filter(product, tuple(currentmember(time),qty>20) but it isn't working.
I have also tried currentmember for rows and columns.

Please help!

[willg]

Would a case statement solve your requirement? eg.

case when [qty] > 20 then [qty] else null end

[Dinok]

Thanks Willg,  I guess it would.  Is it as efficient as using the filter statement?

[Lynn]

I have been trying to avoid a detail filter on a crosstab using OLAP.  The crosstab is :

                   2001     2002     2003
prod 1           20         30         22
prod 2           30          5            7
prod 3           11          22         12

I only want numbers over 20, so the reult should be :

   QTY          2001       2002      2003
prod 1                          30         22
prod 2          30
prod 3                           22

A detail filter works fine, but best practice and all that!

I have tried the filter function filter(product, tuple(currentmember(time),qty>20) but it isn't working.
I have also tried currentmember for rows and columns.

Please help!

Your filter function is looking to remove entire product rows based on the condition. You can't filter out any product rows because all are needed for at least one of the time periods.

You could also try a conditional style on the intersection CellValue() < 20 and then hide the value via styling.

[CognosPaul]

What are you trying to achieve by hiding values below the threshold? Is it for the purposes of display, or will you need to perform a calculation on them? Hiding the value from the page will not remove those values from future calculations.


If you do need to hide the values on the query level, you can modify the measure to something like if(tuple(measure,currentMember(hier),currentMember(hier))>20) then (null) else (measure)

The benefit of doing that is you can continue to slice further down on those values. So whole prod1, 2013 is 30, each quarter may be under the 20 min. A detail filter  will remove them while the if statement will not.