Clickable, Drillable Cascading Dynamic Parameters in Tableau

Dynamic parameters are a well-documented demand in the Tableau community. The reasons for this are numerous but most of all simply goes back to better enabling advanced analysis with a better, more intuitive experience for end users. My experience is no different.  It’s not a problem that I run into often, but when I run into it, it is a seemingly endless brick wall between the analysis that I can do and the analysis that I want to do.

Now the solution that follows solves a fairly specific problem (which we’ll talk about) and is not intended to be an end-all be-all solve for dynamic parameters (we still need Tableau on that one).  But what I am going to show you is extremely powerful in the right circumstance - and the funny thing is that it’s been right in front of our faces all this time. 

The solution comes from an actual client problem that was a bit of a brainteaser for a few hours. Before I go any further, a big thanks to both Chris Toomey and Allan Walker of Slalom for allowing me to bounce some ideas off of them, even though I went in a different direction with this solution. 

Here’s the situation that were solving for in the analysis that we want to create:

Working with a large organization with a five or six step hierarchy, some steps have many hundreds of members, and the hierarchy cascades very well (ie walking down a step drastically reduces the pertinent data).  The analysis I want to show is a year-over-year percent change metric (or maybe a few metrics – sales, profit, cost, etc…), where you can see both the parent and its relevant children over time AND where the user can either select a child to drill into (making it a new parent and displaying its children, one level down in the hierarchy) or clicking and giving the ability to walk one step back up the hierarchy. Oh, and the client JUST upgraded to Tableau Server 8.2 (gasp) - so a level of detail calculation is out of scope on this one.

Did I lose you?  They say a picture is worth 1000 words, so in terms of superstore data here’s an idea what I’m talking about:

As you can see on the left we’re showing year-over-year change in sales by two different levels of detail: the thick gray line represents all of superstore, while the three thinner trend lines represent each of the different departments that make up superstore (Technology, Furniture, and Office Supplies). Here’s why this is visually so important - the parent is giving context to the children, so if you can understand that a parent is down 10% year-over-year, you’ll actually have admiration for a child that was only off 5% over the same period of time. Similarly a child that is up 20% year-over-year doesn’t look quite so good when you realize the parent was up 40% year-over-year.  Seeing both levels of detail is extraordinarily powerful visual analytics especially in very hierarchical organizations.

Now creating these views in Tableau at each level of the hierarchy is not especially difficult - what is extraordinarily difficult is creating the interactive flow and filtering between these different levels of detail in an intuitive and excellent user experience.  So the challenge became how can we dive in and out of a piece of the hierarchy without robbing users of a great experience? I’m glad you asked…

Let’s get a couple questions out of the way. 

Is publishing to Tableau Server or Tableau Public required for this solution?

Yes, actually it is.  If your organization doesn’t have Tableau Server and you can’t publish your data to Tableau Public, this isn’t going to work for you.  This hack takes advantage of a trick with URL parameters, which means it will only work after the workbook has been published.  We’ll talk about this more in a bit.

Did you say something about dynamic parameters?

I did and here’s why - we need to use parameters because we need to know what level of the hierarchy we need to be on (are we drilling in or going back out?).  We also need to use parameters because we need to hold each of the values that we’ve drilled into the hierarchy (we’re basically creating a breadcrumb).  The easiest way to understand what I’m talking about is by drilling into the viz below.  The simple viz shows how you can leverage logic based off of parameters to select the correct sheet/level of the hierarchy as you drill in.

Note: I’m going to show two different solutions to solve the same problem.  This first solution (above) is the lesser of the two as it has the most ‘gotchas’ and by far the worst user experience - parameters don’t cascade (or ’show relevant values’ as quick filters can) so by the time you make your second selection only 1/3 of your choices will actually produce another visualization. Imagine if each step had dozens of options, they would be beyond frustrating to get three levels into your hierarchy. Also from a user experience, if I want to drill into something that interests me I want to do it by clicking on or hovering over that piece of data.  Having to select where to drill via a drop-down takes me out of the analytic ‘flow’ that Tableau wants me to be in.

But it totally works - if you have the patience for it. But I’m here to save your patience by giving you another solution via…

DYNAMIC PARAMETERS!!!

This is actually shockingly simple, so don’t blink as you might miss it.  It actually comes from knowing a little bit about how URL parameters work.  You may have used these before to create some customize links, or if you have ever written a blog or embedded a dashboard inside of a webpage you tend to learn a thing or two on this.  And Tableau’s documentation is very clear on how these work:

