Dropdown Filter

Dropdown filter presents filter values as a drop-down list. Dropdown filter's values can be loaded from a database table or manually added, and they follow the key - value form.

Options#

  • Default Value: entered the value of filter here, not the display name
  • Help text: Optional
  • Hide "All": Disable the "Select All" option
  • Allow Multiple Selection: Allow users to choose multiple items by ticking in the checkbox. If not enabled, users can only select one item at a time.
  • Lazy Loading: For large datasets, lazily fetch data from the server basing on the user's keyword instead of fetching everything.

Configurations#

Get Keys/Values#

There are different ways to get the key - value pair to be used in the filter:

  • From SQL: Write a SQL query that returns two fields to be used and keys and values

  • From Database Table: Instead of writing SQL, you can select a table and specify the fields for keys and values

  • Manual Entries: Input filter values manually in the form:

Parent-Child Filters#

Two dropdown filters can have a parent-child relationship where selecting a filter value in the parent filter will restrict the list of available options in the child filter.

For example, the parent filter is a list of countries and the child filter is a list of cities. When users choose a specific country, only cities of that country will appear in the child filter

How to set up#

  1. Create the parent filter first as a dropdown filter
  2. Create the child dropdown filter. Tick on "This is a child filter" and choose its parent filter from the list. Note that one parent filter can have multiple child filters.

Example You want to create a Parent-Child filter reflecting the Country name and City name

First, the filter Parent Categories must already be created.

For the child filter Categories:

  • If you get the key - value pairs from SQL, 3 fields must be specified: id, name (child category name) and parent_id. For example: SELECT id, name, parent_id from categories.
  • If you get the key - value pairs from Database Tables, you must also specify the field corresponding to parent_id.

If you use Manual Entries, you must specify the parent_id yourself

Create multi-level parent-child filters#

The logic will be the same as a single parent-child filter. For example, you have Continents filter as parent filter, Countries as child filter 1, Cities as child filter 2. From there, set Countries as child filter of Continents filter and set Cities as child filter of Countries filter. Consequently, you will have multi-level parent-child filter

Pre-fetching Schedule#

If your Dropdown Filter values are from SQL, every time you use the filter, Holistics needs to run the SQL to get the values. To speed up filter loading time, you can set a schedule to run the query and prefetch filter values.

How to use#

  • If multiple selection is allowed, Holistics creates a comma-separated list of values and insert an IN clause, for example: where table_col in (value1, value2, value3). Therefore, you need to wrap the filter variable in brackets:
    where [[ table_col IN ( {{variable_name}} ) ]]
  • If multiple selection is not enabled, users can only choose a single item from the list. In the WHERE clause you will only need = operator:
    ```
    where table_col = {{variable_name}}
    ```
    For more information, please refer to Query Syntax page.