COGNOiSe.com - The IBM Cognos Community

Hello,
I have working in Report Studio, my package is based on a transformer cubes and I am trying to filter any negative values from Revenue.

I have tired several different approaches but nothing worked so far. I will really appreciate any response.

Here is what I tried so far as a slicer and in the Revenue data item as well:

filter([my_cube].[Products].[Sales person].[Sales person],(my_cube].[Measures].[Revenue]) >0)

filter([my_cube].[Products].[Sales person].[Sales person],tuple([sales_lpm].[Measures].[Revenue]) >0)

I am not getting any error but it is not filtering out the negative values.

Thanks

Is there a typo in your first expression? You have a ( where there should be a [.

filter([my_cube].[Products].[Sales person].[Sales person],[my_cube].[Measures].[Revenue]) >0)

I believe this should work, assuming the MUNs are correct.

Thanks bdbits..

Yes, it is a typo.

Not sure what do you mean " assuming the MUNs are correct"
I want to filter the revenue based on the entire sales team not just one. I will use MUN if I want to filter a specific member.

Thanks

Quote from: CognosDev007 on 12 Nov 2014 02:41:58 PM
Hello,
I have working in Report Studio, my package is based on a transformer cubes and I am trying to filter any negative values from Revenue.

I have tired several different approaches but nothing worked so far. I will really appreciate any response.

Here is what I tried so far as a slicer and in the Revenue data item as well:

filter([my_cube].[Products].[Sales person].[Sales person],(my_cube].[Measures].[Revenue]) >0)

filter([my_cube].[Products].[Sales person].[Sales person],tuple([sales_lpm].[Measures].[Revenue]) >0)

I am not getting any error but it is not filtering out the negative values.

Thanks

Hi,

If you use an expression in a slicer filter, it will act as context for the measures (ie the values in the cells in your crosstab will change) but it will not affect the row or column heading sets in your crosstab. This doesn't sound like the result you require here?

From your post, it looks like you need to show only those Sales Person members with a total Revenue value greater than zero?

If so, delete the Sales Person level from your crosstab and replace it with a query calcuation that has the expression

filter([Your Sales Person level], [Revenue] > 0)

This is essentially what Bob was saying, too, although he was trying to make sure that the expression you posted has valid syntax for your cube structure.

MF.

Thanks MFGF..

I got it working  :) :) :) :) :)

Thanks MFGF. I did not say it very well.

Glad you got it working, CognosDev007.