We explain here a dynamic way to filter specific levels of a hierarchy. This provides a better way to filter & visualize hierarchies.
This tutorial explains how to calculate the level of a list in a hierarchy in order to apply specific calculations (custom summary) or filters by level.
In this example we have an organization hierarchy of 4 levels (Org L1 to Org L4). For each item in the hierarchy we want to calculate a filtering module value that returns the associated level.
Context & Notes
This technique addresses a specific limitation within dashboards where a composite hierarchy's level cannot be selected if the list is synchronized to multiple module objects on the dashboard.
We show the technique of creating a static filtering module based on the levels of the composite structure.
The technique utilizes the Summary method Ratio of line items corresponding to the list levels to define the value of the filtering line items. Note that it is not a formula calculation but a use of the summary method Ratio applied to the composite hierarchy.
We defined in this example a 4-levels list as follows:
Defining the level of each list
In order to calculate the level of each item in the lists, we need to create a module that calculates it by:
Creating as many line items as level of hierarchy + one technical line item.
Changing the settings in the blueprint of those line items according to the following table:
Summary method Setting Ratio
Technical line item
Level or L4 (lowest level)
L3 / Technical
L2 / Technical
L1 / Technical
L1 / Technical
When applying these settings, the calculation module looks like this:
*Note that the Technical line item Summary method is using Formula, Minimum Summary methold can be used but will return an error when a level of the hierarchy does not have any children and the level calculated is blank.
We can now use the line item at the lowest level (“Level (or L4)” in the example) as the basis of filters or calculations.
Applying a filter on specific levels in case of synchronization
When synchronization is enabled, the option “Select levels to show” is not available. Instead, a filter based on the level calculated can be used to show only specific levels.
In the example, we apply a filter on the level 4 and 1:
This gives the following result: