Subset | the naked leaf

The Hierarchy filter is a major subset type that is usually used in Jedox Web. The Hierarchy filters elements in a dimension based upon definitions around a hierarchy.

As the meta-data (dimension structures) change in your system, the changes will be reflected wherever you use the subset. Download the sample file here.

There are 2 examples in this file. On tab 1, there is a straightforward hierarchy filter. A Hierarchy filter allows you to filter the subset list based on hierarchical arguments. For example, you can specify to only return elements at a certain level, or elements under a certain parent. Hierarchy Filters are fantastic for when you want to display a particular tree of a specific hierarchy on a report:

On the first tab of the examples, I have basically exposed all the variables on the h-filter. Play around and see how they work. You will notice that some of the variables are values in @Value format. These are Jedox Web variables. They are user and session related variables that you can populate and share between reports. More on those later.

The second tab illustrates the flexibility of Subsets and Dynaranges. The ‘Show Parents Beneath Children” is manually set on the General tab, but it is not a variable option. You can, however, set a flag in the subset formula that gets generated.

What we are doing here is generating the base subset in the Subset Editor, and then changing the DynaRange type to Formula. We can now modify the formula manually to allow the user to toggle between showing parents above and below the rows:

The finished report now allows the user to toggle between having totals at the bottom at the top of the row elements:

Thomas Arampatzis from the Jedox Academy was in town last week for some training. An interesting point that he touched upon with the students in relation to subsets was how the subset array works. Depending on the orientation and dimensionality of the array, you can retrieve different information from the subset. Below is an example of a simple subset using a hierarchy filter.

Single Column

If you paste out a single column, you return the element name:

Two Columns

If you paste out 2 columns into the spreadsheet, you get the alias as well. Helpful if you want to show an element code plus description in a report.

Three Columns

..will give you the hierarchy enumeration (in this case, indent)

This is very handy if you want to test the row for conditional formatting. You can bold and underline totals, for example.

Four Columns (1 row)

An interesting thing happens when you paste out across 4 columns. The Subset array automatically ‘pivots’ and now shows the levels across the columns: