Let me start by saying this: I am not an MDX expert, but I know enough and am stubborn enough to figure out what I need. This blog post will be short…
I have had a medium level of experience with MDX. And by medium I mean member formulas only in Essbase…not in traditional databases. I had a requirement to take a currency member formula in BSO to ASO. AKA: Complete rewrite. It took me a bit of time to figure out how to do this as you [apparently?!] can’t have nested CASE statements.
My formula was this: the currency rates are driven off the USD value which is calculated from the Local rate. So, the main thing I need to calculate the USD value from Local so that the other currencies can be calculated correctly. These rates are driven based on the Scenario. Nothing out of the ordinary.
A sample of my BSO member formula for USD is:
IF(@ISMBR(“Actual”))
IF(@ISUDA(“Entity”, “USD”))
“Local”;
ELSEIF(@ISUDA(“Entity”, “CAD”))
“Local” * “CADRate”->”Entity_None”->”NoProduct”->”EOMRate”->”Actual”;
ETC…
ENDIF
ELSEIF(@ISMBR(“Budget”))
ETC…
The same ASO member formula for USD would be:
CASE WHEN (IsUDA([Entity].CurrentMember, “USD”) AND ([Scenario].CurrentMember IS [Actual])) THEN [Local]
WHEN (IsUDA([Entity].CurrentMember, “CAD”) AND ([Scenario].CurrentMember IS [ActualLoad])) THEN [Local] * ([CADRate], [Entity_None], [NoProduct], [EOMRate], [Actual])
ETC…
WHEN (IsUDA([Entity].CurrentMember, “CAD”) AND ([Scenario].CurrentMember IS [Budget])) THEN [Local] * ([CADRate], [Entity_None], [NoProduct], [EOMRate], [Budget])
ETC…
END
Hopefully this helps you!
