Drill into hierarchies using groups and a handy script

Manage hierarchies with group-on-import + helpful script

At our Dimension 2021 conference we showed you one way you can present hierarchical data in your model. Below are the steps for using Quantrix’s group on import functionality (which comes out of the box) and a powerful, customizable script that will handle some of the manual setup for you. Note that the script isn’t necessary to accomplish the end result, but it will make the process much faster if you have a large, multi-level hierarchy.

Step 1: Start with good data

It’s important that your hierarchy is defined in a 2D table, which we’ll call a hierarchy table. This table should contain all of the items at each level of the hierarchy and how they relate to each other (see our sample data set).

Using Data Import, bring in your hierarchy table and drop the lowest level of the hierarchy onto the row or column tray to form a category in the configuration matrix. Then, in ascending order, drop the remaining levels of the hierarchy directly on top of the items in the existing category (do not create a separate category for each level). Notice that Quantrix groups these automatically for you based on your hierarchy table. You can then finish bringing in the rest of the data you want for your matrix and complete the import.

Step 2: Import the script file (.qsl)

Save and load the script into the script library in your model (will post in the comments; check out the online help for instructions: Using Libraries). It’s best to open another script first and turn on Expert Mode if you are not already working from there. Then, open the summarization script.

In lines 5 and 6, change the name of the matrix and the category to match the data import matrix from Step 1.

Run the script (Control + Enter or from the Scripting menu). Voila! The script does the following:

• Adds a Total item to each group, at each level, of the hierarchy category
• Adds the related sum(summary()) formulae for these items
• Creates a new matrix (Filter [Category Name]) that will be used to display drill down & roll up functionality on a canvas, with necessary formulae to manage the relationships between the hierarchy levels and totals

Step 3: Complete the setup

Right-click the Input item in the script-generated Filter Matrix and constrain the input to two values in a list: Summary and Details (note: if you prefer different terms, be sure to also change them in the script and rerun it to update the filter formulas).

Create a canvas and drop the Filter Matrix and the data import matrix. Snap them together horizontally, with the Filter Matrix on the left.

Apply a filter to the top Total item with an expression: @Values = “Input”

Apply a filter to the Input item in the column: Filtered

You now have a working hierarchy view of your data that enables you to drill down or roll up at any level of the matrix.

A few notes:

You can also write some very basic scripts to roll everything up or drill down the full hierarchy. Check out our recorded session on Scripting to see an example. (We’ll provide the links to the recording on this thread when they become available)

If your data set is particularly large, you may need to adjust your timeout settings under Tools > Options > Scripting.

if your structure changes (updating the import adds or removes any groups), be sure to rerun the script (and delete any formulas that calculate missing Total items). This step is unnecessary when items are added or removed.