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:
- crm_dynamic_items — SPA
- crm_entity_stage_history — SPA item stage history
- crm_dynamic_items_prod — products in SPAs
- crm_automated_solution — SPAs in automated solutions
- crm_smart_proc — Smart Process Automation
- crm_entity_relation — links between SPAs and CRM items
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.
- Open BI Builder > Charts.
- Click + Chart in the top-right corner.
- Choose a dataset from the list. If you do not see the dataset you need, click Add a dataset.
Create a dataset - Choose a chart type — the format for viewing the information, such as a card, table, line chart, or another option.
- Click Create new chart.
Merge datasets
Merge datasets to add information about the products used in SPA items to a report.
- Open BI Builder > SQL > SQL Lab.
- Make sure
bitrix24is selected in the Schema field. - Enter the SQL query and click Run.
- 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
IDfrom thecrm_dynamic_items_1036dataset - 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.