Skip to Content
0
Former Member
Sep 02, 2009 at 09:15 AM

MDX Formula Error. 3 Hierarchy dimension

65 Views

Hello experts!

We have some problems with MDX Formula. We are working in SAP BPC MS 7 SP4 and MS SQL 2005.

We need to create a formula using members of the same dimension, however dimension has 3 hierarchies and the members in formula included exists on the 3 hierarchies of the dimension.

We are trying to use the example propose by SAP:

#CF_ST_FIN_RATE= ((#IFRSTRATE/#WKSYR)*#WKSCURMTH)

Where IFRSTRATE, WKSYR and WKSCURMTH are base members of hierarchy H1 of Account dimension, and CF_ST_FIN_RATE is a dimension formula in H1 of account dimension.

The correct formula for H1 is

IF(Account.H2.CurrentMember is Account.H2.(All Account.H2) And

Account.H3.CurrentMember is Account.H3.(All Account.H3),

+ (Account.H1.IFRSTRATE/Account.H1.WKSYR)*Account.H1.WKSCURMTH, NULL)+

An example of this formula for H2:

IIF(Account.H1.CurrentMember is Account.H1.(All Account.H1) And

Account.H3.CurrentMember is Account.H3.(All Account.H3), (Account.H2.IFRSTRATE/Account.H2.WKSYR)*Account.H2.WKSCURMTH, NULL)

But when dimension is processed following error appears:

Parser: The end of the INPUT was reached

Our dimension Name is "Cuenta" and the formula we desire to introduce is simple:

IIF(Cuenta.H2.CurrentMember is Cuenta.H2.[All Cuenta.H2] And Cuenta.H3.CurrentMember is Cuenta.H3.[All Cuenta.H3],Cuenta.H1.A2221000*Cuenta.H1.A2221000, NULL)

Could you help us? Does anyone have experience using formula property on multiple hierarchy dimension?

Thank you very much

Regards

Gabriel