Jinja Templates is a special language for creating templates. It allows you to write dynamic SQL queries that change depending on the specified conditions. Use templates in virtual datasets of the BI Builder. For example, add a dynamic date range that will request data only for a selected period.
In this article, we'll show you how to add a Jinja template to a virtual dataset and consider the main variables for templates.
Create a virtual dataset
Open the BI Builder and go to SQL > SQL Lab. Write an SQL query on the right and save it as a new dataset.
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
To add a template, open the Datasets tab, select a virtual dataset, and click Edit.
Edit your SQL query and add the 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;
-
{%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 the query more dynamic. You can change the time range of the data set using a filter. Just put different values in from_dttm and to_dttm without editing the rest of the query.
Variables in Jinja templates
| Variable | Description |
|---|---|
| portal_url |
Shows the Bitrix24 account address from which BI Builder opens. For example, add the string SELECT '{{portal_url()}}' as string to the query to see in the dashboard which account BI Builder is opened from.
|
| columns | Defines the columns to group the data in a query. |
| filter | Specifies the filters applied to the data. |
| from_dttm and to_dttm | Sets the time range of data sampling. |
| groupby | Repeats the columns function, grouping data by specified columns. |
| metrics | Includes aggregate functions such as sum or average. |
| row_limit and row_offset | Limits the number of rows in a query result and the offset of the start of the selection. |
| table_columns | Lists the available columns in the dataset. |
| time_column and time_grain | Defines the time column and data aggregation interval. |
Check how the template works
Click on the dataset and create a chart to test your query. Select the Raw Records query mode, move the metrics to the Columns block, set the filter, and click Create chart.
To see how the template works in your query, click the three dots (...) button > View query.
The dynamic date range is applied to the chart, requesting data only for a selected period.
In brief
-
Jinja Templates is a special language for creating templates. You can use it to write dynamic SQL queries that change depending on the specified conditions.
-
To add a Jinja template, edit the SQL query in a new or existing dataset.