Documentation Visual SQL (beta) FAQs

Filter "Is one of" with Dropdown in Visual SQL

There may be a situation where you need to filter on a Dropdown list in the Pipeline.

Let’s say we have a Dropdown Control that lists all of the states in the United States on your dashboard. Multi-select is selected, Empty state is Show All, and Initial value(s) is deselected.

We want to select multiple states in the Dropdown and have our chart filter on those states. We could do this manually by adding the State column to the Filters section of the chart, but we don’t want to do that because we need the initial query not to filter on any state selection. Therefore, the filtering must be done in the Pipeline by following the steps below:

  1. The first step to create your “is one of” Pipeline filter is to make a clone of the column you want to filter on. You can do this in two different ways:
    • Adding the column twice when building your initial Query or
    • Using a Calculated Column to clone the column to the result set.

    Assuming you have some Actions before the filter, we’ll use the latter method for this example. To quickly clone a column, add a Calculated Column, select Custom as the formula type, then write in the column name you’d like to clone in double quotes (") in the formula field.

    Here, we’re looking to filter using our State column; therefore, we enter "State" as the formula. We also rename this new column to State2 (the column name is arbitrary because we’ll hide this column at the end).

    Add Column "State2"

  2. The next step is to use a CASE Statement. The Pipeline uses SQLite syntax, so that’s what we use to set this up. We’ll essentially force a boolean out of the clone State column to use as a filter.

    To do this, use Edit Formula on the State2 column, select Custom as the Formula Type, and use the CASE statement below in the formula field:

     case when {DROPDOWN.IN('"State2"')} then 1 else 0 end
    

    Replace DROPDOWN in the formula with the name of your Dropdown Control and State2 with the name of the column you’re editing. This will connect the Dropdown to this Action.

    CASE statement in Edit Formula Action

  3. Next, add a Filter Action to correctly filter on the Dropdown selections. Select to include rows matching all conditions where the column “State2” equals 1 ("State2" = 1).

    Note: If you’re trying to implement an “is not one of” filter, you’d just need to change include to exclude.

    Filtering rows in Chartio

  4. Lastly, hide the boolean “State2” column.

If you followed these steps correctly, you should now have an “is one of” filter in the Pipeline and you should be able to filter your chart using multiple selections from the State Dropdown.

This process also works for multi-select Text Inputs. Go ahead and try it out!

State Dropdown