A couple of years ago, Tableau introduced one of its most highly requested features ever: Dynamic Parameters. This feature made it possible to populate a parameter with values directly from a data source, rather than being limited to static, manually entered values.
This works great, but as soon as this was available a new request came up: adding manual values to the list – especially the ‘All’ value.
An understandable request, but not easy to implement. Once you set the parameter to update dynamically when the workbook opens, you lose the ability to manually add values.
So, if you want “All” (or any other custom value) to show up, it needs to come from your data itself. I will describe a reliable way to add ‘All’ – but also some more useful values to a Dynamic Parameter
A new ‘slim’ datasource just for the parameter
Although other articles often suggest to union the original dataset to make this work, I create a ‘super slim’ datasource with only the needed values. This makes it much easier to maintain the values, and avoids performance issues due to duplicated datasets.
Distinct SQL query for the values
This query returns all ‘Sub-Category’ value from the datasource, de-duplicates (DISTINCT):
SELECT DISTINCT "superstore"."Sub-Category"
FROM "superstore"."main"."superstore"
(your database might have a slightly different SQL dialect – I use DuckDB for this, which is similar to PostgreSQL)
The query results into this list of Sub-Categories:
Add a datasource based on this custom sql in Tableau, and create your dynamic parameter of it:
With this dynamic parameter you can create calculations/filters
[Sub-Category] = [Category Parameter]Adding ‘All’
If you want to add an option to select ‘All’, create a UNION query
(A SQL UNION combines the results of two or more SELECT queries).
SELECT DISTINCT ' All' FROM "superstore"."main"."superstore" "superstore"
UNION ALL
SELECT DISTINCT "superstore"."Sub-Category" FROM "superstore"."main"."superstore" "superstore"
Note the extra space before All. This is needed because Tableau loads the query in the parameter alphabetically, and we want to show ‘All’ on top of the list, not inbetween Accessories and Appliances.
The ‘All’ value can be used to show all the values, for example using a calculation like this:
IF TRIM([Category Parameter]) = 'All' THEN TRUE
ELSE [Sub-Category] = [Category Parameter] ENDHierarchy in the parameter
You might want to filter on either a category or a sub-category. The UNION query can easily be extended to show both All, Categories ànd Sub-categories. We’ll even add headers to both the Categories and Sub-Categories
SELECT DISTINCT ' All' AS 'list' FROM "superstore"."main"."superstore" "superstore"
UNION ALL
SELECT DISTINCT ' --- Categories --- ' AS 'list' FROM "superstore"."main"."superstore" "superstore"
UNION ALL
SELECT DISTINCT CONCAT(' ',"superstore"."Category") AS 'list' FROM "superstore"."main"."superstore" "superstore"
UNION ALL
SELECT DISTINCT ' --- Sub-Categories --- ' AS 'list' FROM "superstore"."main"."superstore" "superstore"
UNION ALL
SELECT DISTINCT "superstore"."Sub-Category" AS 'list' FROM "superstore"."main"."superstore" "superstore"Both ‘All’, ‘Category’ and the headings get some extra spaces so the list is sorted the logical way.
The result is a nice list with an ‘All’ option, the Categories and the Sub-Categories
Even more customization
With a little imagination – and/or ideas from other people – you can add even more option. Not only more hierarchical options, but also combination. Like this example:
SELECT DISTINCT ' --- Categories --- ' AS 'list' FROM "superstore"."main"."superstore" "superstore"
UNION ALL
SELECT DISTINCT
CONCAT(' ', a."Category", ' & ', b."Category") AS "Category Combination"
FROM "superstore"."main"."superstore" a
JOIN "superstore"."main"."superstore" b
ON a."Category" < b."Category"
This query add the heading, and all ‘two’ combinations of the categories!
This small example shows the ‘enhanced’ parameter. Download the workbook to see the small calculations which make this parameter functional:
