Skip to Content
avatar image
Former Member

child count of a dimension member

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 21, 2010 at 06:13 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 10, 2010 at 01:48 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • avatar image
    Former Member
    Apr 13, 2010 at 08:08 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded