1. Data Reporting
2. Report Filters

Report Filters

Filters provide you ability to make your dashboards dynamic and change values according to different user selections.

Filter Syntaxes

Each filter would have a "variable name", you add the filter name to your SQL query. For example, the following query filters products based on the category of user's selection:

SELECT * FROM products P
WHERE is_active = true
  AND [[ P.category_id = {{category_id}} ]]

Notes:

• You refer to each filter by using double curly brackets, {{var_name}}. In the above example category_id is a filter variable name.

• The double square brackets [[ are used to indicate optionality. When there's no category being selected by the user, the text wrapped by [[ ]] will simply be replaced by a TRUE expression.

The above will be translated to:

SELECT * FROM products P
WHERE is_active = true
  AND P.category_id = '1'

Although '1' is string and P.category_id is a number field, most SQL DB will still recognize this and do proper matching.

If there's no category_id selected, the following SQL will render:

SELECT * FROM products P
WHERE is_active = true
  AND (1=1)

For multi-select values

If you're using a multi-select dropdown filter, please change your SQL to the following:

SELECT * FROM products P
WHERE is_active = true
  AND [[ P.category_id IN ({{category_id}}) ]]

It will be translated to:

AND P.category_id IN ('1','2','3','4')

Escaping the single quotes

If you want to remove the single quote of the variable, simply add |noquote to the expression.

SELECT * FROM products P
WHERE is_active = true
  AND [[ P.category_id = {{category_id|noquote}} ]]

The where clause will become:

AND P.category_id = 1

Filter Types

There are 6 types of filters that can be added to a report:

• Date Filter: Accepts date inputs from the user.
• Date Range Filter: Accepts two date inputs from a user for a specific time period.
• Text Filter: Accepts free text inputs from the user.
• List Input Filter: Accepts list inputs.
• Drop-down Filter : Accept inputs from a drop-down menu. Typically used as a dimension filter.
• Separator Filter: Adds a line break between the filters so that subsequent filters are shown on a new row.

 

All filters are added into a report through the report editor. For a report specific filter, select “New Adhoc Filter “ under the Filters section.

If you have previously created Shared Filters, you can also add them to your report. Shared filters are similar to ad-hoc filters, but as oppose to locally confined ad-hoc filters, shared filters can be used in multiple different reports.

 

Once a filter has been created, it appears on the report with its input field and a navigation bar.

 

All filters can be referenced by SQL queries. To find out more about using filter values in your query syntax, please visit Query Syntax.

 

Date Filter

A date filter can be set up to represent a single date input. This could be used in cases such as defining the earliest date a product arrived, or querying people born after a certain date.

 

Notice in this video how we add date filters to the report and assign them variable names. You can refer to these variable names with double curly braces {{start_date}} and {{end_date}} inside your query syntax.

When the date radio button is selected in the “New Ad-hoc Filter” screen, the fields to populate are:

• Variable Name: This will be used inside the SQL query to represent the variable.
• Label: Pre-populated when the variable name is set. Shows as the filter label in the report.
• Default Value: Optional. Default value for variable. If not set, defaults to yesterday. The date filter accepts a variety of inputs including wording such as 'yesterday' or '2 weeks ago'. Please see Relative Date Syntax for more information.
• Help text: Optional. Description of the filter.
• Permissions: Optional. Allows you to set date range limitations based on user groups. To learn more about permissions, please see Restricting Date Selection for Date Filters.

 

The filter can also be limited by selecting maximum or minimum dates, which define the dates users can pick from.

 

By default, the date filter only allows a selection up to yesterday. This limits the use of today's incomplete data. If you wish to change this limitation, simply turn on "Set maximum date" and set it to a date in the future. This could be today, 1 month from now, or similar. You can also set default values for the date filters using human friendly relative date syntax. In the video, we set the filters such that they will always load the past week of data.

Refer to our docs for more examples of default date values you can use.

To see how to include the date filter in your SQL query, please see Query Syntax References.

 

Date Range Filter

The date range filters works in the same way as a single date filter, however, it makes choosing a time period between two dates easier.

Date range filter setup includes the following fields:

• Variable Name: This will be used inside the SQL query to represent the variable.
• Label: Pre-populated when the variable name is set. Shows as the filter label in the report.
• Default Start Value: Optional. Default value for start variable. The date filter accepts a variety of inputs including wording such as 'yesterday' or '2 weeks ago'. Please see Relative Date Syntax for more information.
• Default End Value: Optional. Default value for end variable.
• Date Min Value: Optional. Set the earliest date a user can pick.
• Date Max Value: Optional. Set the latest date a user can pick.
• Help text: Optional. Description of the filter.

 

 

The start and end dates of the date range can be references in the SQL queries by appending _start or _end tags to your variable name. If your variable name is {{signup_from}}, you can refer to the start and end dates in your query as {{signup_from_start}} and {{signup_from_end}}.

The SQL query would become:

WHERE [[ U.created_at::date >= {{signup_from_start}} ]]
AND [[ U.created_at::date <= {{signup_to_end}} ]]`

To see how to include the date range filter in your SQL query, please see Query Syntax References.

 

Dropdown Filter

A dropdown filter produces a drop-down menu that can be used by the end user for easily selecting variables. The values of the dropdown can be loaded from a database table or manually added in as a comma separated list. The dropdown filter always includes a value and a name. The name is shown as the option on the dropdown list, but when chosen, the equivalent value is referenced.

Example: A dropdown could be generated to display a list of countries (name), which you can pull from your database table with their equivalent country codes (value).

Table: countries
country_code | country_name
---------------------------
usd          | USA
sgd          | Singapore
myr          | Malaysia
idr          | Indonesia`

 

Creating Dropdown Filters

 

Let's add a dropdown filter to let your users filter this report by the City that the posting was listed in. In this video, we will load the values of this city dropdown filter from an existing database table (values of table shown on 0:13).

 

Holistics will create the 2 comma-separated values (value and text) for the dropdown filter from the query below:

SELECT DISTINCT id, name FROM quicksell.cities ORDER BY 2

And we have just created a dropdown filter for our report!

 

To further explain, a dropdown filter in Holistics consists of 2 comma separated values:

1. the value field which is basically the corresponding syntax passed into your query
2. the text field which shows the labels in your dropdown.

The text field  is shown as the option on the dropdown list, but when chosen, the equivalent value is referenced.

Dropdown filters can be created by a new adhoc filter by selecting the "Dropdown" radio button. As with date filters, the variable name and option fields are available:

• Variable Name: This will be used inside the SQL query to represent the variable.
• Label: Pre-populated when the variable name is set. Shows as the filter label in the report.
• Default Value: Optional. Default value for variable.
• Help text: Optional. Description of the filter.
• Hide All: By default, a dropdown filter has an additional option (All) to select all dropdown values listed by replacing it with an empty INPUT value. Check here to hide this option so the values displayed on your dropdown are strictly what you listed. Especially useful for column based filtering.
• Allow Multiple Selection: Check this to use a multi-select dropdown filter instead of a single value filter, so your viewer can select multiple options to filter the data. Note that your query will need to be changed to using an IN clause like below

where [[ channel IN ({{channels}}) ]]

• Lazy Loading: For large data-sets. Lazily fetch data from server based on user's keyword.

To add values to the dropdown menu, you can either pull data from the database by a SQL query or by specifying the required columns, or you can manually input the values. The required list of variables needs to form a name value pair, such as an id and a name, or a country code and a country name.

 

From SQL

Allows you to select your values by a SQL query from your selected database.

 

From Database Table

Allows you to specify the required database, table and fields. The “Generated SQL” field below shows the generated SQL query.

 

Manual Entry

By double clicking the table, you can add in your own dropdown values. The value and the name are separated by a comma, and each item is written on its own line. The second value will be the one shown on the dropdown menu.

This will generate the following dropdown filter:

To see how to include the dropdown filter in your SQL query, please see Query Syntax References.

 

Parent-Child Filters

Another option when creating a dropdown filter is a parent-child filter. Parent-child filters allow two dropdown filters to have a parent-child relationship. For example, one filter could be a list of countries and the other one, its child filter, a list of cities. Whenever the user chooses a specific country from the first filter, only cities of that country are shown in the second one.

 

How to set up

To set up a parent-child filter, a parent filter needs to first be created. The parent filter can be set up as any other dropdown filter with a list of names and values. The child dropdown filter can then be created by starting a “New Ad-hoc Filter”, choosing the dropdown option and selecting the “This is a child filter” checkbox. This then lets you select the parent filter from the list of existing dropdown filters. Note, one parent filter can have multiple child filters.

You will then need to specify a name, value and the parent's values from each item:

Alternatively, just as for normal dropdown filters, you can pull this information from the database with a query such as:

select item_value, item_name, category_value from items

 

Permissions

Dropdown filters can also be managed through permissions. These can be added by opening up the “Permissions” section when creating or editing dropdown filters. This allows you to only display data to specified user groups. For example, only showing events happening in Singapore to users residing in Singapore. To learn more about permissions, please see Permissions System.

For parent-child filters, setting up permission filters on the parent filter also requires manual setup of permission on the individual child filters.

For example, if the parent had the following permission syntax:

permissions:
- in_group: User Group 1
  sql: values (1), (2), (3), (7), (8)
- in_group: User Group 2
  sql: values (4), (5), (6)

You would also need to configure your child filter permissions to:

permissions:
- in_group: Your User Group 1
   sql: select id from quicksell.categories where parent_id in (1, 2, 3, 7, 8)
- in_group: Your User Group 2
   sql: select id from quicksell.categories where parent_id in (4, 5, 6)

 

List Input Filter

A list input filter allows your business users to input a list of values into the SQL queries either through manual input, or from sources such as an Excel or a CSV file. The list items are separated by line breaks and will become variable inputs in your SQL query. This filter type is particularly useful in cases when a business user has a list of items in a CSV file, such as customer_id's, and would like to find out how many bookings were made by these customers in a certain period of time.

The filter itself requires a minimal amount of setup when being created:

• Variable Name: This will be used inside the SQL query to represent the variable.
• Label: Pre-populated when the variable name is set. Shows as the filter label in the report.
• Help text: Optional. Description of the filter.
• Maximum number of items: Optional. Allows you to set a limit for the maximum number of items in a list input. If no input is given, the field defaults to 1000 items.

After the filter is created, the user can click on the empty list filter and enter list values into the pop up screen.

(please wait while video loading above)

 

Usage

When adding a list input into your query, round brackets are added around the filter value ({{ids}}):

SELECT id, name, bookings
FROM customer_bookings
WHERE [[ id::VARCHAR IN ({{ids}}) ]]`

Once run, the generated SQL would look like:

SELECT id, name, bookings
FROM customer_bookings
WHERE id::VARCHAR IN ('1', '2', '3', '4')`

To find out more about Holistic query syntax, please see Query Syntax References.

 

Text Filter

The text filter is the simplest filter type. It will accept free text inputs from users and only requires the following fields to be set up:

• Variable Name: This will be used inside the SQL query to represent the variable.
• Label: Pre-populated when the variable name is set. Shows as the filter label in the report.
• Default Value: Optional. The filter defaults to this value if no filter value is set by user.
• Help text: Optional. Description of the filter.

 

 

Note: When comparing text filters with database values, it is important to identify differences in lower and uppercase letters. To avoid issues, it is recommended to wrap the text input field and the table name inside LOWER( ) tags:

[[ LOWER(U.realname) LIKE CONCAT('%', LOWER({{name}}), '%') ]]
AND [[ LOWER(U.email) LIKE CONCAT('%', LOWER({{email}}), '%') ]

To find out more about how to include the text filter in your SQL query, please see Query Syntax References.

 

Separator Filter

The separator filter adds a line break between filters so that subsequent filters are shown on a new row. This helps you organize your filters and makes the report look cleaner. To create a new separator filter, only two optional fields are available:

• Variable Name: Optional. Name of filter.
• Label: Optional. Pre-populated when the variable name is set.

Once the separator filter is set up, a horizontal line breaks up the Filter section so that new filters are created into a new row:

 

Shared Filters

Shared Filters allow you to create filters that can be used across multiple reports, thus saving you time from creating the same filter multiple times. The range of filters available is the same as for the report specific ad-hoc filters, which are explained previously on this page, see Report Filters.

To create, edit or manage your shared filters, go to the “Shared Filters” page on the left-side menu. You can select a pre-existing filter from the list, or create a new one by clicking “New Shared Filter”. You will be able to add in a name for your shared filter and select the type of filter you wish to create. For aid on any of the filter types, please refer to its specific section earlier on this page. With shared filters, you will also be able to review the filter before saving.

 

Another way to create a shared filter is to convert a report specific ad-hoc filter into a shared filter. You can do this by opening the dropdown menu on the filter's navigation bar, and selecting "Convert to shared filter".

 

To add a shared filter to your report or dashboard, select “Add Shared Filter” from your Filter section and select the saved filter from the dropdown menu.

Once the filter has been added to the report or dashboard, you can add it as a variable into your query, as with any other filter. When a shared filter is included into a report, a small S symbol appears next to it, indicating that it is shared by others. To see how to include new filters into your SQL query, please see Query Syntax References.