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:
Category → Sub-Category
Sub-Category → Region
Region → Segment
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.
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.
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.
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
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:
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.
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:
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
The following image shows the sales by category i.e. the highest level of detail
Final Dashboard showing Multi-Level Drill-down & up using parameter action
Conclusion:
The application of the Parameter Actions concept eases the illustration of complex Drill Down & Up in Tableau.