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.

• Rosario introduced the general technique in this post.
• Rosario and Klaus leverage the technique in their piece on Exploration Dashboards with Multiple Drill-Downs.
• Klaus, Rosario, and Robert Crocker also presented a use case in the context of P&Ls.

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

PARAMETER	TYPE	CURRENT VALUE	ALLOWABLE VALUES
Level	Integer	0	All
Path	String	Overall	All

Step 2: Definitions for Drill-Down

DD Level	IIF([Level] = 5, 1, [Level] + 1)
DD Label	CASE [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 Path	CASE [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 Densification

INT([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 Label	CASE [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 Path	CASE [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 ACTION	SOURCE	TARGET PARAMETER	FIELD OR VALUE	AGGREGATION
DD Level	Chart	Level	DD Level	None
DD Path	Chart	Path	DD Path	None
DU Level	Menu	Level	DU Level	None
DU Path	Menu	Path	DU Path	None

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!