CASE Statement Not Evaluating with Level-Based Hierarchy in OAC

PeteSiekierski

I want to change the behavior of certain columns in my OAC analysis that uses a ragged level-based hierarchy. What I've found, though, is that the value of a column in the hierarchy does not seem to be available consistently - or even at all - when I use it in a CASE statement.

In my situation, my accounting hierarchy has 15 levels, and when I get down to level 9, one of the parent accounts is revenue, represented by the value "REV". However, when I put the value of "Parent Account 9" into a CASE statement to do something as simple as choosing between two numeric values, I get nothing back - even when I include an ELSE clause as a catch-all.

As you can see in the example above, Parent Account 9 is "REV" (revenue) or "EXP" (expenses) when I get far enough down in my hierarchy. Notice that the column is NULL for the other rows; this is because they are at levels 8 or above in the hierarchy. The children of REV and EXP all display one of those two values in the column when I expand the node. This seems perfectly normal.

The M1 Switch column - a simple CASE statement involving the value of Parent Account 9 - evaluates to NULL in all cases, though, regardless of whether the value is "EXP", "REV", or NULL.

I would expect something - a zero or a one - but instead I get nothing. OAC doesn't give me any warnings or errors about my formula syntax; nonetheless, it is definitely not working the way I think it should.

Is there perhaps a practical workaround or trick of the syntax that I've missed?