Drill-Downs and Drill-Ups in Tableau without CONTAINS

Aug 25, 2020

Lukas Deibel

As a constant visitor on Klaus’ blog I came across the blog series of Drill-Downs and Drill-Ups in Tableau.

I appreciated the proposed way and how it is leveraging visual elements to provide a guided Drill-Down experience for your users. I only had one issue: SAP HANA. Since SAP HANA doesn’t support the Split and FINDNTH function, I needed to figure out a way to avoid them… Which I did, only to find Klaus’ new blog post Drill-Down and Drill-Up without Split and FINDNTH two days later. You really need to be quick to beat this guy 😉 Looking into the details I noticed that we use different approaches.

In the following, the blog adapts Klaus’ example so that you can use Superstore to create a hierarchy of four levels, with a fifth level to return to the first level.

Step 1: Create Parameters

PARAMETERTYPECURRENT VALUEALLOWABLE VALUES
LevelInteger0All
PathStringOverallAll

Step 2: Definitions for Drill-Down

DD LevelIIF([Level] = 5, 1, [Level] + 1)
DD LabelCASE [Level]
WHEN 1 THEN [Region]
WHEN 2 THEN [State]
WHEN 3 THEN [City]
WHEN 4 THEN str([Postal Code])
WHEN 5 THEN “Top”
END
Reg/State[Region]+[State]
Reg/State/City[Region]+[State]+[City]
Reg/State/City/PC[Region]+[State]+[City]+str([Postal Code])
DD PathCASE [Level]
WHEN 1 THEN [Region]
WHEN 2 THEN [Reg/State]
WHEN 3 THEN [Reg/State/City]
WHEN 4 THEN [Reg/State/City/PC]
WHEN 5 THEN ‘Top’
END

Drag and drop the “DD Level” field to dimensions.

My first solution was even a bit simpler and faster, however had a big downside. It was only applicable for Drill-Downs with dimensions that have unambiguous values. Klaus had the idea to use Combined Fields to get a unique connection between the values. Although Combined Fields can’t be used in CASE or IF Statements the idea to concatenate the information did the trick. Instead of Combined Fields you can use a Calculated Field.

The difference to Rosarios and Klaus’ approach is the way how the Path Parameter is populated. Instead of dynamically adding the selection to the Path Parameter, you can use the concatenated string to feed the Parameter.

Filter CASE [Level]
WHEN 2 THEN [Region] = [Path]
WHEN 3 THEN [Reg/State] = [Path]
WHEN 4 THEN [Reg/State/City] = [Path]
WHEN 5 THEN [Reg/State/City/PC] = [Path]
ELSE TRUE
END

For each value in the hierarchy there is a unique string, which can be leveraged to filter the views. The advantage of this approach is, that it avoids the CONTAINS Function which in my experience causes a huge load on the data base.

Step 3: Definitions for Drill-Up

DU DensificationINT([Order Date]) % [Level]

Convert “DU Densification” into a Dimension with a right click or just drag it to dimensions.

The formula calculates the remainder of dividing the numerical value of the date of the “Order Date” by the level. In case the level equals 3 the days are clustered into three groups (0,1,2), one for each required menu option. In contrast to the original posts the amount of columns which are returned is flexible and depends on the level.

DU Level[DU Densification]+1
DU LabelCASE [DU Densification]
WHEN 0 THEN “Top”
WHEN 1 THEN [Region]
WHEN 2 THEN [State]
WHEN 3 THEN [City]
WHEN 4 THEN STR([Postal Code])
WHEN 5 THEN “Top”
END
DU Color[DU Densification]+1 = [Level]
DU PathCASE [DU Densification]
WHEN 1 THEN [Region]
WHEN 2 THEN [Reg/State]
WHEN 3 THEN [Reg/State/City]
END

Step 4: Creating the worksheets

The steps to put together the views and to set the parameter actions are very similar to the original post.

Make sure to apply the Filter to both views, the drill-down and the drill-up view.

Step 5: Putting together the dashboard

PARAMETER ACTIONSOURCETARGET PARAMETERFIELD OR VALUEAGGREGATION
DD LevelChartLevelDD LevelNone
DD PathChartPathDD PathNone
DU LevelMenuLevelDU LevelNone
DU PathMenuPathDU PathNone

Thanks for reading. If you enjoyed this blog post leave a comment and share it with a friend!

Note: The Drill-Up menu like presented in this post will only work under the assumptions of Internal Data Densification (and unfortunately not in the presented case). For a working solution (including CONTAINS but also usable with SAP HANA connections) see this post: Drill-Down and Drill-Up without Split and FINDNTH. We’re working on it!