Sustaining in detail the numbers of a presentation or the conclusions of a study is of vital importance, since with this a greater understanding, credibility and confidence about the work carried out is achieved. Perhaps this is why I have always been fascinated by the different techniques that Tableau can use to perform Drill Down & Drill Up exercises.

  • Drill Down: It allows us to “dive” from a visualized information, that is, to go from the general to the specific in order to know the detail of the data or the origin that makes up a summary.
  • Drill Up: It allows us to return to the higher levels of a hierarchy, that is, to go from the specific to the general, allowing us to return either to the next higher level or to jump several levels backwards.

For creating Drill Down & Up menus, Parameter Actions are one of my favorite Tableau tools. Parameter Actions are available as of Tableau 2019.2 and immediately after its release it was common to see them used in multiple Drill Down exercises. However, the first exercises I saw implied that for each level that was added to the hierarchy, the complexity in the definition and number of formulas and parameters increased, as well as the number of sheets required to present the menu options by Drill Up.

Then in early 2020 I came across a community post where a person who had done a beautiful Drill Down & Up exercise commented that she didn’t think a single sheet solution for the Drill Up menu was possible.

Those who know me know that I love technical challenges and when I read that message I couldn’t help but take it as a challenge, as I thought and felt that it was possible. I quickly found myself doing tests and when I found what I was looking for I got excited and soon found myself posting examples of the technique.

As the number of examples continued to grow, at the beginning of 2021, I presented a concentrate with several of the examples that I had created in order to answer various questions from the Community, but months later, I felt that an urgent Makeover was necessary in order to create new examples that will help show the potential of the technique, because up to that point, my technical self-had dominated.

Now was the time to get down to the fun of designing and looking for new examples that would offer the end user different levels of configuration:

MetricIs the metric pre-established or is the user allowed to select a metric from different options?
HierarchyA single hierarchy or multiple pre-established hierarchies?, or
Is it allowed to select a hierarchy between different options?, or
Is the user allowed to define their own hierarchy, customizing the dimensions to be used and the order between them?
Drill DownWill a Drill Down be used immersed in the main chart or in a separate menu or in both?
The order of the items presented in the menu is pre-established, or the user can select the type of order: ascending, descending or by name?
Drill UpUsing words or using shapes?
Landscape format or portrait format?
With subtotals and without subtotals?

A valuable point of this technique is that it does not require duplicating the data, nor any type of data modeling to navigate between the different levels of the hierarchy. The technique itself revolves around two parameters, which is why it is essential to understand the use that is given to each of them:

  • Level Parameter: It is used to identify the Drill Down level within the hierarchy as far as the user has deepened, that is, the current level of detail at which the hierarchy has been deepened is stored. Example: 0 for Total, 1 for Region, 2 for State, 3 for City and 4 for Postal Code.
  • Path Parameter: It is used to store each of the previous selections that the user has made to reach the current level within the data hierarchy. In other words, a trace of breadcrumbs is stored on it, which provides the user with a path to return to the starting point. Example: Total »West» California »Los Angeles» 90004

Additionally, the technique requires the definition of:

  • A Sheet for the Drill Down Menu.
  • A Sheet for the Drill Up Menu
  • Four Parameter Actions (2 for Drill Down and 2 for Drill Up)

Let’s see in graphic form the main characteristics of the technique.

A requirement of the technique is that the dimensions of the hierarchy to be worked on in the Drill Down & Up must be of type string, in case of being of another type such as postal codes that are normally used of numeric type, they must be converted to string type. Furthermore, “Null” values are not allowed, therefore, if there are null values, they must be replaced by an empty string.

Below I share the steps and formulas to work with an example of a geographic hierarchy with the following levels: 1) Region, 2) State, 3) City and 4) Postal Code.

1. CREATE PARAMETERS.

As I mentioned previously, the operation of the Drill Down & Up requires the use of two parameters. These parameters can be updated both from the Drill Down menu and from the Drill Up menu.

PARAMETERTYPECURRENT VALUE
LevelInteger0
PathStringTotal

STEP 2. CALCULATE THE DIMENSIONS THAT WILL SUPPORT US WITH THE DRILL DOWN

The Drill Down sheet shows the next level of drill options within the hierarchy. When one of the menu options is selected, both parameters must be updated:

  • If the selected option implies advancing in detail, the “Level” parameter will be increased by one its current value and the new selected option will be added to the “Path” parameter.
  • If the hierarchy levels end, that is, the Drill Down cycle ends, we must return to the total amounts, for which it is necessary to reset the initial values in both parameters.

The basic dimensions to be defined for the Drill Down menu are:

  • DD Level: To update the Level parameter. 
  • DD Label: To calculate each of the descriptions of the available options that together create the Drill Down Menu.
  • DD Path: To update the Path parameter.
  • DD Filter: To show only the data source values that meet the options previously selected by the user.
