> ## Documentation Index
> Fetch the complete documentation index at: https://docs.datazone.co/llms.txt
> Use this file to discover all available pages before exploring further.

# Filters

> How to use filters for interactivity in Datazone Intelligent Apps.

Filters allow users to interactively control the data displayed in your Intelligent App dashboards. They are linked to variables and can be referenced in SQL queries to dynamically filter results.

## Filter Types

Datazone supports several filter types:

| Type       | Description                   |
| ---------- | ----------------------------- |
| `text`     | Free text input               |
| `number`   | Numeric input                 |
| `dropdown` | Select from a list of options |
| `date`     | Date picker                   |

## Filter Configuration

Each filter is defined in the `components.filters` section of your YAML configuration. Key properties:

* `type`: The filter type (`text`, `number`, `dropdown`, `date`)
* `name`: Unique filter name
* `title`: Label shown to users
* `affected_variable`: The variable this filter updates
* `default_value`: (Optional) Default value
* `options`: (For dropdown) Static or SQL-driven options
* `multiple`: (Optional, **dropdown only**) Enable multi-select functionality (default: `false`)
* `placeholder`: (Optional) Placeholder text

The `options` object also accepts `use_input` (SQL options only) to enable [server-side search](#searchable-dropdown-filters).

<Warning>
  The `multiple` attribute is only allowed for `dropdown` filters. Using it with other filter types (`text`, `number`, `date`) will result in a validation error.
</Warning>

<Warning>
  If you define a filter in the Intelligent App layout but do not use it any layout tab, validations will fail. Make sure all defined filters are included in at least one tab's `filters` list.
</Warning>

### Example: Static Dropdown Filter

```yaml theme={null}
- type: dropdown
  name: membership_type_filter_limited
  title: Membership Type
  affected_variable: membership_type_second
  default_value: 'all'
  options:
    type: static
    values:
      - value: all
        label: All
      - value: basic
        label: Basic
      - value: premium
        label: Premium
      - value: vip
        label: VIP
```

### Example: SQL-Driven Dropdown Filter

```yaml theme={null}
- type: dropdown
  name: membership_type_filter
  title: Membership Type
  affected_variable: membership_type
  options:
    type: sql
    query: |
      SELECT distinct ProductGroup_Name FROM sales_order LIMIT 100;
```

<Warning>The query should return only one column with the values you want to display in the dropdown.</Warning>

### Example: Date and Number Filters

```yaml theme={null}
- type: date
  name: order_date_filter
  title: Order Date Filter
  affected_variable: order_date
  default_value: '2024-04-01'

- type: number
  name: penalty_count_filter
  title: Penalty Count Filter
  affected_variable: penalty_count
  default_value: '10'
  placeholder: 'Enter penalty count'
```

<Warning>
  All filter values are treated as strings. Ensure your queries handle type conversion as needed. Also if you pass
  a non-numeric string from SQL based dropdown to a number filter, it will be ignored.
  Example: `SELECT distinct toString(year) as year FROM sales_order;`
</Warning>

## Using Filters in Queries

Filters update variables, which you can reference in your chart queries using double curly braces:

```yaml theme={null}
query: |
  select * from sales_order WHERE OrderDate > '{{ order_date }}' limit 100;
```

## Multi-Select Dropdown Filters

Dropdown filters support multi-select functionality by setting `multiple: true`. This allows users to select multiple values from the dropdown list simultaneously.

### Configuration Example

```yaml theme={null}
- type: dropdown
  name: region_filter
  title: Select Regions
  affected_variable: selected_regions
  multiple: true
  default_value: North  # Optional: single default value
  options:
    type: static
    values:
      - value: North
        label: North Region
      - value: South
        label: South Region
      - value: East
        label: East Region
      - value: West
        label: West Region
```

### Using Multi-Select Values in Queries

When `multiple: true` is enabled, the variable will contain an array of selected values. Use Jinja's `join` filter to format these values for SQL queries:

```yaml theme={null}
query: |
  SELECT * FROM customer_data
  WHERE 1=1
  {% if selected_regions %}
  AND CustomerRegion IN ('{{ selected_regions | join("','") }}')
  {% endif %}
  LIMIT 100;
```

**How it works:**

* When users select `['North', 'South']`, the Jinja template transforms it to: `'North','South'`
* The final SQL becomes: `AND CustomerRegion IN ('North','South')`
* If no values are selected, the condition is skipped entirely

### Multi-Select with SQL-Driven Options

You can also use SQL queries to populate multi-select dropdowns:

```yaml theme={null}
- type: dropdown
  name: product_filter
  title: Select Products
  affected_variable: selected_products
  multiple: true
  options:
    type: sql
    query: |
      SELECT DISTINCT ProductName
      FROM products 
      ORDER BY ProductName
```

## Searchable Dropdown Filters

By default, an SQL-driven dropdown fetches its full list of options once and then filters them **client-side** as the user types. This is fine for short lists, but it does not scale to high-cardinality columns (thousands of customers, product references, IDs, etc.) where you cannot — or do not want to — load every value into the browser.

Setting `use_input: true` on an `sql` options block switches the dropdown to **server-side search**. The text the user types into the dropdown's search box is sent back to your options `query` as the `search_term` variable, so the database does the filtering and returns only matching rows.

### Configuration Example

```yaml theme={null}
- type: dropdown
  name: order_reference_filter
  title: Order Reference
  affected_variable: order_reference
  multiple: true
  options:
    type: sql
    use_input: true
    query: |
      SELECT DISTINCT order_reference
      FROM orders
      WHERE 1=1
      {% if search_term %}
      AND order_reference LIKE '%{{ search_term }}%'
      {% endif %}
      LIMIT 100
```

**How it works:**

* As the user types in the dropdown, the input is debounced (\~300ms) and sent to the options `query` as `search_term`.
* Wrap the search condition in `{% if search_term %}` so the query still returns a sensible initial list (e.g. the first 100 rows) before the user types anything.
* The database returns only the matching values, which populate the dropdown. Because filtering happens server-side, client-side filtering is disabled for that dropdown.
* Always add a `LIMIT` to keep the result set small and responsive.

<Note>
  `search_term` is only injected when `use_input: true`.
</Note>

<Warning>
  `use_input` is only valid for `type: sql` options. Setting it on a `type: static` options block results in a validation error.
</Warning>

`use_input` works for both single-select and multi-select (`multiple: true`) dropdowns.

## Filter Dependencies

Filters can depend on other filters to create cascading filter effects. When one filter changes, dependent filters can update their options based on the selected value. This is particularly useful for hierarchical data like country/city relationships.

### Example: Country and City Filters

In this example, the city filter's options are limited based on the selected country:

```yaml theme={null}
filters:
  - type: dropdown
    name: country_filter
    title: Country
    affected_variable: selected_country
    default_value: 'USA'
    options:
      type: sql
      query: "SELECT DISTINCT country FROM locations ORDER BY country"

  - type: dropdown
    name: city_filter
    title: City
    affected_variable: selected_city
    options:
      type: sql
      query: |
        SELECT DISTINCT city FROM locations 
        WHERE 1=1
        {% if selected_country is defined %}
        AND country = '{{ selected_country }}'
        {% endif %}
        ORDER BY city
```

### Key Points for Filter Dependencies

* Use conditional logic with `{% if variable_name is defined %}` to check if a filter value exists
* Always include a fallback condition (like `WHERE 1=1`) to ensure valid SQL when no filters are applied
* Dependent filters will automatically refresh when their parent filter changes
* You can chain multiple levels of dependencies (e.g., country → state → city)

## Best Practices

* Use descriptive titles and placeholders for better UX
* Use SQL-driven dropdowns for dynamic option lists
* Set sensible defaults to improve initial dashboard state
* Group filters logically on tabs for clarity

See also: [Intelligent Apps Overview](./overview), [Chart Reference](./charts)