http://kb.tableau.com/articles/knowledgebase/view-filters-url (Filter Views using URL Parameters)

Here we learn first about how we can set parameters in our workbook by putting that value into the URL, like so (parameters and parameter values in purple):

http://servername/views/workbook/sheet?param1=value1,value2&param2=value

and just a little bit farther down from there we come to find out that we can also set field values from our data or calculated fields in a similar way (calc fields and field values in orange):

http://<servername>/views/CoffeeSales/SalesPerformance?Product%20Type=Espresso&Market=East

These are both very interesting pieces of information but neither is extraordinarily groundbreaking. The question that I’ve never seen anyone ask is what happens if I take a parameter value and set equal to a field value - from the examples above something like this:

http://<servername>/views/CoffeeSales/SalesPerformance?param1=Espresso&param2=East

By Joe that’s crazy! If only there was an easy way to do that :) Well of course there is - it’s our dear friend the URL action. You see making a URL action with a link that looks like the following would actually create the URL you saw above:

http://<servername>/views/CoffeeSales/SalesPerformance?param1=<Product Type>&param2=<Market>

By the way - if you’ve always wondered what the checkbox on URL actions that says ‘URL Encode Data Values’ was all about, now is your time to find out, because you definitely want to check the box when you go with this solution.  It’s what handles funny characters like spaces when you have to put them into a URL.  Here’s some more details if you’re curious:

So what we’re saying is you can dynamically set the value of the parameter by clicking on point in the data and using a URL action to set the parameter equal to the point in the data.  They are dynamic because you set their values when you click on them.  They are cascading because of the filter logic you’re using on each sheet that shows the relevant data for the current level of the hierarchy.  And they’re drillable both in and out (we’ll show you how in a bit).  Thus, ladies and gentlemen – I give you:

CLICKABLE, DRILLABLE CASCADING DYNAMIC PARAMETERS IN TABLEAU!!!

I know - where has this been all my life?

I’m now going to walk you through how I built a second, more elegant solution which leverages this URL parameter hack using super store sales data. One big thing to note before we get started – In the first solution we created two parameters that were both LISTS of strings based on the values of Department and Category from the data.  In the solution that follows (and in your solution) I recommend blank string parameters that can be set to ANY value to accomplish the functionality we’re going to discuss here. The advantage is that, of course as your data changes there is no need to update some list of parameters in your workbook.  Also note that you don’t need to create a parameter for the lowest level of the hierarchy, which in my example is Item (Remember SuperStore is Department>>Category>>Item).

The How-To

To begin we need to create three sheets, one at each of the hierarchy used that we care about. Remember the goal of the view the hierarchy is to show two levels at a time - a single parent and all of its associated children. Also note that in each example were looking at a year-over-year change (by quarters if you’re following along with me). I’ll show you how to make the first one, and the other two you can make following the exact same methodology.

First we’ll create our year-over-year metric - it’s a Table Calc using the lookup function. We’re going to compare the current value to the one from four quarters prior and take the percent difference:

Once that calc field is created it’s time to make the first view – this will be Y/Y change in Sales (by quarter and Table Calc using the default of Table Across (Quarter[Order Date])) at the first two levels of detail:  Total SuperStore (one thick grey line) and by Department (three colorful thin lines).  Leveraging a dual axis (don’t forget to sync them) at those two different levels of detail and it should look something like this:

Note the two different levels of detail in each of the dual axis:

Got it? Perfect.  You’re done creating the first level.  Add in any additional formatting/tool tip cleanup and then duplicate the sheet. I’ve called this first sheet ‘1LevelBlank’ so that I know it’s the first level in my drill down.

In the duplicated sheet (‘2LevelBlank’) drill down one level of detail on both of the dual axis – meaning the axis that showed Total Superstore now shows the level of Department, and the axis that originally Department now goes to Category.  Now given that we haven’t talked about filtering yet, you’re looking at about 20 lines (3 Departments and 17 Categories).   It’s a mess.  Don’t worry, we’ll fix it.  Go ahead and duplicate it one more time (‘3LevelBlank’), this time taking the two different levels of detail down to Category and Item (the lowest level for our example).  If you thought the 3 Departments and 17 Categories were a mess, then this is an absolute disaster.  Again, don’t worry, we’re about to fix it. Now that our three sheets are created, let’s go ahead and create our parameters and filters.

Parameters and Filtering

As noted earlier, the easiest thing to do is to create two blank string parameters and set their default values to be blank.  In this example, I’ve named the two parameters: 1DeptBlankParam and 2CategoryBlankParam.  Here’s an example:

