Read FAQ
NEW
Bitrix24 Support
Registration and Login
Bitrix24 Security
Plans and Payments
AI in Bitrix24
Alaio Vibecode
Employee Widget
Booking
Contact Center
Sales Center
Analytics
BI Builder
e-Signature
Knowledge base
Telephony
Bitrix24 Messenger
General questions
Past updates

Bitrix24 Helpdesk

BI Builder datasets: Smart Process Automation

A dataset contains information about leads, deals, companies, and other Bitrix24 items. Each item has several datasets that automatically receive data from system and custom fields. Use datasets to create charts and analyze business efficiency metrics.

In this article, you'll learn about datasets for Smart Process Automation (SPA), how to merge them, and how to create charts.

Datasets:

Examples:


Choose a dataset

To analyze information in a dashboard, choose a suitable dataset and create a chart.

crm_dynamic_items — SPA. This dataset contains data from the SPA's system and custom fields. Use this dataset to see how many items were created during a month and which employees worked with them.

Each SPA has a separate dataset. When you create an SPA, Bitrix24 adds its type ID to the dataset name, for example crm_dynamic_items_1032. In the dataset list, it appears with the SPA name, for example SPA: Invoice reconciliation.
Add multiple SPAs to a Power BI report

crm_dynamic_items

Field Description Data type
ID SPA item ID Number
XML_ID External ID for linking to external systems String
TITLE SPA item name String
CREATED_BY ID and name of the employee who created the item String
CREATED_BY_ID ID of the employee who created the item Number
CREATED_BY_NAME Name of the employee who created the item String
UPDATED_BY ID and name of the employee who last updated the item String
UPDATED_BY_ID ID of the employee who last updated the item Number
UPDATED_BY_NAME Name of the employee who last updated the item String
MOVED_BY ID and name of the employee who changed the item's stage String
MOVED_BY_ID ID of the employee who changed the item's stage Number
MOVED_BY_NAME Name of the employee who changed the item's stage String
CREATED_TIME Date and time the item was created Date and time
UPDATED_TIME Date and time the item was last updated Date and time
MOVED_TIME Date and time the item's stage was changed Date and time
CATEGORY ID and name of the item's pipeline String
CATEGORY_ID ID of the item's pipeline Number
CATEGORY_NAME Name of the item's pipeline String
OPENED Shows whether the item is available to everyone: Y — yes, N — no String
STAGE Code and name of the item's current stage String
STAGE_ID Code of the item's current stage String
STAGE_NAME Name of the item's current stage String
PREVIOUS_STAGE_ID Code of the item's previous stage String
BEGINDATE Date and time the event or process started Date and time
CLOSEDATE Date and time the event or process ended Date and time
COMPANY ID and name of the company linked to the item String
COMPANY_ID ID of the company linked to the item Number
COMPANY_NAME Name of the company linked to the item String
CONTACT ID and name of the contact linked to the item String
CONTACT_ID ID of the contact linked to the item Number
CONTACT_NAME Name of the contact linked to the item String
CONTACTS IDs and names of all contacts linked to the item String
OPPORTUNITY Expected amount Number
IS_MANUAL_OPPORTUNITY Shows whether the expected amount was entered manually: Y — yes, N — no String
TAX_VALUE Tax amount Number
CURRENCY_ID Currency code of the expected amount String
OPPORTUNITY_ACCOUNT Expected amount in the reporting currency Number
TAX_VALUE_ACCOUNT Tax amount in the reporting currency Number
ACCOUNT_CURRENCY_ID Reporting currency code String
MYCOMPANY ID and name of your company String
MYCOMPANY_ID ID of your company Number
MYCOMPANY_NAME Name of your company String
SOURCE Code and name of the source String
SOURCE_ID Source code String
SOURCE_NAME Source name String
SOURCE_DESCRIPTION Source description String
ASSIGNED_BY ID and name of the employee responsible for the item String
ASSIGNED_BY_ID ID of the employee responsible for the item Number
ASSIGNED_BY_NAME Name of the employee responsible for the item String
WEBFORM_ID ID of the CRM form the item was created from Number
UF_CRM_"field number" Data from the custom field with the specified number, for example, UF_CRM_123456789 Depends on the field type

crm_entity_stage_history — SPA item stage history. This dataset contains data on items moving through stages: the start and end dates of a stage, the responsible employee, department, pipeline, stage, and stage type.

Use this dataset to analyze an SPA item's path and identify stages where work takes longer. For example, a supervisor can see that requests spend the most time in the approval stage, then check why employees do not process them on time.

crm_entity_stage_history

