member function filtering olap

[cognos05]

Cross Tab reports on a olap package looks like below  with the measure quantity
Rows                                     Columns
                                                 Years
Product A                                   12
Product B                                   23
A+B                                           35
Product C                                  56
Product D                                  44
C+D                                         100
Product E                                  45
Producr I                                   15
E+I                                            60
Overall Total(A+B,C+D,E+I)      195


I should show these only for reps whose overall total is greater than 0

So my expression for reps was  filter( reps,tuple(OverallTotal,currentMember(column hierrarchy for years),Quantity)>0)

since overall total is a dataitem with expression (A+b,C+D,E+I)   it says this parameter is numeric type and it expects a member.


If I change my expression to filter( reps,tuple(member(OverallTotal),currentMember(column hierrarchy for years),Quantity)>0)

what does member() function does in olap. I am not sure if this would work or if there is some other way to filter on overall total for reps.

Thanks,
Nithya

[MFGF]

Cross Tab reports on a olap package looks like below  with the measure quantity
Rows                                     Columns
                                                 Years
Product A                                   12
Product B                                   23
A+B                                           35
Product C                                  56
Product D                                  44
C+D                                         100
Product E                                  45
Producr I                                   15
E+I                                            60
Overall Total(A+B,C+D,E+I)      195


I should show these only for reps whose overall total is greater than 0

So my expression for reps was  filter( reps,tuple(OverallTotal,currentMember(column hierrarchy for years),Quantity)>0)

since overall total is a dataitem with expression (A+b,C+D,E+I)   it says this parameter is numeric type and it expects a member.


If I change my expression to filter( reps,tuple(member(OverallTotal),currentMember(column hierrarchy for years),Quantity)>0)

what does member() function does in olap. I am not sure if this would work or if there is some other way to filter on overall total for reps.

Thanks,
Nithya

Hi,

How does Reps fit into this data? I see Products here - is Reps another dimension? Is it another level of the same dimension? Can you explain what it is you need to do and how reps, products and years fit into this? What is your measure - Quantity?

Cheers!

MF.

[cognos05]

Reps is another dimension which will be in the rows and products is nested under it.
the report will show each reps and products and their sales quantity for the respective year to data. and my filter here is on reps to show only reps whose overall total for products is greater than zero.

Thanks,
Nithya

[MFGF]

Reps is another dimension which will be in the rows and products is nested under it.
the report will show each reps and products and their sales quantity for the respective year to data. and my filter here is on reps to show only reps whose overall total for products is greater than zero.

Thanks,
Nithya

Hi,

Ok - products are nested within reps. Next question - are the products you show here the entire set of products from the products level, or are they a selection of products and others exist that you are not including? For example, you show products A, B, C, D, E and I here. Are there other products not being used in your report or are these all the products you have?

What about the years - are you just including one year in your columns, or are there multiple. If there are multiple, are they all the years from your year level?

If these are all the products you have, and the quantities are for all your years, then this should work:

filter([your reps level], [quantity] > 0)

If you are using a defined set of products and all years, you could try this:

filter([your reps level], aggregate([quantity] within set set([Product A],[Product B],[Product C],[Product D],[Product E],[Product I])) > 0)

If you are using a defined set of products and a defined set of years, you could try this:

filter([your reps level], aggregate([quantity] within set set([Product A],[Product B],[Product C],[Product D],[Product E],[Product I]),set([Your first year],[Your second year],[Your third year])) > 0)

I'm still having to guess, though, because you haven't made your requirements clear.

MF.

[cognos810]

Hello Nithya,
I would sugggest this since you are not displaying the REPS in the Crosstab.
Create a data item as such:
FILTER([REPS LEVEL],[Quantity]>0) ...You do not need to do the overall total as the totals will be automatically aggregated per REP and then Filter applied.
Then DRAG and DROP this data item in the SLICERS section of the query.

-Cognos810

[cognos05]

Hi MFGF,
The expression for years is Descendants(Ytd,1) .  So this will give me the current months that exists in  YTD.
And coming to products I am using products from one dimension ,but only few selected products and randomly grouping them and then doing a full total.
Say If I have 25 products like below
a,c,d,e,f a+c+d+e+f,e,r,t,y,u ,e+r+t+y+u,k,l,k+L,overall total

so should I use all these 25 products in the below filter expression.Cant I directly use the overall total.
filter([your reps level], aggregate([quantity] within set set([Product A],[Product B],[Product C],[Product D],[Product E],[Product I])) > 0)

Thanks,
Nithya