Jinja Templates is a template language you can use to build dynamic SQL queries. These queries adjust based on conditions you define. Use Jinja templates in virtual datasets in BI Builder. For example, you can set a dynamic date range to return data only for a selected period.
In this article, you’ll learn how to add a Jinja template to a virtual dataset and how to use key template variables.
Create a virtual dataset
- Open BI Builder.
- Go to SQL > SQL Lab.
- Write your SQL query.
- Save it as a new dataset.
Use this dataset as the base for adding templates.
Create a virtual dataset
In the screenshot above, we use an SQL query to get data on deals and employees for 2023.
Edit a virtual dataset
- Open the Datasets tab.
- Select a virtual dataset.
- Click Edit.
Update your SQL query and add a Jinja template:
WHERE {% if from_dttm is not none %} date_create >= from_iso8601_timestamp('{{ from_dttm }}') AND {% endif %} {% if to_dttm is not none %} date_create < from_iso8601_timestamp('{{ to_dttm }}') AND {% endif %} true;
How this works:
-
{%if from_dttm is not none %} checks if there is a start date for filtering.
-
date_create >= from_iso8601_timestamp('{{ from_dttm }}') AND filters data to include records with the creation date starting from the specified start date.
-
{%endif %} completes the start date check.
-
{%if to_dttm is not none %} checks if there is an end date for filtering.
-
date_create < from_iso860i_timestamp('{{ to_dttm }}') AND filters data to include records with the creation date before the specified end date.
-
{%endif %} completes the end date check.
-
true supports the operation of the query if no dates are specified.
Template variables make your query flexible. You can change the date range by setting values for from_dttm and to_dttm. You don’t need to edit the query itself.
Variables in Jinja templates
| Variable | Description |
|---|---|
| portal_url |
Returns the Bitrix24 account URL where BI Builder runs. Example: SELECT '{{portal_url()}}' as string shows the account in your dashboard.
|
| columns | Sets the columns used to group data in the query. |
| filter | Applies filters to the dataset. |
| from_dttm and to_dttm | Define the time range for the data. |
| groupby | Repeats the columns function, grouping data by specified columns. |
| metrics | Adds aggregate functions, such as sum or average. |
| row_limit and row_offset | Control how many rows to return and where to start. |
| table_columns | Lists all available columns in the dataset. |
| time_column and time_grain | Set the time field and the aggregation interval. |
Test the template
- Open the dataset.
- Create a chart.
- Select Raw Records as the query mode.
- Move metrics to the Columns section.
- Set a filter.
- Click Create chart.
To review how the template works in the query:
- Click the three dots (...) menu.
- Select View query.
The chart will reflect the selected date range. The query returns only the data within that period.
In brief
- Jinja Templates let you create dynamic SQL queries.
- Add a template by editing the SQL query in a dataset.
- Use variables like
from_dttmandto_dttmto control filters without changing the query.