Creating the filtering is really simple as well. We’re simply going to match the parameters we created above up with the fields from the data.  Here’s an example:

Now, the first sheet, ‘1LevelBlank’, doesn’t require any filtering, so let’s head to the sheet on the second level, ‘2LevelBlank’.  On the filter shelf add the filter we just created where [1DeptBlankParam]=[Department].  Because the parameter is blank, set the filter to exclude False (ends up doing the same as keeping only True).

Go to the last sheet, ‘3LevelBlank’, and add the filter for [2CategoryBlankParam]=[Category].  Filter it the same as before, excluding False.  When adding the filter on sheets 2 and 3, they should disappear – if they don’t it’s because your blank parameters aren’t really blank.  To test if you’ve done everything right, show the ‘1DeptBlankParam’ parameter and type in ‘Technology’ – you should see some lines show up on the 2LevelBlank Sheet.  If that worked, type in ‘Copiers and Fax’ in to the ‘2CategoryBlankParam’ parameter – and you should see some lines show up on the 3LevelBlank Sheet.  Once you’ve both successfully, clear both parameters and move on to the hack that brings it all together.

The Hack

Now, you’re all probably very familiar with the fact that you can create actions on dashboards.  Should this be news to you, please go and check out Peter Gilk’s great post on Dashboard Actions http://paintbynumbersblog.blogspot.com/2014/10/a-rough-guide-to-tableau-dashboard.html - well done and rather comprehensive.  The interesting thing that most people don’t know is that basically all of these same actions dashboard actions can be done in the worksheet view.  Since we’re all about trying new things, we’ll take this road less traveled and add some worksheet actions.

We’ve already discussed that URL Actions are going to play a key role, so on the first sheet, 1LevelBlank, go up to the top and select Worksheet>>Actions

You’ll be greeted by a very similar action menu that should look very familiar.  Click Add Action>>URL.  This is where we get fancy.  Since we’re going to create a ‘run on menu’ action, the name of the action is what will show up at the bottom of our tooltip.  In the name field, type: ‘Drill in to ’ and then click on the arrow at the end.  Here, select ‘Department’ which will show the value of the Department you’re hovered over.   

Select only the current sheet in the check boxes, 1LevelBlank.

Head to the URL tab and paste in the following (we’ll dissect it next):

http://public.tableau.com/views/DynamicParameterURL/2LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=<Department>&2CategoryBlankParam=

There’s a lot going on here so let’s talk about each piece so that you understand it.  Understanding all of this is very important because this is how you make it both clickable and dynamic so that everything works.

Let’s start at the beginning:

http://public.tableau.com/views/DynamicParameterURL/2LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=<Department>&2CategoryBlankParam=

This is nothing more than the URL address of sheet ‘2LevelBlank’ on the server.  Remember, you’ll be on the highest level of the hierarchy on sheet ‘1LevelBlank’, so this is going to move you to a different sheet one level down in the hierarchy.  The easiest way to get this URL right is to publish the workbook (either to Tableau Server or to Tableau Public, like I’m doing here).  The format is clear:

http://<server>/views/<workbook>/<sheet>

So you should be able to plug in your values and go.

Next up are some very important URL parameters:

http://public.tableau.com/views/DynamicParameterURL/2LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=<Department>&2CategoryBlankParam=

They’re all specified here: http://onlinehelp.tableau.com/current/server/en-us/help.htm#embed_list.htm

The ? means we’re going to add some details (parameters) to the URL. The : that follows it means that the detail is for how to render on the server (without the :, shows that we’re setting a value in the workbook either as a filter or parameter – which we’ll do at the end of this).

:showVizHome=no

This gets rid of the header at the top of the viz (that is now rather large in v9).  Here’s an explination by the famous Ben Jones of Tableau Public. https://public.tableau.com/s/blog/2014/03/how-fix-your-iframe

&

It means there something else I want to add to the URL.  Separate each ‘detail’ parameter in the URL with this ‘&’.

 :embed=y

‘Required for URL parameter. Hides the top navigation area, making the view blend into your web page better.’  When they say required, they really mean some of the other URL parameters won’t even work if this isn’t included. Obvious, but worth mentioning.

:tabs=no

Hides tabs, even if you tick the box in the publishing dialog to show the tabs (which you should do in this case).

:linktarget=_self

Specifies he target window name for external hyperlinks.  There’s two options here – ‘_self’ which we use here means open in the same window/tab; the other option ‘_blank’ would open the URL in a new window/tab. Using this will re-spin the viz in the same window/tab, which is the user experience we are looking for.

