Retrieve the 2nd level parent of the base level member

[Former Member]

Hi,

I want to get the 2nd level parent of the base level member.

Ex:

All_ACCOUNT

-->Balancesheet Account

  ----> asset

      -->Fixed asset

         ---> Ac_FA1

        ----> Ac_FA2

        ----->AC_FA3

AC_FA1 ,AC_FA1,AC_FA2,AC_FA3, These are base level of the All_Account. By using these accounts i have to get the Balancesheet Account as output.

I tried  epm member property using "H1" but it showing the immediate parent.

Can anyone help me

[Shrikant_Jadhav · Accepted solution]

Hi Surya,

Use nested formula

=EPMMemebrProperty(,EPMMemebrProperty(,"Memebr","PARENTH1"),"PARENTH1"),

Shrikant

[Former Member · Accepted solution]

Hi,

Thxs for your input.

But we don't know how many ascendants  child have, it could be change by child to child.

Can you suggest dynamic.

[Shrikant_Jadhav · Accepted solution]

Hi Surya,

Can you explain the business case and screen shot of required template ?

Shrikant

[Former Member]

Hi Surya,

Take look at below screen shot.

[Former Member]

HI,

I need it dynamically...

can you explain 

[Former Member]

Hi Surya,

Above screen shot is just for your understanding. In my account dimension i have five level.

Simply populate your member in one cell of which you want parent and use that cell in your formula.

In my case in B2 cell i have populated one member and i have used that cell in formula.

i have used level like 1,2,...5. it will populate the required parent level member.

Try this one.

=EPMMEMBEROFFSET("",B2,,2)

Explain the business required so we can assist you.

[Former Member]

Hi,

I arrived by manually by empmemberproperty.

Thxs for you support.

[Former Member]

HI Surya,

Instead of member property try to use member offset function specify the level in that function.