Selecting Hierarchy Columns in Calculated Columns with Case Statements (OAC DV + Essbase)

Ajinkya Vyawahare

Environment:

• Oracle Analytics Cloud (OAC) Data Visualization
• Source System: Essbase EPM
• Issue: Dynamic hierarchy column selection in calculated columns

I am trying to create a calculated column that dynamically selects different hierarchy columns based on a parameter value using a CASE statement. While the hierarchy members exist and work perfectly as canvas filters, I cannot reference them in calculated column expressions.

Questions:

1. Is there a specific syntax for referencing Essbase hierarchy members in OAC calculated columns?
2. Are there limitations on using hierarchy dimensions in calculated column expressions vs. filters?
3. What's the correct way to dynamically select between different hierarchy levels using parameters?
4. Are there alternative approaches to achieve dynamic hierarchy column selection in OAC DV?

Additional Context:

• The same hierarchy members work perfectly when used directly in visualizations or as filters
• This suggests the issue is specifically with how calculated columns handle hierarchy member references
• I need this functionality to create dynamic reports where users can switch between different organizational hierarchy levels

Any guidance on proper syntax or alternative approaches would be greatly appreciated!

Ajinkya Vyawahare

Can someone please take a look and help?

GayathriAnand-Oracle

Hi

Not sure what error you are seeing but when I tested I get Nonexistent Column error when I try to use a hierarchy column in a calculation. Looks like this is not yet a supported use case.

Thanks

Gayathri

SteveF-Oracle

To add, potentially selection steps feature may help with your use case.

Ajinkya Vyawahare

Hi @GayathriAnand-Oracle

It's the same error that I am getting. Non-Existent column.

Seems true that OAC DV currently does not support Hierarchy selection using Parameter.

Hope they work on this and it should be added as another feature

Ajinkya Vyawahare

Hi SteveF-Oracle

It won't work that way. User wants Hierarchy as Table to filter another pivot. He does not want drop down.

I want to pass parameter from the landing page to filter hierarchy depends on user selection.