Documentation Visual SQL (beta) FAQs

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 use the Document Company 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'
    

    SQL codeblock

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

    Select dropdown settings

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

    Add a column in the Pipeline with a CASE statement

  7. Add a Group & Aggrgate Action, setting your category columns to use the GROUP option and set your aggregated columns to use the Total Sum aggregation. Click Apply & Close.
    Group & Aggregate in Pipeline
  8. Reorder the new category column (from Step 5) to the front of the Result Table. Rename the aggregated columns as desired.

    Group & Aggregate result table