How To: Nested BSO If Statement in ASO MDX

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(@ISUDA(“Entity”, “USD”))
     ELSEIF(@ISUDA(“Entity”, “CAD”))
          “Local” *  “CADRate”->”Entity_None”->”NoProduct”->”EOMRate”->”Actual”;

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])
 WHEN (IsUDA([Entity].CurrentMember, “CAD”) AND ([Scenario].CurrentMember IS [Budget])) THEN [Local] * ([CADRate], [Entity_None], [NoProduct], [EOMRate], [Budget])

Hopefully this helps you!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s