Now for setting the parameters in the filters:

http://public.tableau.com/views/DynamicParameterURL/2LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=<Department>&2CategoryBlankParam=

As we addressed back in the beginning, the trick that we are doing here is setting the parameter ‘1DeptBlankParam’ to be equal to the department that we click on in the viz.  Here’s what that looks like when we add in purple for the name of the parameter and orange for the field value from the data:

1DeptBlankParam=<Department>

In the last piece we are simply reminding Tableau that we want the second parameter to remain blank. You should now understand all of the pieces of the URL that we’ve created.

Lastly, to the box for ‘URL Encode Data Values’ - this should properly encode any spaces, commas, and other special characters that are in the data.  It is very important that you check this box.

Here’s what everything should look like:

Now, here’s what the drill in URL menu action for the next level down on sheet ‘2LevelBlank’ looks like – the only changes are in bold at the end:

http://public.tableau.com/views/DynamicParameterURL/3LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=<Parameters.1DeptBlankParam>&2CategoryBlankParam=<Category>

What we are doing here is going to a different sheet one step down in the hierarchy (‘3the Department parameter is holding the value that we gave it in the first drill down (we’re reminding it of the value we set it to in the first drill down), while we are setting the value for the category parameter based off the category that we’re clicking on in the viz (just as we did before with Department, one level up).  If you have additional levels in your hierarchy, you would simply continue creating URLs where the previous parameters would hold their values, the current level would set the value of the parameter, and lower levels would remain blank.

Great – now how do I go back up the hierarchy?

If you’re at the bottom level of the hierarch on sheet ‘3LevelBlank’ (which is where you’d be after the step above), then you’d want to do two things: 1) Go to the sheet one step above in the hierarchy (in this case ‘2LevelBlank’ and 2) Clear the parameter ‘2CategoryBlankParam’ while holding the value of the first parameter ‘1DeptBlankParam’.  All you have to do is create a URL that does exactly that:

http://public.tableau.com/views/DynamicParameterURL/2LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=<Parameters.1DeptBlankParam>&2CategoryBlankParam=

Here’s what mine looks like:

If once you’re there and you want to return all the way to the top, do the same process.  You’ll end up with the following:

http://public.tableau.com/views/DynamicParameterURL/1LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=&2CategoryBlankParam=

We’re almost home.

Putting it all together

Now we could simple publish the tabs, and the interactions would pretty much work, but the user would be heading to a different URL with every click.  This isn’t a winning formula.  Instead, we’re going to create a dashboard and put the whole flow inside of it.  This way it’s easily accessible from Tableau Server.

It’s easier than you think – Create a new dashboard and name it whatever you want (mine is called DynamicBlankParamDrill).

Add a web page object and let it take up the entire dashboard. When prompted for the URL, put in the URL for the highest level, with blank parameters:

http://public.tableau.com/views/DynamicParameterURL/1LevelBlank?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=&2CategoryBlankParam=

Now, it’s time to publish and enjoy:

Things to note

First off, you’ve just created a dashboard with a dynamic parameter!  Wahoo!

Now we should talk.  If you’ve made this far and you understand everything we did then you’ve probably already realized some things:

1)      Using this method as outlined requires you to create a different sheet for every level of the hierarchy – This is less than optimal if there are many levels/layers in your organization’s hierarchy.

2)     You can only dynamically drill in and out of one sheet at a time.  This is because you can only send a URL to one web page object on a Dashboard. 

3)     If you’ve played around with the dashboard, you may have figured out that the dynamic setting of a parameter in the web page object doesn’t extend to other parts of the dashboard.  What I mean is that if you have the parameters on the dashboard and the web page embedded – the parameters are only being updated in the web page object, not outside of it.  Think of movies like Inception or The Matrix – The experience was inside, while the person existed outside.  This is no different – Even though I can show the parameters existing on the dashboard, the experience is inside the web object – and they don’t really tie together at all.

Now those seem like big limitations – and they are.  But you can actually overcome them all.  Here’s how:

Advanced Jedi Tricks

At the client where I put this together, I needed to not only be able to go up and down the hierarchy in a sheet, I needed to create a complex dashboard with multiple sheets.  I also had six levels to go up and down – so there was no way I was going to create six highly complicated dashboards, with all the actions and sheet placement, and then reference each one in the URL.  I would have gone insane.  Now that you understand the basic framework from above – and I walk through that basic example in detail so that this advanced use makes sense – I’ll talk about how I make this work on a complex Dashboard.

Parent and Child Dimensions, Filtering, and URL Fields

