Tableau: Export all data? Do this instead.
One of the first questions that I receive from almost all of my customers when gathering requirements is, “Can I export all the data so that I can see…?” I feel like it’s a poor reflection on a report when users just want to export the data and do something else, so my goal is to get them to say, “Oh, it’s okay, we don’t need all the data, then.” In my opinion, it’s a very rare occurrence that a user should need to export data to derive their own analysis— dashboards should be flexible enough to answer the majority of user questions..
If you’ve built one chart, then it’s only a couple of steps to multiply that up to many charts, simply by using a parameter to switch between how a bar is stacked, or what is displayed on the x-axis. Using this functionality, you can give the user the ability to drill down through any combination of dimensions, to get a lower-level breakdown without having to scroll through pages of a table in Tableau that just shows everything. This is particularly applicable in situations where users might want to look at dimensions that are highly granular, like customer names or order IDs. I work in the airline industry and we usually have a lot of dimensions — it’s been a challenge moving users away from having upwards of 20 filters on screen to using chart and map driven filtering, but I’m getting there. What I’m going to describe looks a little like this:
The orange cells represent the three choices made by the user for the header of each column. Clicking on the columns in turn will reveal the corresponding second column values for that row, e.g. clicking on IT will reveal the four cabins and their totals. Subsequently clicking on J (just the abbreviation for the business cabin) will reveal the IT Corporates that have booked in J cabin. Since the percentage is a table calculation, it could easily be displayed as the overall percentage, or the percentage of the selected level, e.g. Apple making up 47% of business class bookings in the IT sector. The table can also be exported to Excel as it is currently displayed, without the need to create additional pivot tables.
Using this method allows users to ask questions of the data in a more intuitive way. For example:
- Which industries are booking the most seats in J cabin, and which of the corporates in that industry are the largest? or
- What is the distribution of this local chain’s bookings by channel, and is it worth renewing their deal across all POS (points of sale)?
Most of the work for this functionality is creating the parameter and CASE statement used to determine which dimensions should be used according to a user selection from a parameter. These two things should look something like this:
There are some important things to note when creating fields in this way (for any purpose, not just the drilldown):
1. All the field types must be the same. You cannot mix strings with numbers or dates. Converting any other field type to text will alter how it is sorted, e.g. “April” will come before “January”.
2. There is no inheritance from the source fields, e.g. setting default sort orders, and customising colours in legends are not carried through. Any of these customisations will also need to be set on the output field. This may involve selecting each of the dimension choices in turn, then sorting/colouring values, as only those that are currently displayed will be configurable.
In programming standards, it’s common to use an underscore at the beginning of a variable that is not intended to be exposed to the user, and it can be useful for both grouping these in the list in Tableau, and signposting what they are used for.
This one will be to store the choice the user has made by clicking on the first column. You’ll need a second parameter to store the choice from the second column. Since column 3 is the terminal column, you don’t need a parameter for that. You can now build an initial display showing the first level of your drilldown.
You should now have 5 parameters: 3 for drilldown field choices, and 2 to store the user click choices. Add all 5 of your new parameters to the display (click on the parameter and choose “Show Parameter”). Add your [DD: Drilldown Level 1 Display] field to the Rows shelf, and Measure Names to the Columns shelf. You can use any measure(s). Don’t worry about any formatting for now:
You should be able to change your [Drilldown Choice 1] parameter and have the table update to show all the entries for that dimension. You may want to sort your rows by something like descending revenue. Later, you can create another parameter to alter how the data is sorted if you want.
Next, create a new Action (go to Worksheets ->Actions), selecting “Change Parameter” as the action type. Select the Level 1 parameter to be updated, and the Level 1 Display field as the dimension with which to populate the parameter. In simple terms this means update the parameter value with whichever value the user clicks on. Check your action works by clicking on one of the values in your table. You should see [_DD: Level 1 Selection] changes with each click. If you click away from the table, then the value should be reset to whatever you chose (it doesn’t have to be -X-, just something that will never appear in your data).
As it stands, this action adds no functionality to the display, but it gives us a value to use in a new calculated field.
To determine if the second level is displayed for a given row, we need to check if the value in the first column is the value that the user clicked on (now populated in the [_DD: Level 1 Selection] parameter). If so, we return the column 2 value in each row of underlying data, otherwise leave it as NULL.
Now, if you add this column to your display, you should be able to click on your first column to expand that row according to the dimension you have selected in [Drilldown Choice 2]. Repeat the process you followed to create the first action, this time using the Level 2 display field to populate the level 2 selection parameter. Check that it works by clicking on something in the second column and making sure that your second parameter is populated with the value. It should look something like this:
For the level 3 display field, we need to be specific about what has been selected in both levels 1 and 2. If we don’t include level 1 then, if our second column is cabin, when we click on J, then the J value will be displayed for every value in the first column, rather than just the row we clicked on.
Because column 2 is always a subset of column 1, clicking on a value in column 2 does not de-select the value in the first column. One drawback in this method is that you cannot drill back up, e.g. if you click to de-select a column 2 value, you are removing your selection in the display, which means both parameters are reset and you will return to the starting point.
You can now drag this third column onto your display and, assuming your parameters and actions are working correctly, the drilldown is now fully functional. There are, however, lots of additional things that can be done to improve it. If the third column is something granular, like POS or IATA, then you are still going to get a lot of rows of data (orders of magnitude fewer than you would have, though) and you may not want to display them all. One option is to allow the user to select a minimum contribution required to be displayed, or only the first 5 rows. POS often has a long tail (most revenue through US and UK POS, with small amounts from elsewhere), so the second method will capture 3 of these smaller, but important contributors, where the former option may be more appropriate when looking for all IATAs that contribute a certain amount.
This is now my go-to technique for giving users massive flexibility in displaying tabular data. Since I have 15 dimension choices, that makes 2,730 combinations to drill through. Additionally, I add a filter to only show rows that contribute more than a certain percentage of a given measure, and add an Export button that will output only what is displayed in the table. No more massive downloads to get 5 specific rows of data. I arrange the parameters across the top of the table to act as column headers (all in fixed containers, nothing floating here), and colour the measure values according to the current level of the dropdown. Now, everyone wants one.
