Dimension Member Formula

[Former Member]

Hello Friends,

Need to create a dimension member formula :

Dimension : BOOK – entity type

Dimension : ACCOUNT  - Account type

Scenario :  create a  ACCOUNT dim member formula effecting only members exists in Entity dimension PARENTH3, with property MGMT_CALC = Calc. 

I am trying below syntax : but not working.

IIF([BOOK.PARENTH3].CURRENTMEMBER.PROPERTIES("MGMT_CALC")="CALC", (IIF ([PNC_REV]=0,0,[ACCOUNT].[PNC_UUND_TOT]/[ACCOUNT].[PNC_REV])),NULL); SOLVE_ORDER=10

In case I am not reading hierarchy   as in below syntax, it works fine.

IIF([BOOK].CURRENTMEMBER.PROPERTIES("MGMT_CALC")="Calc", (IIF ([PNC_REV]=0,0,[ACCOUNT].[PNC_UUND_TOT]/[ACCOUNT].[PNC_REV])),NULL); SOLVE_ORDER=10

The requirement is to ready H3 members with specific property value.

Regards,

Rajesh. K

[former_member186338 · Accepted solution]

Hi Rajesh,

I don't think you will manage to achieve the result trying to check hierarchy... Create a separate property and fill it for all members of the required hierarchy.

B.R. Vadim

[Former Member]

Hi Rajesh,

You cannot read a hierarchy in dimension formula.

I would like to highlight one point, here. Your formula is based on a condition which based on the value of a property - MGMT_CALC. What I dont understand is that why do you want to read the hierarchy? Property value of a member will remain the same irrespective of which hierarchy it belongs to.

Hope this helps.

[former_member186338]

Hi Nilanjan,

Glad to hear you!

Vadim

[Former Member]

Thanks Vadim.

Its really been a long long time.

[Former Member]

Thanks Nilanjan,

We  need to read hierarchy in case of below :

Entity Dimension : Total members : 300

H1  members : 175

H2  : 150

H3  : 100

Some of them members are part of  all 3 hierarchies.

250 members are assigned with value  “ Calc”  in property MGMT_CALC.

Formula should check as below :

Check member is part of Hierarchy first,  then check that member  having “Calc” for property MGMT_CALC then go ahead and do the calculation for ACCOUNT Dimension for those filtered members.

Looking for your expert advice...

Regards,

Rajesh. K.

[former_member186338]

Create a new property HIR3 and fill it with Y for all members in H3, then use check for this property in the member formula. No better solution.

Vadim

[Former Member]

Thanks Vadim

Regards,

Rajesh.K