Drill Down with Navigation Buttons
Work in Progress
Background
Drilling down is an integral part of dashboard design. Overview, zoom and filter, details on demand. However, doing this in one sheet and maintain optimal performance can add hiccups during the development phase. This feature was suggested to our healthcare system’s ambulatory committee and it was quickly something they desired.
In a large effort a year ago through a collaboration between Brigham and Women’s Hospital and Massachusetts General Hospital identifiers were developed to help aggregate the various practices at higher levels. The ability to compare metrics such as “average new appointment lag”, “schedule utilization”, etc. at the Service level and then drill into a problem area, and keep drilling down several layers while identifying a particular group of interest? Excellent!
But what if a user in the Director of Medicine and they want go down several levels to the Practice level without filtering along the way?
Initial Approach (now broken)
I first designed this dashboard in Tableau Desktop version 10.3. The key issue that we encountered was figuring out how to put buttons that will only affect the drilldown at certain times. Example: you’re at level 3, you don’t want to click on a button that is based on the level 5 granularity because you’d be filtering on the first row that appears alphabetically.
As such, I had developed buttons for every level of the drilldown and wrote calculated fields to determine when they would pop open for use. If you’ve drill down to level 3, you would have one level 3 row as the header for multiple level 4 rows. Clicking on this would pass levels 1-3 as action filters (in addition to next level #). It worked, but it was an absolute BEAST to maintain. There were ~15 buttons and at least 30 filter actions.
One more thing: to make different buttons appear, a little trick was used that we had implemented previously – floating containers. In 10.3, you could have a floating container, a thin Blank, and then a worksheet that would collapse and expand as mentioned above. If the sheet collapsed, the container would collapse with it! It worked wonders to keep buttons in the same location and, even though we couldn’t easily test on Desktop, it worked as planned on the Server.
Alas, we upgraded to 2018.3, and this “feature”, which I imagine was a bug, no longer works.
New Approach (hey, it works!)
Enter Set Actions. I believe just about every post has mentioned Set Actions, but they’re that useful to user interactivity. As soon as Set Actions were mentioned at TC18, I immediately started to think about creating a Set for each column that was featured in the drilldown. Through participation in Workout Wednesday and wonderful tips from Lindsey Poulter and Bethany Lyons I quickly got acclimated to how to use them. However, back to the original question: how to drilldown without filtering.
Due to certain levels potentially rolling up to different parent levels, we had documented the need to click on the header to drill down. The metric (Sales) is a window sum calculating everything at the Level Value that is displayed and stack marks are turned off. Otherwise if you move forward a level, you would see the new Level Value (product Category) divided up by each Region – not what we want! Due to the marks not being stacked, the bar you see is whichever is first alphabetically. Clicking on this would filter the viz (you’ll notice this in the breadcrumbs if you so choose to test.
With that important caveat out of the way, let’s talk about reducing the original 9723415 buttons into 2.
Methodology
Create a set for each column that is used in your drill down up until the last one. For Superstore, I did:
Region
Category
Sub-Category
Now with these Sets created, we can create the filter on the sheet by referencing each as needed based on our current Level.
CASE [Level CD]
WHEN 1 THEN TRUE
WHEN 2 THEN [Set 1 Region]
WHEN 3 THEN [Set 1 Region] AND [Set 2 Category]
WHEN 4 THEN [Set 1 Region] AND [Set 2 Category] AND [Set 3 Sub-Category]
END
