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 Type

Current Value

DD Parameter1

String

Empty

DD Parameter2

String

Empty

DD Parameter3

String

Empty

DD Parameter4

String

Empty

DD Parameter5

String

Empty

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 Name

Calculated field

DD Dim1

[Category]

DD Dim2

IIF([Drill down level] >= 2, [Sub-Category], “”)

DD Dim3

IIF([Drill down level] >= 3, [Region], “”)

DD Dim4

IIF([Drill down level] >= 4, [Segment], “”)

DD Dim5

IIF([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 Action

Target parameter

Field/Value

Aggregation

ParameterAction2

DD Parameter2

DD Dim2

None

ParameterAction3

DD Parameter3

DD Dim3

None

ParameterAction4

DD Parameter4

DD Dim4

None

ParameterAction5

DD Parameter5

​DD Dim5

None

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 Name

Calculated Field

BackL1

IIF([Drill down level] > 1, “« Back to” + CHAR(10) + ” CATEGORIES”, “”)

BackL2

IIF([Drill down level] > 2, “« Back to” + CHAR(10) + ” SUB-CATEGORIES”, “”)

BackL3

IIF([Drill down level] > 3, “« Back to” + CHAR(10) + ” REGIONS”, “”)

BackL4​

IIF([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 Action

Sheet

Target Parameter

Field/Value

Aggregation

BackAction2

Back2

DD Parameter2

Blank

None

BackAction3

Back3

DD Parameter3

Blank

None

BackAction4

Back4

DD Parameter4

Blank

None

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.

633 views0 comments

Recent Posts

See All