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