Silota allows you to author custom filters allowing an end-user to manipulate the query sent to the database without knowledge of SQL. There are three kinds of filters:
- Date aggregation filters
- Date range filters
- Custom filters
1. Date aggregation filters
The date aggregation filter allows the user to pick an aggregation interval: daily, weekly, monthly, quarterly and yearly. The date aggregation is dynamically changed by using the SQL macro like this:
select [timestamp_field:aggregation], another_column from table group by [timestamp_field:aggregation]
and the user picked aggregation interval is substituted in-place before sending it to the database.
Read more on SQL Macros.
2. Date range filters
The date range filter allows the user to pick a date range for the time axis between relative periods like
last_30days, and absolute periods.
Read more on SQL Macros.
3. Custom filters
Silota supports two kinds of filters that are custom to your organization:
- Filters with custom hardcoded values and labels
- Filters that retrieve values and labels from the database dynamically
3a. Filter labels and values hardcoded
Simply type in the labels/values you want to use. For example, to use the hardcoded values alice, bob and eve, you would structure your query like this:
select name, sum(revenue) from sales where [name=SalesPerson];
[name=SalesPerson] is substituted with the choices the user makes. There are three possible states:
- None picked – the macro is replaced with
- One picked (alice) – the macro is replaced with
- Multiple picked (alice, bob) – the macro is replace with
name in ('alice', 'bob')
3b. Filter labels and values with a query
Additionally, you can use a SQL statement to dynamically populate the filter labels and values. This is useful when the number of choices is too numerous to hardcode, or you want to provide a user friendly label that is different from the value. A good example of this is to use the
customers.id for the values and
customers.name for the label.
Consider a table
customers with the following data:
Using the SQL for the filter definition:
select id, name from customers;
shows the labels "Tesla", "Apple", "Google", but will use the values from the
id column of 100, 200 and 300 when the user picks one or more choices.
4. Additional considerations
4a. Using Filters in Chart Titles
If you want to use the "SalesPerson" filter value in a chart title, you can simple put "[SalesPerson]" in the title.
4b. Parent-child filters
At times, you will want to define a parent-child hierarchy between the filters. It's possible to do this in Silota:
First, define the parent filter as before either with a SQL statement or by hardcoding:
select value, label from table;
Then, define a child filter and make sure the SQL you define returns three columns:
select value, label, parent_value from table;
On the front-end, depending on the choice of the parent filter, the child filter is automatically filtered to only include values that match the parent filter.
Parent-child filters (example)
If you have a table
product of products with their categories:
|1||1||Books||Art Of War|
To define the parent filter with the categories:
select category_id, category_name from product;
And to define the child filter based on the parent category:
select product_id, product_name, category_id from product;
No spam, ever! Unsubscribe any time. Learn more about the product.