Dashboard Filters

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:

1. Date aggregation filters
2. Date range filters
3. 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 this_week, this_month, 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:

1. Filters with custom hardcoded values and labels
2. 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];

The [name=SalesPerson] is substituted with the choices the user makes. There are three possible states:

1. None picked – the macro is replaced with 1=1
2. One picked (alice) – the macro is replaced with name='alice'
3. 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:

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;

Next Generation SQL Data Analysis Write SQL, visualize data, and share your results. Onboarding forward-thinking teams now:

No spam, ever! Unsubscribe any time. Learn more about the product.