Bitrix24Care

Using Jinja Templates in BI Builder

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

Save 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.

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.

Was this information helpful?
Integration specialist assistance
That's not what I'm looking for
Complicated and incomprehensible text
The information is outdated
It's too short. I need more information
I don't like the way this tool works
Go to Bitrix24
Don't have an account? Create for free