Field Description Data type
ID Stage history record ID Number
TYPE_ID Stage history record type Number
OWNER_TYPE_ID SPA type ID Number
OWNER_ID ID of the SPA item whose stage changed Number
DATE_CREATE Date and time the stage history record was created Date and time
START_DATE Start date of the period the item spent at the stage Date
END_DATE End date of the period the item spent at the stage Date
RESPONSIBLE_BY_ID ID of the employee responsible for the SPA item Number
RESPONSIBLE_BY_NAME Name of the employee responsible for the SPA item String
RESPONSIBLE_BY ID and name of the employee responsible for the SPA item String
RESPONSIBLE_BY_DEPARTMENT Path in the department hierarchy of the responsible employee String
CATEGORY_ID SPA pipeline ID Number
CATEGORY_NAME SPA pipeline name String
CATEGORY ID and name of the SPA pipeline String
STAGE_SEMANTIC_ID Stage type code String
STAGE_SEMANTIC Stage type, for example, "In progress" String
STAGE_ID Stage code String
STAGE_NAME Stage name String
STAGE Stage code and name String

crm_dynamic_items_prod — products in SPAs. This dataset contains product data. You can find product names, quantities, and prices for the selected SPA.

Each SPA has a separate product dataset. Its name includes the same SPA type ID, for example crm_dynamic_items_prod_1032.
Add multiple SPAs to a Power BI report

crm_dynamic_items_prod

Field Description Data type
ID Product or service record ID Number
ITEM_ID SPA item ID Number
PRODUCT ID and name of the product or service String
PRODUCT_ID ID of the product or service Number
PRODUCT_NAME Name of the product or service String
PRICE Price of the product or service Number
PRICE_EXCLUSIVE Price of the product or service excluding tax, with discount Number
PRICE_NETTO Price of the product or service excluding discounts and taxes Number
PRICE_BRUTTO Price of the product or service excluding discounts, with tax Number
QUANTITY Product or service quantity Number
DISCOUNT_TYPE Discount type code and name String
DISCOUNT_TYPE_ID Discount type code Number
DISCOUNT_TYPE_NAME Discount type name String
DISCOUNT_RATE Discount size Number
DISCOUNT_SUM Discount amount Number
TAX_RATE Tax rate Number
TAX_INCLUDED Shows whether tax is included in the price: Y — yes, N — no String
CUSTOMIZED Shows whether the product or service was manually modified after being added to the item: Y — yes, N — no String
MEASURE Unit of measurement code and name String
MEASURE_CODE Unit of measurement code Number
MEASURE_NAME Unit of measurement name String
SORT Sort order Number
PARENT Products section of the product or service String
SUPERPARENT Products section one level above String
SUPERSUPERPARENT Products section two levels above String

crm_automated_solution — SPAs in automated solutions. This dataset contains the names and IDs of an automated solution and its related SPAs.

You can find the automated solution ID in Automation > Automated solutions > All automated solutions, in the ID column.
Automated solutions: automate work of company departments

crm_automated_solution

Field Description Data type
ENTITY_TYPE_ID SPA type ID Number
TITLE SPA name String
DATASET_NAME SPA dataset name String
AUTOMATED_SOLUTION_DATASET_NAME Automated solution dataset name String
CUSTOM_SECTION_ID Automated solution ID Number
CUSTOM_SECTION_TITLE Automated solution name String

crm_smart_proc — Smart Process Automation. This dataset contains a list of all SPAs and automated solutions. It shows the ID and name of each process and solution.

crm_smart_proc

Field Description Data type
ENTITY_TYPE_ID SPA type ID Number
TITLE SPA name String
DATASET_NAME SPA dataset name String
AUTOMATED_SOLUTION_DATASET_NAME Automated solution dataset name. If the SPA is not added to a solution, this field shows CRM String
CUSTOM_SECTION_ID Automated solution ID Number
CUSTOM_SECTION_TITLE Automated solution name String
PRODUCT_DATASET_NAME SPA product dataset name String
CREATED_AT Date and time the SPA was created Date and time
USER_FIELDS SPA custom field codes String

crm_entity_relation — links between SPAs and CRM items. This dataset contains links between SPAs, new invoices, and other CRM items. For example, see which deals are linked to SPA items.

Standard links between CRM items, such as a deal and a company or a lead and a deal, are not stored in this dataset. They appear in the datasets of the corresponding CRM items.

crm_entity_relation

Field Description Data type
SRC_ENTITY_TYPE_ID Type ID of the linked item: lead — 1, deal — 2, contact — 3, company — 4, invoice — 31, estimate — 7, company details — 8 Number
SRC_ENTITY_ID ID of the linked item Number
SRC_ENTITY_DATASET_NAME Name of the linked item's dataset String
DST_ENTITY_TYPE_ID SPA type ID Number
DST_ENTITY_ID ID of the SPA item that the link points to Number
DST_ENTITY_DATASET_NAME Name of the SPA dataset that the link points to String
CREATED_TIME Date and time the link was created Date and time

