Hierarchy Tab in the Subset Editor

Related article: Subsets Overview

To demonstrate the possibilities of this tab, we select in General tab the database Biker and the dimension Customers. To set options in the Hierarchy tab, you must first add a hierarchy filter.

Then we select in the first pulldown menu (1) the option Hierarchically and in the second pulldown menu (2) the consolidated element West:

In the third pulldown menu (3) you can select:

Below Returns the selected element and all elements below it.
Above Returns the selected element and all elements above it.
Siblings Returns the selected element and all its siblings.

If you check Exclusive, then the selected element itself will not be included in the Subset.

In the 4th pulldown menu (4) you can filter by type: the options are Base elements, Consolidated, or None.

Furthermore, you can filter elements by level. In this case, you can set the "Start level" and the "End level" for your filtering, using absolute levels in the dimension or the relative level starting with the selected element.

The numbering of the absolute levels depends on the Enumeration setting in General tab.

 

The selection Cyclic list (1) repeats its elements for the arguments Below and Above until the defined List length (5) is reached. The behavior for the argument Siblings is different, see the description of the argument Siblings below.

With the selection of Cyclic list (1)and [All Elements] (2) you can opt in third pulldown menu (3) Below or Above. In (5) you can set the List length:

Below Returns all base elements up to "List length" elements in a cyclic list.
Above Returns all elements up to "List length" elements in a cyclic list.
List length A number up to 100.

 

With the selection of Cyclic list (1) and an element, in our case West (2) you can opt:

Below Returns all siblings with its descendants in alphabetical order up to "List length" elements in a cyclic list.
Above Returns all siblings with its parents, starting with highest level and in in alphabetical order, up to "List length" elements in a cyclic list.
Siblings Returns once all siblings without considering of the field "List length".
List length A number up to 100.

 

Notes:

  • In parallel hierarchies, if a selected element is not specified with its path, the hierarchy filter is carried out for all parallel branches. Adding the path to an element is not yet implemented in the Jedox Web Subset Editor. But in the PALO.HFILTER section of a formula you can manually specify the path of an element.
    Example: =PALO.Subset("localhost/Biker","Customers",1,,PALO.HFILTER("All Customers\South\Spain",1,FALSE,,,,,,,),,,,,PALO.SORT(1,0,,0,,0,1,,))

  • If a dimension uses parallel hierarchies, and the element specified in an HFilter Subset is in several hierarchies of this dimension, the result for some filter settings can be ambiguous. In this case, you can further restrict the filter by specifying the full element path in the Subset formula. For example, instead of:
    PALO.HFILTER("First Product group",...)
    You can use:
    PALO.HFILTER("All product lines\Product Line by Region\First Product group",...)

  • If a specific element is defined in the HFilter, (e.g., top return its descendants), the Subset will only return elements connected to this specific element if the current user has at least read access to that element. If the user has no access ("N"), and the "Hide Elements" option is enabled for the database, the Subset will instead return the first accessible element for this user. In the older versions of Jedox, an error was returned in this scenario.

  • When selecting a specific element to return children, descendant elements, or ancestor elements for the Hierarchy Filter, note that the result will only include this hierarchy branch once, even if it exists in multiple aggregations in the dimension. This behavior is independent of the setting for the "Show duplicates" option for the Subset.

Updated December 9, 2024