cancel
Showing results for 
Search instead for 
Did you mean: 

child count of a dimension member

Former Member
0 Kudos

How to find the child count of a dimension member and put the results in an excell column?

I have entities in one column and in the second column i need to have the children of respective member.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Kiran Kumar,

I guess if i understand correctly. You want to find Average based on number of children instead of SUM.

In BPC always into hierachy you will have the parenth like sum of children.

In your case you can use dimension formula wher you don't need to addup all children numbers into parent but into property formula from your dimension you will specify that this member will be calculate average.

You have also MDX formula where you can specify that it is Average of all children.

Anothher solution will be to add a custom measure

(You have an HTG published into BPX/SDN regarding custom measure) to be calculate always like average of children.

I hope this will help you.

Rajesh Muppala.

Answers (2)

Answers (2)

Former Member
0 Kudos

What wandi said would work, but I would use COUNTIFS

- add a parent column (assuming you already have a property that mimics PARENTH1)

- your expansion will have to expand on self,all

- Your sheet will look like this

Col A - Entity ID

Col B - Parent property

Col C - CountIF

A B C

1 E1 2

2 E2 E1 =COUNTIF(A2,$B$1:$B$9)

3 E3 E1 0

4 E4 5

5 E5 E4 0

6 E6 E4 0

7 E7 E4 0

8 E8 E4 0

9 E9 E4 0

You can do this on a hidden worksheet and do a vlookup on the sheet you need the number in.

Former Member
0 Kudos

Thanks nick

you have been a lot of help

thanks for helping

former_member599120
Contributor
0 Kudos

Dear kiran kumar thamatam,

I have an idea for you case. If you had simple hierarchy for your member, let say parent and child. Please you do some tricks from me as following:

1. You could provide some columns in Ms.Excel likes: Account (column A), Parent (column B)

2. Please you use Ms.Excel function SUMIF for calculate the number of members and put it in somewhere cell. The function SUMIF could have some filter criteria for count the number of members, You could reference column B for SUMIF filter criteria.

I apologize for could not provide more information SUMIF format because I really didn't know your dimension hierarchy. Please you refer the Ms.Excel helps for more information how to use SUMIF.

Kind Regards,

Wandi Sutandi

Former Member
0 Kudos

thanks for the reply

my requirement is

i have the entity members in column A and i need to find no of children(child count) of each of the member in column A and display it in the column B

for example

Column A Column B

member no of childern

entity1 4

entity2 6

entity3 5

i really appreciate if u have an answer

Edited by: kiran kumar thamatam on Apr 11, 2010 9:52 PM