It's been a little more than six weeks since I've saddled up and hopped on the blog. The #Tableau30for30 took a lot out of me and I've needed some time to recover :).
So dynamic parameters. It's the number one most requested feature idea in the Tableau Community. Everyone seems to have come across a time where this would come in handy. It's a great idea and given the demand it should be implemented sooner rather than later.
Unfortunately, it's not coming in the very soon to be released v8.2. You'll hear a lot about some very slick new features - Story Points and the Visual Data Window to name a few. But dynamic parameters ain't on the docket. So it's time we take matters in to our own hands.
Let's start at the beginning. What's the point of dynamic parameters? Here's the text from the idea page (currently with 870 votes - only one with over 320 votes):
Parameters are really useful when you need to do something too complex to be handled by quick filters or action filters. However parameters are currently hobbled by the fact they have to be static lists.
It would be really, really useful and solve a lot of Tableau gotchas, if you could define the options available for a parameter dynamically, from the result of a datasource, and preferably with the option to apply filters also.
That's a bit about what dynamic parameters could do. Let's talk about what parameters are in general. Here's what we're NOT talking about - The type of parameter that is a numerical value that can be changed via a slider or typing something in that will affect calculations. Those parameters work great - no complaints there. Rather, what the "Idea" is typically talking about is a parameter based on a STATIC list (think one time snapshot) of values of a dimension in the data. Here's how it typically goes:
Create a new parameter>>Change the data type to "String">>Click the button for a "List" of Allowable values>>Select "Add from Field" and select the dimension to get your list of values (I'm using Superstore data and going with "Category")
This will create a list of values based on the dimension selected.
This static list now lives apart from your data set. If the data set changes (the company adds more product categories) this list will NOT update. This is the gripe. The thinking is that if the data changes, so should the parameter.
This will create a list of values based on the dimension selected.
This static list now lives apart from your data set. If the data set changes (the company adds more product categories) this list will NOT update. This is the gripe. The thinking is that if the data changes, so should the parameter.
So here we go. I'm going to propose an idea. Follow me for a minute.
When we create a list of values in a parameter, it's as if we're informally creating a new table of values, based on a snapshot of the data, that now lives outside the the data.
What if we just more formally create a table of values that remains based on the underlying data, yet lives outside of the full datasource (as a parameter would). What if, instead of calling this new table of a single dimension of data a "parameter", we called it what it really is - a small data source.
Here's what I'm proposing - Based on the same example from above (Creating a String Parameter from a List of Values based on the Category field), we head back to the data window and head for the Custom SQL. What? You don't like SQL? This is going to be the easiest three lines of code you've ever written. It goes like this:
When we create a list of values in a parameter, it's as if we're informally creating a new table of values, based on a snapshot of the data, that now lives outside the the data.
What if we just more formally create a table of values that remains based on the underlying data, yet lives outside of the full datasource (as a parameter would). What if, instead of calling this new table of a single dimension of data a "parameter", we called it what it really is - a small data source.
Here's what I'm proposing - Based on the same example from above (Creating a String Parameter from a List of Values based on the Category field), we head back to the data window and head for the Custom SQL. What? You don't like SQL? This is going to be the easiest three lines of code you've ever written. It goes like this:
In general terms it's:
Select [Your Dimension]
From [Your Datasource]
Group by [Your Dimension]
And using the SQL from above, what do we get?
A single value list with all of the Product Categories - this looks strikingly similar to the parameter we created, except for one thing. This is dynamic. This list will change if the underlying data would change.
So we've created this dynamic list - what can we do with it? Basically anything we want - we can do most of the same things we could do with parameters - use it in formulas, use it in quick filters (single select or multi-select - the second option is not currently possible with a parameter), etc.... The sky is the limit. Here's an example:
Notice the two data sources. Superstore is our primary data source and the single value list dynamic table based on Categories is the secondary source. Creating a multi-select quick filter works like a champ (make sure the two data sources have the correct links and relationships - which Tableau will make sure of if the headers have the same names).
Interesting stuff, I say.
Is it a perfect solution. Nope - it's a hack. Are there a dozen reasons why it's not a best practice? I'm sure there are, and I'm sure the comments section below will let that play out. But with all that said - could it help solve a problem you're having? It just might.
It's a crazy simple solution and my hope is that it sparks a conversation. I by no means think this is anywhere near the last word on this topic, but with v8.2 walking out the door, it's time we begin to amass ideas for v9.0.
I look forward to your feedback. Many thanks-
Nelson
