New Upload weather data from web services into TM1/Planning Analytics — Read the post →

Using Hierarchies in SUBNM Formulas

Using Hierarchies in SUBNM Formulas

Slice supports the use of hierarchies directly inside SUBNM formulas, allowing you to select elements from an alternative hierarchy of a dimension.

SUBNM Formula Syntax with Hierarchies

The key addition is :HierarchyName appended to the dimension reference.

Step-by-Step Example

  1. Open the P&L_2 view from the General Ledger cube in the Slice Cube Viewer.

  2. Export it to Excel as a Slice.

  3. Go to cell B4 and inspect the default SUBNM formula:

  4. Modify it to reference the Currency hierarchy:

  5. Double-click the formula to open the Subset Editor — you will now see the elements of the Currency hierarchy instead of the default geographic hierarchy.

Viewing Available Hierarchies in the Cube Viewer

  1. In the Slice Cube Viewer, open the P&L_2 view.

  2. Go to View Options and toggle the View Hierarchy Panel button.

  3. All available hierarchies across all dimensions are displayed. In this example, the Region dimension has the Default and Currency hierarchies.

  4. Drag the Currency hierarchy down to the titles area.

  5. Export the view to Excel as a Slice.

Using Multiple Hierarchies in the Same Report

After exporting, you can have both the Default and Currency hierarchies for the same dimension in separate rows (e.g. cells B4 and B5). Select different elements in each hierarchy and refresh the sheet to compare results simultaneously.