Multi-level Drill-down & Up Using Parameter Actions

Authors: Nishanth Mannem & Nishu Singh

When it comes to dashboard interactivity, parameter actions open up a world of options. One example is the ability to drill down into a dimension by clicking on the view, allowing the user to explore the data in greater depth while reducing Viz cluttering. 

Drill Down Tables with Parameter Actions offer the option to visualize the analyzed dimensions’ measures and values. These values are derived from parameters rather than being found in the data set. When we click on a dimension that represents a product category, for example, we can look at the overall value of sales in that category. We can also look at the overall value of sales in a specific subcategory for a specific geographic location.

Drill Down Tables with Parameter Actions are extremely beneficial when studying many levels of data. It also keeps the dashboard structured, providing for the first time the bare minimum of information customers require to go deeper into the insights they seek. Users can visualize details only at the level they’re interested in using Drill Down with Parameter Actions.

To see how we can make this, we’re going to jump into the Sample Superstore Europe Dataset. We have a simple Bar chart with the sum of Sales across each category/ sub-category/ region/ segment/ shipping mode:

We’ll use Parameter Actions to deal with 5-level Drill Down and Drill Up in this practice. 

In this example we’ll build a simple table using five hierarchies within the Sample Superstore Europe dataset:

  1. Category → Sub-Category
  2. Sub-Category → Region
  3. Region → Segment
  4. Segment → Shipping Mode 
Step 1: Creating parameters

We will create five parameters to store five levels of the hierarchy: Category, Sub-Category,  Region,  Segment, and  Ship Mode.

Parameter Name Parameter TypeCurrent Value 
DD Parameter1StringEmpty
DD Parameter2StringEmpty
DD Parameter3StringEmpty
DD Parameter4StringEmpty
DD Parameter5StringEmpty

The parameters are initially assigned an empty string as their value, but as the user interacts with the Viz, the Parameter Actions will update the value of its associated parameter. When the parameter values are updated, the value of this field is automatically updated.

Step 2: Creating a drill-down level field

We will create a calculated field to keep track of the drill-down levels as follows and convert it into dimensions.

Fig: Calculated field for Drill down level

Step 3: Creating a new dimension for each drill-down level:

A variable is created for each hierarchy level: Category, Sub-Category, Region, Segment, and Ship Mode. Its value will depend on the level of detail used. We create five calculated fields as follows, however, the first calculated field is optional.

Calculated field NameCalculated field 
DD Dim1[Category]
DD Dim2IIF([Drill down level] >= 2, [Sub-Category], “”)
DD Dim3IIF([Drill down level] >= 3, [Region], “”)
DD Dim4IIF([Drill down level] >= 4, [Segment], “”)
DD Dim5IIF([Drill down level] >= 5, [Ship Mode], “”)
Step 4: Creating a new dimension:

Create a new dimension named Current drill down a level as follows, Its value will depend on the maximum drill down level used.

Fig: Calculated field for Current drill down level

Step 5: Creating a dimension to filter:

It is necessary to create a filter that only displays the next level of detail based on the last mark selected by the user in the visualization.

For example, if a user selects the Category = Furniture mark, the subsequent consultation should only display the Sub-Categories that correspond to the newly selected category.

Fig: Calculated field for Filter  

Step 6: Creating visualization:

Filter shelve ← “Filter” (select True)

Columns ← Sales 

Row ← Current drill down level

Color ← Drill down level

Details ← DD Dim1,  DD Dim2,  DD Dim3,  DD Dim4,  DD Dim5

Fig: Drill down & up worksheet

We create a new dimension with a null string which can be useful to clear the value in the parameters. Next, we create a dashboard with a newly created worksheet.

Step 7: Defining the parameters actions

We create a Parameter Action for each level of Drill Down. Parameter action can be added from the Dashboard tab by clicking the Actions option and then selecting the Change Parameter Action. We will create five parameters actions as we have five drill-down levels.

We create the first parameter action “ParameterAction1” for the Drill-down sheet as follows:

Fig: Parameter action for drilling down

Similarly, we create other four-parameter actions using the values mentioned in the table below. 

Parameter ActionTarget parameterField/ValueAggregation
ParameterAction2DD Parameter2DD Dim2None
ParameterAction3DD Parameter3DD Dim3None
ParameterAction4DD Parameter4DD Dim4None
ParameterAction5DD Parameter5DD Dim5None
Step 8: Creating drill up buttons:

We will create variables for drilling up, its value will depend on the maximum value of the drill-down level. This will help us to drill up upon a button click

Field NameCalculated Field 
BackL1IIF([Drill down level] > 1, “« Back to” + CHAR(10) + ”   CATEGORIES”, “”)
BackL2IIF([Drill down level] > 2, “« Back to” + CHAR(10) +  ”  SUB-CATEGORIES”, “”)
BackL3IIF([Drill down level]  > 3, “« Back to” + CHAR(10) + ”   REGIONS”, “”)
BackL4IIF([Drill down level] > 4, “« Back to” + CHAR(10) + ”   SEGMENTS”, “”)

We will create sheets for each level of drill-up and name them as “Back1”, “Back2”, “Back3’, “back4”.Next, we add all these sheets on the dashboard as shown below to create a menu to drill up. These options will act as buttons and will help us to drill up the levels of hierarchy. 

Fig: Menu option for drilling up

Step 9: Parameter actions for Drill up

We will create a parameter action to clean the value of parameters for each drill-up level. Hence we create four-parameter actions.

We create the first parameter action “BackAction1” for the Back1 sheet as follows: 

Fig: Parameter action for drilling up

Similarly we create other three parameter actions using the values below

Parameter ActionSheetTarget ParameterField/ValueAggregation
BackAction2Back2DD Parameter2BlankNone
BackAction3Back3DD Parameter3BlankNone
BackAction4Back4DD Parameter4BlankNone

The following image shows the sales by shipping mode i.e. the lowest level of detail 

Fig: Dashboard showing sales by shipping mode

The following image shows the sales by category i.e. the highest level of detail 

Fig: Dashboard showing sales by category

Final Dashboard showing Multi-Level Drill-down & up using parameter action

Fig: Dashboard showing Drill Down & up feature

Conclusion:

The application of the Parameter Actions concept eases the illustration of complex Drill Down & Up in Tableau.

We use cookies on this site to enhance your user experience. For a complete overview of how we use cookies, please see our privacy policy.