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:

Four Columns (2 rows)

Now the process starts again, but this time vertically. By pasting 4 columns by 2 rows , you get the element name and the alias :

Four Columns (3 rows)

Finally we have completely trans-positioned the data with the indent now included:

To see how it actually works, try it yourself, or download it and check it out here