7 Replies Latest reply on Jun 6, 2013 5:29 PM by Celvin Kattookaran

# How to count number of members on specific level at specific moment?

ASO
4 dims(for example)...
-Times
-Product
-Organizations
-org1
-org2
-orgN
-Measures
-cost
-org_num

I want to identify the count of memebers in Dimension Organizations at any time(f.e. for all Products in January in) . What is the syntax of mdx formula in outline?
• ###### 1. Re: How to count number of members on specific level at specific moment?
Try count http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/mdx_count.html

Regards

Celvin
http://www.orahyplabs.com
• ###### 2. Re: How to count number of members on specific level at specific moment?
Thanx. But it is
NonEmptyCount(Organizations.currentmember.leaves,Measures.cost)
• ###### 3. Re: How to count number of members on specific level at specific moment?
NonEmptyCount is going to give you only those members which have a cost (acc to your formula), however you asked for count of all members under org.

Regards

Celvin
http://www.orahyplabs.com
• ###### 4. Re: How to count number of members on specific level at specific moment?
Sorry for bad describtion, but i dindnt know that i have to use Cost in formula to get the correct result
Count gave me the same result at any level of any dimension, but I wanted to count number of members on specific level at specific moment(wanna get different values).
• ###### 5. Re: How to count number of members on specific level at specific moment?
Here is an example that i've right now

Count (EXCEPT([Dimension1].Levels(0).members,Descendants([Parent 1],Levels([Dimension1],0))))

This will give all the level 0 members of dimensions except level 0 of a parent.

I'm assuming that by specific moment you mean a specific measure.

NonEmptyCount (Market.Levels(0).Members, Sales,exclude_missing)
``````With
Member [Measures].[Number Of Markets]
as 'NonEmptyCount (Market.Levels(0).Members, Sales,exclude_missing)'

Select
{[Measures].[Number Of Markets]} on Columns,
{[100].Children, [200].Children} on Rows
FROM Sample.Basic
where ([jan],[actual])``````
This will give you

(axis)     Number of Markets
100-10     20
100-20     16
100-30     8
200-10     20
200-20     17
200-30     9
200-40     3

Now if you clear one market (I cleared colorado->100-10,Sales,Actual,Jan) and then the result will change to
(axis)     Number of Markets
100-10     19
100-20     16
100-30     8
200-10     20
200-20     17
200-30     9
200-40     3

Regards

Celvin
http://www.orahyplabs.com
• ###### 6. Re: How to count number of members on specific level at specific moment?
"I'm assuming that by specific moment you mean a specific measure."

For this task it doesnt depend on specific measure at all.
I want to identify the number of leaves(level 0 members) of the Dim Organizations which at least has one any measure in given(specific) conditions(values of another dimensions).
• ###### 7. Re: How to count number of members on specific level at specific moment?
That is what the nonempty example is doing. It checks for all level 0 market members which has Sales. (exclude missing makes sure you get only those which has sales)

Regards

Celvin
http://www.orahyplabs.com