Pasting Subsets

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:

paste_ss1

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.

paste_ss2

Three Columns

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

paste_ss3

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:

paste_ss4

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 :

paste_ss5

Four Columns (3 rows)

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

paste_ss6

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s