*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 queriesReport 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
Access filter settings
Open your report in edit mode
Look for the "Add filter" button in the top-right area of the report page
Click "Add filter" to open the filter settings
The settings open with "Create filter" header and two tabs: Settings and Insights
Configure filter basics
In the Settings tab, set up your filter:
Label: Enter a clear, descriptive name (maximum 50 characters)
Example: "Department Selection" or "Report Date"
This appears to users as the filter name
Slug: Auto-generated from label (can be edited)
Used internally for filter identification
Must be unique across all filters
Type: Choose the data type:
Text - for departments, statuses, categories
Number - for amounts, counts, thresholds
Date - for time-based filtering
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
Set optional configurations
Configure additional filter behavior:
Set starting value:
Enable "Default value"
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
Connect to insights
Switch to the Insights tab to connect your filter:
View all insights on your page with their chart type icons
For each insight you want to connect:
Find insight in the list
Click menu next to it
Select SQL variable (e.g.,
$department
,$status
)
Connection status shows whether each insight is connected
Multiple connections: One filter can connect to multiple insights with different variable names
Save filter
Complete filter creation:
Review settings in Settings and Insights tabs
Click "Save" (or "Connect to insight" if no insights connected yet)
Filter appears as field in top-right filter area
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"
Your filter is now active! Users will see the filter interface you configured and can interact with it to change what data appears in connected insights.
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 appliedTest 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
Always handle null cases - Each variable should have an
OR $variable IS NULL
conditionTest with and without filters - Check that your query works both with filters applied and empty
Use consistent patterns - Apply the same null-handling approach across all your SQL insights
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)
Important: Without proper null handling, your filtered reports can show empty charts when filters are not applied. Always test your SQL insights both with and without active report filters.
Testing your variable logic
Create your SQL insight with proper NULL handling
Test the insight independently with different variable values
Add insight to a report page
Create and connect page filters
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
Pro tip: Always test your SQL variable logic in the insight editor before adding to reports. Use the variable controls to test different values and ensure NULL handling works correctly.
Zuletzt aktualisiert
War das hilfreich?