Create a chart

Add information from a dataset to charts and track business metrics. Charts show which products sell more often and total sales volume.

  1. Open BI Builder > Charts.
  2. Click + Chart in the top-right corner.
  3. Choose a dataset from the list. If you do not see the dataset you need, click Add a dataset.
    Create a dataset
  4. Choose a chart type — the format for viewing the information, such as a card, table, line chart, or another option.
  5. Click Create new chart.

Merge datasets

Merge datasets to add information about the products used in SPA items to a report.

  1. Open BI Builder > SQL > SQL Lab.
  2. Make sure bitrix24 is selected in the Schema field.
  3. Enter the SQL query and click Run.
  4. Review the query results. To create a chart for a report, save the query as a dataset. The dataset receives data from the fields specified in the SQL query.
    Create a chart from a virtual dataset

If this is your first time merging datasets, see the sample query.

Example SQL query

This SQL query extracts data from four datasets: the SPA (crm_dynamic_items_1036), SPA products (crm_dynamic_items_prod_1036), product properties (crm_product_property), and product property values (crm_product_property_value). You can copy it to see how the query works.

The query selects SPA items with products whose names contain the word "coat" and shows their properties by color. Replace crm_dynamic_items_"type ID" and crm_dynamic_items_prod_"type ID" with your SPA datasets before running the query.

SELECT
    crm_dynamic_items_1036.ID AS "SPA item ID",
    crm_dynamic_items_1036.TITLE AS "SPA item name",
    crm_dynamic_items_prod_1036.PRODUCT_ID AS "Product ID",
    crm_dynamic_items_prod_1036.PRODUCT_NAME AS "Product name",
    crm_dynamic_items_prod_1036.PRICE AS "Product price",
    crm_product_property.NAME AS "Property name",
    crm_product_property_value.VALUE_NAME AS "Property value"
FROM
    crm_dynamic_items_1036
LEFT JOIN
    crm_dynamic_items_prod_1036 ON crm_dynamic_items_prod_1036.ITEM_ID = crm_dynamic_items_1036.ID
LEFT JOIN
    crm_product_property_value ON crm_product_property_value.PRODUCT_ID = crm_dynamic_items_prod_1036.PRODUCT_ID
LEFT JOIN
    crm_product_property ON crm_product_property.ID = crm_product_property_value.PROPERTY_ID
WHERE
    crm_dynamic_items_prod_1036.PRODUCT_NAME LIKE '%coat%'
    AND crm_product_property.NAME = 'Color';

SELECT determines which information to extract from the dataset. For example, the query crm_dynamic_items_1036.ID AS "SPA item ID":

  • Selects the SPA item ID ID from the crm_dynamic_items_1036 dataset
  • Assigns it the name SPA item ID
  • Displays the result in a separate table column

FROM specifies the main dataset that the data comes from. The query gets data from crm_dynamic_items_1036, a table with information about SPA items.

LEFT JOIN links the crm_dynamic_items_1036 table to crm_dynamic_items_prod_1036 by SPA item ID. The query connects each SPA item to its products. LEFT JOIN keeps SPA items even if they have no linked products or properties.

LEFT JOIN links the crm_dynamic_items_prod_1036 table to crm_product_property_value by product ID. The query connects each product to its property values.

LEFT JOIN links the crm_product_property_value table to crm_product_property by property ID. The query connects property values to their names.

WHERE filters the results and shows SPA items that contain products with the word "coat" in their names, along with their property values by color. You can remove or replace the filters. For example, in crm_dynamic_items_prod_1036.PRODUCT_NAME LIKE '%coat%', replace "coat" to search for other products.


In brief

  • A dataset contains information about leads, deals, companies, and other Bitrix24 items.
  • Each item has several datasets that automatically receive data from system and custom fields.
  • SPAs have six datasets: SPA (crm_dynamic_items), SPA item stage history (crm_entity_stage_history), products in SPAs (crm_dynamic_items_prod), SPAs in automated solutions (crm_automated_solution), Smart Process Automation (crm_smart_proc), and links between SPAs and CRM items (crm_entity_relation).
  • Use datasets to create charts and analyze business efficiency metrics. Charts show sales volume and the products that sell more often.
  • Merge datasets to add information about the products used in SPA items to a report. Write an SQL query, save the result as a dataset, and create a chart.
Go to Bitrix24
Don't have an account? Create for free