Combining Groups & Hierarchies for custom drilldown lists.

This is a simple method that expands the ability to expand from summary data to detail level within the same worksheet. This was shown in Orange Belt training, but it’s worth reviewing again.

Example 1: Group with Hierarchy Drilldown.

Create a Salesperson group (i.e., Territory) with a hierarchy drilldown to customer, invoice#/date, item.

  1. Create a group based on Salesperson. In this example we are grouping salespeople into territories: Western, Central, Eastern, with an additional group for No Salesperson.

  2. Create a hierarchy with the Salesperson Group as the highest level.

    1. The Salesperson is the next hierarchy level. Create a duplicate of the original field and add a dash (“-”) to the end of the name. Since a specific field name can only be in one hierarchy, creating a copy leaves the original field in its current hierarchy relationship.
      There was nothing special about adding a dash. You can use whatever works for you to differentiate the field name.

    2. Make copies of the Customer, Inv No, Invoice Date, and Item fields to add to the hierarchy.
      Since the relationship between Inv No and Inv Date is a one-to-one relationship, you can save a step by making the copy of the Inv No field include the Inv Date in the calculation by converting the Inv Date field to a String value and concatenating it to the Inv No field.

    3. The end result is a hierarchy going from Salesperson Group to Item.

  3. When displaying the Salesperson Groups, the default sort is alphabetical.
    To show in the order: Western, Central, Eastern, No Salesperson, use the Manual Sort feature

  4. To show totals for the higher hierarchy levels when drilling down, Add Subtotals.
    The Subtotal disappear as you collapse the hierarchy

Example 2: Hierarchy that Collapses to the Grand Total

Create a Hierarchy with the Grand Total as the highest level.
This example is Summary, Customer, Inv No & Date, Item.
01 Worksheet Design Tips: Example 01b Summary & Hierarchy - DataSelf Analytics

The trick with this idea is to create a calculated field with a constant value as the highest level in the hierarchy. Since all records have the same value, they collapse to one row. The total is the same as the grand total.

  1. Create a calculated field with a constant value in it. The example below shows the string value of
    + to Expand”. You can make the string value whatever you want to describe its purpose.

  2. Create the hierarchy as described above. If needed, duplicate fields and assign modified names to avoid the field duplication issue.


The result is that when the hierarchy is collapsed to its highest level, you only see one row with the grand total.

In the example below, the next hierarchy level to customer expands to many more rows, which might distract from the overall view.


A variation on the examples above is to add the Summary calculated field as the top level in the Salesperson Group Hierarchy.

Summary hierarchy with Salesperson Territory group

Think of other ways you might want to group data with the ability to drill down:
Ship To State, Product type, Customer type, Price Plan type