Below you can see a base Subset. If no sorting filter is specified, elements will be displayed in the order of definition (creation). Take a look at the structure of the function in the formula bar. There you can see the base filter that is the pure Subset part: =PALO.SUBSET(Server/Database,Dimension,Indent,Alias). In this example, the "Alias" argument is left empty.
Indent
If you want to display the indent information, then you must extend the array function over three columns:
With indent 1 (or empty) and without other functions, the indent information of the elements is 0 for all elements.
Depending on the argument, four different indents could be displayed. For the filter displays themselves, the indent argument only takes effect if the hierarchy level has been specified in a Subset filter.
Indent 1:
The indent information of the elements is 0 for all elements with Indent 1. Indent 1 needs PALO.SORT() or PALO.HFILTER() to display the hierarchical numbers. The hierarchical numbers 1, 2, or 3 will be displayed; for example, in combination with PALO.SORT(), the first argument is 1 or 2.
In general, and in the written case, the elements of the highest hierarchy level get the number 1, the second-highest level gets number 2, and so on.
Indent 2:
With indent 2, the hierarchy numbering of the elements is 0,1, 2.
In general, elements in the lowest hierarchy level (base elements) get the number 0. The number is incremented by 1 for every step up in the hierarchy.
Indent 3:
With indent 3, the hierarchy numbering of the elements is 2, 1, 0.
In general, the elements of the highest hierarchy level get the number 0, the second highest level gets the number 1, and so on.
Indent 4:
The Dynamic indent option dynamically adjusts the indent of the elements in the Subset based on the overall Subset results. It also allows hierarchies to appear correctly when users have different levels of access rights in a dimension.
All elements for which no parent element is found will get indent number 1, regardless of their position level in the dimension. Elements for which at least one parent element is found in the Subset will get an indent number calculated from their parent's indent in the result (incremented by 1).
For Dimension "Months" of database "Demo", indent 4 has the same result as indent 1 in the screenshot below, because there are no dynamic changes in this dimension.
Note that when using this option on dimensions that use parallel hierarchies, the result can be ambiguous. In this case, the full paths of elements in the Subset can be optionally included in the Subset result to achieve a more consistent ordering.
As shown in the screenshot below, without the Element path option, "Dec" appears to be a child element of "Sep". With the Element path option, it is properly recognized as a non-child element and placed at root level. See the description of PALO.SORT() function for details.
Notes:
- The Subset for indent 1 is shortened. Due to the sorting of PALO.SORT(1), all hierarchical numbers are displayed in the first three lines .
- Indent 2 and indent 3 do not require a combination with other functions to display the hierarchical numbers.
- Using the Lowest level is 0 (reverted indent) in Subset as the source for a ComboBox, DynaRange, or List might result in an unexpected hierarchy order.
Updated March 27, 2024