Interactivity & Parameters

Parent-Child Filters

Custom filters associated with a drop-down control permit setting a parent filter. A custom filter that is assigned a parent filter is called a child filter, or dependent filter. The available values for a child filter depend on the value(s) selected in the parent filter.

A typical example may be a parent filter that permits selecting a country (or multiple countries) and a child filter that permits selecting a city (or multiple cities) within the selected country (or countries), as used in a query like this:

SELECT 
   ... 
WHERE {country=country_filter} AND {city=city_filter}

Here country_filter and city_filter would be configured as custom filters with drop-down controls, so that the dashboard UI may look as follows:

parent-child.png

When displayed within the dashboard filter bar as above, the parent filter is always shown to the left of the child filter. To create such parent-child filters:

  • Create the parent filter as a custom filter with a drop-down control.
  • Create the child filter as a custom filter with a drop-down control and assign the previously created filter as the parent filter. In the SQL query that selects the values for the child filter, make sure it produces 3 columns of data in exactly the following order:
    1. The values of the child filter.
    2. The labels for the child filter.
    3. The matching values of the parent filter.

A child filter may in turn be the parent filter for another child filter, establishing a chain of dependent filters. However, the number of rows in the result set for the values of a child filter is subject to the same limits as regular queries, so with a larger numbers of values, especially many child values per parent value, or longer chains of child filters these limits may be hit. E.g. 100 parent values, each having 100 child values would already result in 10.000 rows in the result set for the child filter values.

For the above example, the SQL queries for selecting the values for the parent-child filters for country and city could look as follows.

Parent Filter (Country):

SELECT DISTINCT 
  country_id, 
  country 
FROM countries;

Child filter (City):

SELECT DISTINCT 
  city_id, 
  city, 
  country_id 
FROM cities;

Embedded dashboards

Parent-child filters work as usual on embedded dashboards with full interactivity. You can additionally fix a specific parent filter value in the fixed_parameters for the embedding, which will

  • display only the child filter drop-down in case only one parent filter value is set in the fixed_parameters.
  • display both parent and child filters, but where the parent filter allows the user to select only one of the values specified in the fixed_parameters, if there are multiple.

In both cases, the set of options for the child filter available to the user of the embedded dashboard is limited to those associated with one of the fixed value(s) of the parent filter.

 

Related articles