Why Vizioneer?

My Photo
Atlanta, Georgia, United States
The "Vizioneer" comes from mashing two words that have shaped my world for most of my adult life - Engineer and [data] Visualizations (or Vizes to those who know what's up). Graduating from first from Oglethorpe University in Atlanta, followed by Georgia Tech with my Bachelors and Masters in Civil Engineering, all of which taught me to think through anything and everything - problem solving, "engineering" solutions, teaching to the "ah ha" moments - is what I love to do. In 2010 that investigative, engineering mindset intersected a job change and a plunge into the world of Data Analysis. In the search for the next great thing I stumbled on to a data visualization and dashboarding product called Tableau software and things just took off. So now I guess you could call me that engineer with the sweet data visualizations - or just "The Vizioneer" :)

In 2013, I joined the incredible team at Slalom, focusing on Tableau and it's been an amazing experience. Recently in 2014, I was honored and humbled to receive Tableau's highest recognition of being named a Tableau Zen Master. Follow along to see what happens next :)

Thursday, October 1, 2015

I Dream of Zoom and New Actions in Tableau

I Dream of Zoom and New Actions in Tableau

A few months ago I had a bit of weird dream.  Before I share it, I’ll give you that’s not normal - but normal is boring.  So here we go: I dreamed of a new way to do Tableau dashboard actions.  It literally just came to me, I saw it clearly, and I woke up wondering why someone much smarter than me hadn’t already come up with the idea.  I told you - normal is boring.

I’ve share the idea with Tableau – will they build it?  I don’t know.  Maybe we’ll see it in Tableau 10 or it could be in Tableau 17 – the crystal ball’s not clear.  But I thought it would be fun to share it with you all, if for no other reason to spark some ideas of all the different things that can and should be improved/added to the tool to make our lives easier.  But know this as well – underlying this idea this the notion that the overall user experience of Tableau needs more attention.  We can see that Tableau’s realizing this – the new visual data window in 8.2 and improved calculated field window in 9.0 are evidence of this – but we should all push Tableau for more.

I’ll begin by saying something you probably won’t like.  I like PowerPoint.  I get why 25+ years later it’s still the default for presentations – which often are graphical presentations of data.  Now, before you get the pitchforks, I’m well aware that PowerPoint’s been abused, and you’ve got horror stories 600 of slide decks with 3D pie charts – I get it.  That’s not why I love PowerPoint.  My love for PowerPoint comes from my friend Mark Jackson’s suggestion of using it as a poor man’s graphic design tool, and I’ve learned to push it to do some amazing things, creating backgrounds for many of my best Tableau Dashboards.  There’s a number of things in PowerPoint that you should be able to do natively in Tableau – but can’t:
  •         Multi-select objects on a dashboard
  •         Align objects – top, middle, bottom, left, center, right.
  •         Group objects together
  •         Resize groups of objects
  •         Move objects together
  •         Draw shapes and do light weight design
  •         (not to beat a dead horse but…) Auto-save :)
  •         And saving the best for last – the ability to Zoom in and out of a dashboard

The first thing I dreamed about was having an ability to zoom in and out of a dashboard.  Has anyone seen Tableau on a Surface Pro 3?  It looks awful - presumably because Windows is scaling up the text 150% (by default) and leaving this visualizations at 100%, but everything looks cramped.  Same thing on many of the Mac displays with high pixel counts – some of the icons on Tableau become so small they’re difficult to click and full sized dashboards take up 40% of the screen.  The point here isn’t to gripe, but to call out why zooming is needed – the days of Tableau 4 and everyone on a 1200 x 800 monitor are gone.  There are still some unfortunate souls dealing with that set up, but most of us have decent hardware at this point.  Yet, it’s difficult to design to the lowest common denominator without the ability to zoom in and see what I’m doing.

So here’s what I sketched out – it’s ground breaking!  It’s amazing!  It’s… exactly like PowerPoint.

Alright, hopefully you’re convinced on the need for zoom.  I’ve made a few standalone arguments, but really the call for zoom is self-serving.  I need it to exist in order to implement the real meat of the dream: a new interactive and intuitive dialog menu for dashboard actions.

Manually creating dashboard actions in Tableau has always felt very un-Tableau to me.  It feels like a not-so-awesome software wizard where I’m ticking checkboxes on things that hopefully I named.  It’s also annoying that by default everything is selected, yet there’s no ‘Select All/None’ button.  As a reminder, here’s our current state:

Now I’m not a UX designer, and these could definitely look better, but what if instead of having check boxes in some dialog box, they actually resided on the sheets themselves?  If that were the case, I wouldn’t have to spin my wheels trying to remember which one Sheet6 is, rather, I’d have an intuitive experience of:
Step 1: clicking on the source sheets
Step 2: clicking on their target sheets

Here’s two examples of what it could look like:

For those of you not into photography (like Dustin and me), Adobe Lightroom does this effect rather well, and is probably where I got the idea.  Here’s a video showing that experience:

