Learn how to build a virtual dataset in BI Builder. You can combine data from deals, products, and custom fields, then use it in a chart.

Open BI Builder and go to SQL > SQL Lab.
Create dashboards in BI Builder

Set up parameters

Configure the following:

• Database: trino
• Schema: bitrix24
• Table: crm_deal

Write the SQL query

Add your query in the editor. This query does four things:

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

2. du.UF_CRM_1702288663142 returns a custom field from crm_deal_uf. Replace this with your field ID from CRM. To find it, open a deal, right-click the field, and click Inspect.

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

4. INNER JOIN links the tables using the shared 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; 

Save the dataset

1. Click Save as new.
2. Enter a clear name for the dataset.

Rename columns

Make the dataset easier to read:

1. Go to Datasets.
2. Click Edit next to your dataset.
3. Open the Columns tab.
4. Update column names in the Label field.

Create a chart

1. Open the Charts section.
2. Click + Chart.
3. Select your virtual dataset.
4. Choose how to display the data.
5. Click Create new chart.

Configure the chart

1. Select Raw Records as the query mode.
2. Move metrics to the Columns panel.
3. Click Create chart.

Save the chart

1. Enter a name for the chart.
2. Select a dashboard.
3. Click Save.

View the result

1. Return to Bitrix24.
2. Reopen the report to see the updated dashboard.