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: e-Signature and e‑Signature for HR

A dataset contains information about signed documents, e-Signature for HR, invoices, 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 e-Signature and e-Signature for HR, 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_36 — customer documents (e-Signature). This dataset contains data from system fields of documents created for signing. Use this dataset to track statuses, deadlines, and how documents move through stages.

crm_dynamic_items_36

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

crm_dynamic_items_39 — HR documents (e-Signature for HR). This dataset contains data from system fields of HR documents. Use this dataset to track statuses, signing deadlines, and how HR documents move through stages.

crm_dynamic_items_39

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

Create a chart

Add information from a dataset to charts and track document stages and responsible employees. Charts show how many documents are signed and who handles them.

  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 HR documents and customer documents for signing into one table to see which documents a specific employee created.

  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 documents from two datasets: HR documents (crm_dynamic_items_39) and customer documents (crm_dynamic_items_36). You can copy it to see how the query works. The query shows all documents created by a specific employee. Replace 374 with the employee ID before running the query.

SELECT
    'e-Signature for HR' AS "Document type",
    crm_dynamic_items_39.ID AS "Document ID",
    crm_dynamic_items_39.TITLE AS "Document name",
    crm_dynamic_items_39.CREATED_BY_NAME AS "Created by",
    crm_dynamic_items_39.CREATED_TIME AS "Created on"
FROM crm_dynamic_items_39
WHERE crm_dynamic_items_39.CREATED_BY_ID = 374

UNION ALL

SELECT
    'e-Signature' AS "Document type",
    crm_dynamic_items_36.ID AS "Document ID",
    crm_dynamic_items_36.TITLE AS "Document name",
    crm_dynamic_items_36.CREATED_BY_NAME AS "Created by",
    crm_dynamic_items_36.CREATED_TIME AS "Created on"
FROM crm_dynamic_items_36
WHERE crm_dynamic_items_36.CREATED_BY_ID = 374

ORDER BY "Created on" DESC;

SELECT determines which information to extract from the dataset. For example, SELECT 'e-Signature for HR' AS "Document type" adds the value e-Signature for HR and names the column Document type.

FROM specifies the dataset that the data comes from. In the first part of the query, it is crm_dynamic_items_39 with HR document data.

WHERE filters documents created by a specific employee. The condition CREATED_BY_ID = 374 appears in both parts of the query, so the result includes only that employee's e-Signature for HR and e-Signature documents.

UNION ALL combines the results of the two queries into one table. Each document appears as a separate row: HR documents are not linked to customer documents for signing.

ORDER BY sorts the combined list by creation date in descending order.


In brief

  • A dataset contains information about signed documents, e-Signature for HR, and other Bitrix24 items.
  • Each item has several datasets that automatically receive data from system and custom fields.
  • There are two datasets for documents: customer documents (crm_dynamic_items_36) and HR documents (crm_dynamic_items_39).
  • Use datasets to create charts and analyze metrics, such as signed documents and processing times.
  • Merge datasets to see which documents a specific employee created. 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