DD LevelIIF([Level] =  4,  0, [Level] + 1)
DD LabelCASE [Level]
WHEN 0 THEN [Region]
WHEN 1 THEN [State]
WHEN 2 THEN [City]
WHEN 3 THEN [Postal Code]
WHEN 4 THEN “Return Total”
END
DD PathIIF([Level]  =  4,  “Total”, [Path] + “»” + [DD Label])
DD Filter STARTSWITH(“Total»” + [Region] + “»” + [State] + “»” + [City] + “»” + [Postal Code], [Path])

As can be seen, in the definition of these dimensions, on the one hand, the values stored in the parameters participate, as well as the values contained in the data source with respect to the dimensions that make up the data hierarchy (remember that the dimensions of the hierarchy must be of type string and null values must be previously replaced by an empty string).

In the formulas, a constant with the value of 4 appears, which indicates the maximum level to which the Drill Down is to be carried out in the hierarchy and its value is intrinsically associated with the number of levels of the hierarchy that is used: 1) Region , 2) State, 3) City and 4) Postal Code.

In addition, in the formulas he used the character “»”, which serves as a separator between each breadcrumb or trace that is saved in the Path as you progress through the Drill Down.

STEP 3. CALCULATE THE DIMENSIONS FOR THE DRILL UP MENU. 

The Drill Up sheet shows the values previously selected by the user within the hierarchy and when one of the options is selected, the value of the “Level” parameter will be updated according to the level of the selected option and stored values in the “Path” parameter will be eliminated. The number of values to eliminate will depend on the level to which you want to return.

The basic dimensions to be defined for the Drill Up menu are:: 

  • DU Level: It helps us to create each of the boxes necessary to show the Drill Up menu options. In turn, this value will help us to update the Level parameter.
  • DU Path: It helps us to update the Path parameter according to the selected option.
  • DU Label: To calculate each of the descriptions of the available options that together create the Drill Up Menu.
  • DU Color: Identifies for each of the breadcrumbs or trace stored in the Path, whether it is the last value stored or not.
DU LevelASCII([City]) % ([Level] + 1)
DU PathLEFT([Path], FINDNTH([Path] + “»”, “»”, [DU Level] + 1) – 1)
DU LabelSPLIT([DU Path], “»”, -1)
DU Color[DU Level] = [Level]

The “DU Level” formula generates an arbitrary grouping of the City field in order to create the necessary values to present the Drill Up menu options, in this exercise the values from zero to four.

You can substitute the City field for any of the other fields from the data source; the only condition is that the necessary values are generated to present the Drill Up menu.

Tip: In general, selecting one of the last dimensions in the hierarchy fulfills the objective of creating the necessary values for the Drill Up, since they are usually the ones that contain a greater number of different or unique values.

The last three dimensions are created exclusively from the values stored in the parameters or from any of the other 3 dimensions of the Drill Up, that is, they do not take information from the data source, therefore, the arbitrary grouping handled in the “DU Level” does not affect its value.

STEP 4. CREATING THE DRILL DOWN MENU SHEET AND THE DRILL UP MENU SHEET

Using the fields created in steps two and three respectively.

Examples:

STEP 5. DEFINITION OF PARAMETER ACTIONS.

Create two Parameter Actions for the Drill Down menu.

PARAMETER ACTIONSOURCETARGET PARAMETERFIELD OR VALUEAGGREGATION
DD LevelDrill DownLevel DD LevelNone
DD PathDrill DownPathDD PathNone

Create two Parameter Actions for the Drill Up menu.

PARAMETER ACTIONSOURCETARGET PARAMETERFIELD OR VALUEAGGREGATION
DU LevelDrill UpLevelDU LevelNone
DU PathDrill UpPathDU PathNone

This means that with a click on one of the Drill Down Menu options or on one of the Drill Up Menu options, the Parameter Actions will update the value of both parameters and the change in the Parameter values ​​in turn, will recalculate the 8 dimensions, which generates a new version of the Drill Down and Drill Up menus according to the last user selection.

See examples: Here

If you want to know more details of the technique or more blogs about examples worked with this technique, you can consult the blog “Drill Down / Up | Basic Technique in a Multilevel Hierarchy ”, which includes links to other secondary blogs with the adjustments to consider for each of the examples of the concentrate presented to that date.

Before I finish, I want to say that this technique is my favorite, because of its versatility I have used it in multiple Drill Down & Drill Up exercises and I consider that once you understand the dynamics of its operation, you are on the other side, since its implementation is simple to carry out: 1 ) It does not require duplicating the data in Tableau to navigate between the different hierarchy levels, 2) The Drill Up options are integrated in a single sheet and 3) Regardless of the number of hierarchy levels, it only requires the definition of two parameters and eight basic calculations.

I hope the new examples that I share are useful to you and I wish you a New Year 2022 full of many blessings! A hug from Mexico!!!

Thank you very much and if you have any questions about the post, feel free to contact me on Twitter (@rosariogaunag).