*Create filters

Step-by-step guide to setting up page-level filters that connect to SQL insights with variables.

Creating report filters is like connecting a control panel to specific machines - you're building user-friendly controls that connect to SQL insights with variables, letting users change what data they see.

Before you start

You'll need:

  • SQL insights with variables - insights using $variable_name syntax in custom SQL queries

  • Report with SQL insights - insights added to pages where you want filtering

  • Manual filter values - you must provide the list of available options for each filter

  • Understanding of variable logic - how your SQL handles NULL values when no filter is applied

Filter creation step-by-step

1

Access filter settings

  1. Open your report in edit mode

  2. Look for the "Add filter" button in the top-right area of the report page

  3. Click "Add filter" to open the filter settings

  4. The settings open with "Create filter" header and two tabs: Settings and Insights

The "Add filter" button will be disabled if no insights are available to connect filters to. You need SQL insights with variables on the report page first.

2

Configure filter basics

In the Settings tab, set up your filter:

  1. Label: Enter a clear, descriptive name (maximum 50 characters)

    • Example: "Department Selection" or "Report Date"

    • This appears to users as the filter name

  2. Slug: Auto-generated from label (can be edited)

    • Used internally for filter identification

    • Must be unique across all filters

  3. Type: Choose the data type:

    • Text - for departments, statuses, categories

    • Number - for amounts, counts, thresholds

    • Date - for time-based filtering

The slug is automatically generated from your label but can be customized if needed. Both label and slug must be unique.

3

Configure input options

Choose how users will interact with the filter:

Free text/number/date entry:

  • Text: Users enter any text values

  • Number: Users enter numeric values (decimals and negative numbers allowed)

  • Date: Users enter dates in YYYY-MM-DD format

Best for: When you can't predict all possible values or want maximum flexibility

4

Set optional configurations

Configure additional filter behavior:

Set starting value:

  1. Enable "Default value"

  2. Choose default based on filter type:

    • Text/Number: Enter default value

    • Date: Use calendar selection

    • Dropdown list: Select from your custom values

Users see: Filter starts with this value selected

5

Connect to insights

Switch to the Insights tab to connect your filter:

  1. View all insights on your page with their chart type icons

  2. For each insight you want to connect:

    • Find insight in the list

    • Click menu next to it

    • Select SQL variable (e.g., $department, $status)

  3. Connection status shows whether each insight is connected

  4. Multiple connections: One filter can connect to multiple insights with different variable names

6

Save filter

Complete filter creation:

  1. Review settings in Settings and Insights tabs

  2. Click "Save" (or "Connect to insight" if no insights connected yet)

  3. Filter appears as field in top-right filter area

  4. Connected filters show as functional fields

After saving:

  • Filter appears on the report page

  • Users can interact with the filter

  • Connected insights update based on filter values

  • You can edit the filter by clicking the filter and clicking "Edit"

Advanced filter techniques

Multiple filters on one page

Create multiple filters that work together:

  • Department filter affects budget and performance insights

  • Date filter affects all time-based insights

  • Status filter affects project-related insights

Each filter works independently based on its connected insights.

Complex logic in SQL

For advanced filter logic, build it into your SQL queries:

SELECT * FROM projects
WHERE (status IN $status_list OR $status_list IS NULL)
  AND (department = $department OR $department IS NULL)
  AND (budget >= $min_budget OR $min_budget IS NULL)
  AND (priority = 'High' OR deadline < CURRENT_DATE + INTERVAL 30 DAY)

The complex logic stays in SQL - the filter interface remains simple.

Overrides

Users can temporarily change filter values for individual insights:

  • Useful for comparing different scenarios

  • Changes are not saved (temporary testing only)

  • Each insight can be adjusted independently

Troubleshooting filter problems

"My filter shows no data"

  • Check that SQL variable names exactly match filter variable names

  • Ensure your SQL includes NULL handling: (column = $variable OR $variable IS NULL)

  • Confirm filter values match actual data values in your database

  • Test the SQL insight independently first

"Filter has no effect on insights"

  • Check that variable names exactly match between SQL and filter

  • Ensure insights are properly connected to the filter

  • Ensure insights use correct SQL variable syntax: $variable_name

  • Confirm insights are SQL-based (not visual drag-and-drop)

"Some insights don't respond to filter"

  • Only insights with matching variable names will be filtered

  • Check that each insight has the correct variable in its SQL query

"Filter shows 'no data' when empty"

  • Your SQL query is missing NULL handling

  • Add OR $variable IS NULL conditions to show all data when no filter is applied

  • Test the SQL logic first in the insight editor

Using variables in SQL insights

When you create insights with custom SQL queries that will be used in filtered reports, you need to handle cases where filter variables might be null (when no filter is applied).

Variable handling in SQL

For single-select filters (like department dropdown):

Problem: When no department is selected, $department will be null and your query will exclude all data.

Solution: Use this pattern in your SQL:

SELECT * 
FROM your_table 
WHERE (department = $department OR $department IS NULL)

How it works:

  • When filter is applied: Shows only the selected department

  • When filter is empty: Shows all departments (because $department IS NULL becomes true)

Real example:

SELECT 
    department,
    COUNT(*) as project_count,
    SUM(budget) as total_budget
FROM projects 
WHERE (department = $department OR $department IS NULL)
    AND (status = $status OR $status IS NULL)
GROUP BY department

Tips for variable SQL

  1. Always handle null cases - Each variable should have an OR $variable IS NULL condition

  2. Test with and without filters - Check that your query works both with filters applied and empty

  3. Use consistent patterns - Apply the same null-handling approach across all your SQL insights

  4. Document your variables - Add comments explaining what each variable represents

Common variable patterns

Date range filtering:

WHERE (created_date >= $start_date OR $start_date IS NULL)
    AND (created_date <= $end_date OR $end_date IS NULL)

Numeric range filtering:

WHERE (budget >= $min_budget OR $min_budget IS NULL)
    AND (budget <= $max_budget OR $max_budget IS NULL)

Text filtering:

WHERE (description LIKE '%' || $search_term || '%' OR $search_term IS NULL)

Multi-field filtering:

WHERE (department = $department OR $department IS NULL)
    AND (priority IN $priority_list OR $priority_list IS NULL)
    AND (region = $region OR $region IS NULL)

Testing your variable logic

  1. Create your SQL insight with proper NULL handling

  2. Test the insight independently with different variable values

  3. Add insight to a report page

  4. Create and connect page filters

  5. Test filter behavior:

    • No selection - should show all data

    • Single selection - should show filtered subset

    • Multiple selections (if multi-select) - should show combined results

    • Clear filter - should return to showing all data

Zuletzt aktualisiert

War das hilfreich?