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 :)
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.
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:
cast(split_part(
(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.
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:
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:
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):
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.
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.
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.
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:
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:
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:
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:
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:
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!