Now I don’t have this whole thing figured out.  I think you should do ‘selected field’ in a drag and drop fashion, similar to the way it’s done on the new calc editor, but I don’t know what you’d do if your sheet is too small to fit the checkbox (you laugh, but there’s a number of Tableau hacks that people use sheets that are 1 pixel x 1 pixel – what do you do there?  I don’t know).  But frankly, it’s not my job to solve it.  I’m just here to push the ball down the field and spark a conversation.  

The last thing I’ll say is that now that you’ve seen these ideas, don’t they feel like they should have always been there?  They feel logical and that they would create a better overall user experience and adoption.  These are Tableau’s goals for their software, as they proved over and over again, but it’s worth a discussion around points where there’s room for growth. 

If it were up to you, how would you improve the software to make it easier and more intuitive to use?

I’d love to hear about it so feel free to drop a comment below and then head over to the ideas section of the Tableau forum and drop it there too (where someone from Tableau will actually read it).

Thanks as always for spending time and hanging out.


Thursday, September 3, 2015

Connecting the Tableau Server Background Jobs Table back to Content

There's some amazing work happening all around us.  One of the greatest things about Tableau is the awesome community, and when someone discovers something it's not long before the world hears about it.  

Today, I have the pleasure of introducing you to Mark Jacobson, a Slalom Consultant out of the Silicon Valley office.  Mark's connected some dots in the Tableau Server Postgres DB repository data model for us, allowing us to better understand how extracts are connected back to their workbooks - something that's really valuable if your Server is overburdened with extract jobs.  Check it out!

Connecting the Tableau Server Background Jobs Table back to Content

If you’ve ever tapped into the Postgres DB that lies at the heart of Tableau Server, you’ll find that it is a gold-mine of data about how your users interact with and experience your Tableau Server. Statistics on everything from how frequently users access workbooks, when they publish content and how well your server is performing can be gleaned from this data. And what better way to do that than by hooking up Tableau directly to get these insights.

Lots of posts have been written about how to access these tables and Tableau has published a data dictionary about what you’ll find within. There have also been numerous contributions from the Tableau Community showing what can be done with this data. Jeffrey Shaffer, who runs the amazing Tableau Blog data + science, has compiled a list of these on his Tableau Reference Guide.

Much of my client work is focused on helping organizations understand what’s going on inside their Tableau Server platforms. On a recent assignment, I had the challenge of needing to understand what was causing my client’s Extract Refresh jobs to experience long delays. I knew that by tapping into the background_jobs table I’d be able to find my answer. 

To see where the delays were, I created a heat map detailing the delays between the scheduled start time and the actual start time of each job. This showed me where the bottlenecks were but one piece was still eluding me. Specifically, I needed to connect the performance of each refresh job back to the workbook or datasource that was experiencing the delay and then to the project where it was located.

Looking for a Solution

At first this would seem like an easy task. I would just join the background_jobs table back to the workbooks table or datasources table on a common ID field and go on with my analysis. That was until I looked at the contents of these tables. The background jobs table does not contain a workbook_id or datasource_id like the other tables within the workgroup schema.

It does contain a “title” field which has the name value of the content and a “subtitle” field that displays either “Workbook” or “Datasource” if the background job is related to an Extract refresh but this is not unique.

What happens if you have a workbook in 2 different projects with the same name? How would you know you were getting the job results of the right workbook? This was indeed my problem so I began to look for a more unique way to link these tables. After all, Tableau Server has to be able to make this association so it can perform the desired operation, right?

Discovering the Link

I knew the answer had to be within the background_jobs table, so I went digging. Low and behold, I found my answer within the “args” field. For a Workbook’s Extract Refresh job, the “args” value looks something like this:

- Workbook
- 87994
- (string masked for confidentiality)
- 75596
- null

Could one of these values turn out to be the workbook_id or datasource_id I was looking for? Turns out the ID value after “Workbook” is the workbook_id value that will tie back to the workbooks table. Having found the workbook_id, I now needed it in a format that I could use in a join. The next step requires some custom SQL and a little knowledge about regular expressions.

Using the following statement, I was able to parse the “args” string into an object_id:

(regexp_replace(args,'---','')),'- ',3
) as integer) as object_id

The statement replaces the dashes and splits the string into its own field which is then cast into an integer so it can be successfully joined with either workbooks.id or datasources.id inside of Tableau’s Data Source editor.

Success! I now had a version of the background_jobs table complete with the ID of the object being refreshed that I could link back to my data!

If you’re like me and you’ve been stuck searching for way to do this, I hope you enjoyed the post. I’ve created a .tds file and hosted it on GitHub so you don’t have to recreate the SQL if that’s not your thing.

Happy hunting!

Mark Jacobson
Consultant, Slalom Consulting
Silicon Valley, California

Tuesday, June 16, 2015

Clickable Drillable Cascading Dynamic Parameters in Tableau

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…


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:

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):


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):


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:


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:


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):


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:

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:


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

Next up are some very important URL parameters:


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).


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 ‘&’.


‘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.


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


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:


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:


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:


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:


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:


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:


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
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

This puts us one level down.  And….

Len([1DeptBlankParam])>0 and

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]

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:


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:


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!