Creating Drill-Down Tables with Parameter Actions

In a recent visualisation looking at global remittance payments I used a large table that you could click into to change the level of detail on either the row or column, or both at the same time.

This drill-down functionality allowed all regions to be shown in the table at one time and also to be able to see country level details for a selected region, thus enabling a comparison of values for visible countries to values for surrounding regions. To remove the country level of detail, a custom button can be clicked which reverts the view to its original settings:

How to build a drill-down table

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

1. Country > State
2. Category > Sub-Category

Create parameters

Two text parameters are required

• One to hold the selected Country
• One to hold the selected Category

Actually, the parameters will hold uppercase versions of the selected values to help differentiate the hierarchy in the dashboard. For this reason you will see these values wrapped in UPPER() calculations throughout the build.

Create drill-down calculations

We need the fields on the Rows and Columns shelves to be dynamic to allow them to show either level 1 or level 2 of each hierarchy. To enable this, calculations are used. The following calculation is placed on Rows:

IF UPPER([Country])=[p.Country]
THEN "  ⮡  " + [State]
ELSE UPPER([Country])
END

If the Country is selected, i.e. Its value is stored in the p.Country parameter, then show the State value. Otherwise show the Country in uppercase.

The following similar calculation is used to show either Category or Sub-Category and is placed on Columns:

IF UPPER([Category])=[p.Category]
THEN "     ⮡  " + [Sub-Category]
ELSE UPPER([Category])
END

Build the view

Below is the view using the above calculations on Rows and Columns, with SUM(Sales) on the Label and Colour shelves:

Note that Country and Category (both inside UPPER() calcs) are also on detail. This is required as they need to be in the view to be passed to the parameters in the next step

Create Parameter Actions

To pass the selected Country and Category to their respective parameters, two parameter actions are needed

1. Selected Category – to pass the selected Category to p.Category
2. Selected Country – to pass the selected Country to p.Country

The first of these is shown below:

And the second:

Drill down in action

Here is the view with the drill-down working. You can see as I click on a header on in a cell, the respective level 1 values in each hierarchy expands to show the level 2 values below:

Adding selected headers

One issue with the above view is that because only level 2 values are shown for the selected Country and Category hierarchy, we can’t see which level 1 value they sit under.

To solve this, we can add two text boxes in the top left that contain the values held within the parameters. Here is the Country header text box:

A similar one is created for Category, where the text is formatted with a Direction of Up, as shown below:

These are then put inside a container and added to the top left corner of the table. You can see them being updated in the below animation as I click on different cells in the table:

Remove level 2 values

Next, we need a way to revert the table to its original view, only showing level 1 values in both hierarchies. To do this we need to clear the values from both parameters, for which I created a simple view using a dummy Blank calculation that contains an empty string “”.

This is then added to the container in the top left and when I click this “button” the parameters are cleared:

Make the button disappear

A final, optional step is to make the button disappear when only level 1 values are shown, as clicking the button in this scenario would not do anything. This is more of a UX enhancement and is purely optional.

To do this, a filter is added to the Button view that returns TRUE if either of the parameters contains a values. If both parameters are empty, then FALSE is returned and the view will not show anything:

This calculation is added to the Filter shelf and set to TRUE. So now, when the view first loads, or after clicking the button, the button is hidden from view:

Download the workbook

I hope you found this post useful. The workbook with the above completed example can be downloaded from my Tableau Public profile here.

If you’re interested in creating more custom UI features, have a look these earlier blog posts here and here.

Thanks!
Marc

• Twitter
• LinkedIn
• Link
• YouTube

Latest YouTube video
This video tutorial steps through how to create a dot plot in Tableau.