Dynamic categorical columns on your dashboard using Visual SQL

Using the Visual SQL Pipeline, it’s possible to dynamically change between categorical columns on your dashboard. This allows dashboard viewers to select only the fields they’re interested in viewing. For this example, we’ll use the Dundersign Demo Data data source, which uses PostgreSQL syntax.

1. On the dashboard, click Add Control from the sidebar and choose Dropdown from the options.

2. Switch to SQL Mode, type in the following, then click Run Query:

   select 'Name'
   union
   select 'Plan'
   

   

   Note: If you have more than two categories, add additional UNION operators and SELECT statements with the additional category names. The aliases you set need to match the names exactly as they are in the table or chart.

3. Below the SQL editor, rename the Dashboard Control to something unique like CATEGORY. Make sure that Multi-select is unselected. Add one of the category names as the Default Value (optional). We’ll use Name as our default value in this example. Click Save to Dashboard when done.

   

4. If you’ve already created your table, choose to Edit it to add your new Dropdown as a filter. If you haven’t created a chart yet, create a new chart and add both categories and the necessary aggregated columns to the chart.

5. Once the initial columns are added, add a step in the Pipeline. For this example, add a Formula Column, choose Custom as the formula type and enter a CASE statement in the formula field. Here’s the one we’ll use for our example:

   case when {CATEGORY} = 'Name' then "Name" else "Plan" end
   

   Note: If you have more categorical columns, add additional WHEN and THEN clauses to your CASE statement. Click Save to submit your changes.

6. Use Hide Column Actions to hide your original category columns in the final chart. In this example, we hide our original Name and Plan columns.

   

7. Add a Group & Aggrgate Action, setting your dynamic category column to use the GROUP option and set your aggregated columns to use the Total Sum aggregation. Click Save.

8. Reorder the new category column (from Step 5) to the front of the Result Table. Rename the Custom formula column and the aggregated columns as desired.

9. Save the chart to your dashboard, then you’re done!