What you need to be able to do on the fly is to know – based on the parameters I have values for – what level of the hierarchy am I on?  If you know what level you should be on then you can set four very important things that drive everything:

1) The Parent Level
2) The Children Level
3) The Filter
4) The URL Fields

If you go this route and use logic based on the parameters, then you can create a single sheet that can set itself to the correct level of the hierarchy.

Basically we test the parameters to see if they’re blank.  To do this we use the LEN() function (returns a number as the length of a string based of the number of characters) and it looks like this:

Len([1DeptBlankParam])=0 and
Len([2CategoryBlankParam])=0

Now if this is true, we’re at the highest level.  Because there’s three levels to our hierarchy, we’d do this twice more:

Len([1DeptBlankParam])>0 and
Len([2CategoryBlankParam])=0

This puts us one level down.  And….

Len([1DeptBlankParam])>0 and
Len([2CategoryBlankParam])>0

Puts us at the bottom level.  Create each as its own calculated field – it will save you some time later.  If your hierarchy has more levels then build as many of these as you need.

Now we’re going to create Children Dimension – We’re basically going to say ‘when we’re at x-level, that means y-field is the correct spot in the hierarchy’. It’s actually very simple:

if [ShowOnlyBlankLevel1] then [Department]
elseif [ShowOnlyBlankLevel2] then [Category]
elseif [ShowOnlyBlankLevel3] then [Item]
end

The Parent Dim is just like it, except one level up on the hierarchy:

The last thing we need to do is build the filtering.  This is slightly tricky, but you’ll get it quickly:

Now, here’s the awesome thing – You can build as many sheets as you want!  Just make sure you use the Parent and Children Dimensions for your slicing and filter everything on the filter we just created.

When you bring everything in to a dashboard everything will be based on the parameters as they are set.  You’ll create dashboard actions (so that you can apply them to multiple sheets, rather than just one at a time) for the URL links. Note: Instead of referencing different sheets as you did before, you will reference this same dashboard view in the URL – only the Parameters in the URL will change.

This brings up to the last bit of trick-er-a-tion:  You’re URL has to be dynamically created, which means for every click you’re going to set each value, just as we did before. But it was easier before because we manually made the URL based on the sheet we were on, but we don’t have that luxury now that everything comes from the same sheet.  So – We have to create fields that do this for us and then call those fields on any sheet that needs the ability to drill.

I’m sure that was confusing. It will make sense in a minute.

The goal here is to tell the parameters what value they should be set to on a click.  So on a drill in click going from Level 1 to Level 2, the Dept param should be changed to the Dept that was clicked.  When going from Level 2 to Level 3, the Dept param should hold it’s value.  Here’s how to write that:

At the same time, on a drill in click going from Level 1 to Level 2, the Category param should remain blank.  But when going from Level 2 to Level 3, the Category param should should be changed to the Category that was clicked.  And here’s how to write that:

Now, you’re going to need to call those values in the URL, so take both fields and add then to the Tooltip on the ‘All’ marks card:

I’ve also added the way to drill back out (download to see it).

Almost done! Dashboard time – Create a new dashboard (Mine is called ‘FinalDynamicDrillDash’).  Set the size of the dashboard to Automatic and bring in the sheet you just created.  Click on Dashboard actions and select URL Actions.

We’re going to do just as we did before with some moderate tweaks.  The URL is going to be self-referencing and the parameter values are coming from the fields you just created.  It looks like this (note the bold parts:

http://public.tableau.com/views/DynamicParameterURL/FinalDynamicDrillDash?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=<ATTR(URLDeptParam)>&2CategoryBlankParam=<ATTR(URLCategoryParam)>

You’re done with that dashboard for the moment.  Just as we did before, create one last dashboard with just a single web object.  When asked for the starting URL, use the one from above, but remove the parameter fields (we want them to be blank when it loads).  It should look like this:

http://public.tableau.com/views/DynamicParameterURL/FinalDynamicDrillDash?:showVizHome=no&:embed=y&:tabs=no&:linktarget=_self&1DeptBlankParam=&2CategoryBlankParam=

Now publish the whole thing!

If you see any errors just reload the page.

You’re done! Yay!

To prove out the dashboard concept I added a Tree Map sheet as well and allow the user to select either sheet to drill in.

Here’s the final product:

I can’t believe you made it all the way through this post.  Thanks for hanging out.  Hopefully you’ve learned a couple things.  The Analysis that you can do with this is incredibly powerful!  So go out and change the world with your dynamic parameters in Tableau!

Nelson