Hierarchy Tab in the Subset Editor

Related article: Subsets Overview

To select options in the Hierarchy tab, you must first activate the Hierarchy Filter by marking the checkbox in the upper-right corner of the tab window (1).

To demonstrate the possibilities of this tab we use the dimension Customers of the database Biker. Then we select the option „Filter elements hierarchically“ (2) and in the pulldown menu (3) the consolidated element "West":

After the selection of an element, in our case „West“, 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.
Exclusive The selected element itself will not be included in the subset.

Furthermore, you can check the options "Filter elements by type" - which enables the selection of "Consolidated" or "Base elements" - and "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 hierarchy enumeration set in General tab.

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

With the selection of Cyclic list and [All Elements] you can opt:

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 99.

 

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

Below (5) Returns all siblings with its descendants in alphabetical order up to "List length" elements in a cyclic list.
Above (6) 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 (7) Returns once all siblings without considering of the field "List length".
List length(8)

A number up to 99.

Example: In the screenshot above is list length = 12 and the result is shown in the preview area (9) of the screenshot (repeats the result for Above up to 12 elements).

 

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 June 5, 2023