In advanced use cases, you can use SQL parameters for filtering native queries in dashboards.
Creator has the flexible ability to allow variables in native (SQL) queries. This lets you dynamically replace values in your queries using filter widgets or through the query’s URL.
Options and settings for your variables will appear in the Variables side panel of the native query builder once you’ve defined a variable. So, how do you define a variable?
➡️ Defining variables
Typing {{variable_name}} in your native query creates a variable called variable_name. Variables can be given types in the side panel, which changes their behavior. All variable types other than “Field Filter” will cause a filter widget to be placed on this question corresponding to the chosen variable type. When a value is selected via a filter widget, that value replaces the corresponding variable in the SQL template, wherever it appears. If you have multiple filter widgets, you can click and drag on any of them to move and reorder them.
This example defines a variable called cat, allowing you to dynamically change the WHERE clause in this query:
SELECT count(*)
FROM products
WHERE category = {{cat}}
➡️ The Field Filter variable type
Setting a variable to the “Field Filter” type allows you to map it to a field in any table in the current database, and lets you display a dropdown filter widget filled with the values of the field you connected it to. Field filter variables also allow you to connect your SQL question to a dashboard filter if you put it in a dashboard.
A field filter variable inserts SQL similar to that generated by the GUI query builder when adding filters on existing columns. This is useful because it lets you do things like insert dynamic date range filters into your native query. When adding a field filter, you should link that variable to a specific column. Field filter variables should be used inside of a WHERE clause in SQL, or a $match clause in MongoDB.
Note: Table aliases are not supported, because field filters generate SQL based on the mapped field. Some databases may also require the schema in the FROM clause. An example for Oracle would be FROM "schema"."table". In BigQuery, back ticks are needed, like FROM `dataset_name.table`. See the below example:
SELECT count(*)
FROM products
WHERE {{date_var}}
➡️ Creating SQL question filters using field filter variables
First, insert a variable tag in your SQL, like {{my_var}}. Then, in the side panel, select the “Field Filter” variable type, and choose which field to map your variable to. In order to display a filter widget, you’ll have to choose a field whose “Type” in the “Data Model” section of the Admin Panel is one of the following:
- Category
- City
- Entity Key
- Entity Name
- Foreign Key
- State
- UNIX Timestamp (Seconds)
- UNIX Timestamp (Milliseconds)
- ZIP or Postal Code
The field can also be a datetime, which can be left as “No special type” in the data model.
You’ll then see a dropdown labeled “Widget,” which will let you choose the kind of filter widget you want on your question, which is especially useful for datetime fields. You can select “None” if you don’t want a widget on the question at all, which you might do, for example, if you just want to allow this question to be mapped to a dashboard filter (see more on that below).
Note: If you’re not seeing the option to display a filter widget, make sure the mapped field is set to one of the above types. If it's still not playing nice, get in touch with our support team to manually re-sync the cached values for that field.
Filter widgets can’t be displayed if the variable is mapped to a field marked as:
- Avatar Image URL
- Description
- Enum
- Field containing JSON
- Image URL
- Number
- Latitude
- Longitude
- URL
➡️ Setting a default value
If you input a default value for your field filter, this value will be selected in the filter whenever you come back to this question. If you clear out the filter, though, no value will be passed (i.e., not even the default value). The default value has no effect on the behavior of your SQL question when viewed in a dashboard.
➡️ Default value in the query
You can also define default values directly in your query, which are useful when defining complex default values. Note that the hash (#) might need to be replaced by the comment syntax of the database you’re using. Some databases use double-dashes (--) as comment syntax.
Current example for a Date filter:
SELECT p.*
FROM products p
WHERE p.createdAt = [[ {{dateOfCreation}} #]]CURRENT_DATE()
➡️ Connecting a SQL question to a dashboard filter
In order for a saved SQL/native question to be usable with a dashboard filter, it must contain at least one field filter. The kind of dashboard filter that can be used with the SQL question depends on the field that you map to the question’s field filter(s). For example, if you have a field filter called and you map it to a State field, you can map a Location dashboard filter to your SQL question. In this example, you’d create a new dashboard or go to an existing one, click the “Edit” button, add the SQL question that contains your State field filter, add a new dashboard filter or edit an existing Location filter, then click the dropdown on the SQL question card to see the State field filter.
➡️ Optional Clauses
To make a clause optional in your native query, type [[brackets around a {{variable}}]]. If variable is given a value, then the entire clause is placed into the template. If not, then the entire clause is ignored.
In this example, if no value is given to cat from its filter widget or URL, then the query will just select all the rows from the products table. But if cat does have a value, like Widget, then the query will only grab the products with a category type of Widget:
SELECT count(*)
FROM products
[[WHERE category = {{cat}}]]
To use multiple optional clauses you must include at least one regular WHERE clause followed by optional clauses, each starting with AND.
Example:
SELECT count(*)
FROM products
WHERE True
[[AND id = {{id}}]]
[[AND category = {{category}}]]
When using a field filter, the column name should not be included in the SQL. Instead, the variable should be mapped to a field in the side panel.
Example:
SELECT count(*)
FROM products
WHERE True
[[AND {{id}}]]
[[AND {{category}}]]
🤔 Need further support?
We're ready to help anytime. Reach out at help@intellischool.co.