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(@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!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s