Read FAQ
Bitrix24 Support
Registration and Authentication
How to start
My Profile
Chats and Calls
Tasks and Projects
CoPilot - AI in Bitrix24
Contact Center
Sales Center
CRM Analytics (beta)
BI Builder
Sales Intelligence
Inventory Management
Online Store (beta)
CRM + Online Store
CRM Store (beta)
Knowledge base (beta)
Mobile App
Desktop App
General questions
Bitrix24 On-Premise


Create a chart based on a virtual dataset

Explore how to create a virtual dataset in which you can combine data on deals, products, and custom fields.

Open the BI Builder and go to SQL > SQL Lab.
Learn how to create dashboards in BI Builder

Fill in the following parameters:

  • The database is trino.

  • The scheme is bitrix24.

  • In the Table, select crm_deal.

Write an SQL query on the right. It performs four actions:

  1. d.TITLE selects the deal name from crm_deal.

  2. du.UF_CRM_1702288663142 selects a specific custom field from crm_deal_uf. Specify the field ID from CRM. To view it, open a deal, right-click the field, and click Inspect.

  3. dp.PRODUCT_NAME selects the product name from crm_deal_product_row.

  4. INNER JOIN connects tables by the common deal identifier DEAL_ID.

SELECT d.TITLE, du.UF_CRM_1702288663142, dp.PRODUCT_NAME FROM crm_deal d INNER JOIN crm_deal_uf du ON d.ID = du.DEAL_ID INNER JOIN crm_deal_product_row dp ON d.ID = dp.DEAL_ID; 

As a result, you'll get a unified dataset presenting the name of each deal, selected custom field values, and the added product names. When saving the dataset, click Save as new and give it a specific name.

To make it clear what data is shown in the chart, edit the column names. Open the Datasets section and click Edit next to your dataset.

Open the Columns tab and specify new column names using the Label field.

Then open the Charts section and click + Chart to add this data to your dashboard.

Pick your virtual dataset, specify how to present the information, and click Create new chart.

Select the Raw Records query mode, move the metrics to the Columns block on the left, and click Create chart.

To save the chart, specify its name, select the dashboard, and click Save.

After saving the changes, go back to Bitrix24 and reopen the report.

In brief

  • Use the BI Builder to perform an SQL query and combine information from different sources into a virtual dataset.

  • Compose a query that selects specific information from various data sources, for example, deal name, linked products, and certain custom fields.

  • To make it clear what data is shown in the chart, edit the column names.

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