A dataset contains information about workflows, SPAs, employees, 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 workflows, how to merge them, and how to create charts.
Datasets:
- bizproc_task — workflow assignments
- bizproc_workflow_state — running workflows
- bizproc_workflow_template — workflow templates
Examples:
Choose a dataset
To analyze information in a dashboard, choose a suitable dataset and create a chart.
bizproc_task — workflow assignments. This dataset contains workflow assignment data. Use this dataset to track assignment progress.
bizproc_task
| Field | Description | Data type |
|---|---|---|
| ID | Assignment ID | Number |
| CODE_ID | Assignment type code | String |
| CODE_NAME | Assignment type name | String |
| CODE | Assignment type code and name | String |
| WORKFLOW_ID | ID of the workflow where the assignment was created | String |
| NAME | Assignment name | String |
| CREATED_DATE | Date and time the assignment was created | Date and time |
| MODIFIED | Date and time the assignment was last modified | Date and time |
| DURATION | Assignment completion time, sec | Number |
| APPROVE_TYPE | Assignment approval type code | String |
| APPROVE_TYPE_NAME | Assignment approval type name | String |
| STATUS_ID | Assignment status code | Number |
| STATUS_NAME | Assignment completion status name | String |
| STATUS | Assignment completion status code and name | String |
| USER_ID | ID of the assignment participant | Number |
| USER_NAME | Name of the assignment participant | String |
| USER | ID and name of the assignment participant | String |
| USER_STATUS_ID | Assignment participant status code | Number |
| USER_STATUS_NAME | Assignment participant status name | String |
| USER_STATUS | Assignment participant status code and name | String |
bizproc_workflow_state — running workflows. This dataset contains data about running workflows: statuses, completion time, and the users who started or edited them. Use this dataset to track when workflows started and monitor their progress.
bizproc_workflow_state
| Field | Description | Data type |
|---|---|---|
| ID | ID of the running workflow | String |
| STARTED_BY_ID | ID of the user who started the workflow | Number |
| STARTED_BY_NAME | Name of the user who started the workflow | String |
| STARTED_BY | ID and name of the user who started the workflow | String |
| STARTED | Date and time the workflow started | Date and time |
| COMPLETED | Shows whether the workflow is completed: Y — yes, N — no |
String |
| DOCUMENT_ID | ID of the document the workflow was started for | String |
| DURATION | Workflow completion time, sec | Number |
| START_DURATION | Time spent starting the workflow | Number |
| WORKFLOW_TEMPLATE_ID | ID of the workflow template | Number |
| WORKFLOW_TEMPLATE_NAME | Name of the workflow template | String |
| MODULE_ID | Code of the module where the workflow was started | String |
| MODULE_ID_NAME | Name of the module where the workflow was started | String |
| ENTITY_ID | Code of the item type the workflow was started for | String |
| ENTITY_ID_NAME | Name of the item type the workflow was started for | String |
| MODIFIED_BY_ID | ID of the user who last updated the workflow template | Number |
| MODIFIED_BY_NAME | Name of the user who last updated the workflow template | String |
| MODIFIED_BY | ID and name of the user who last updated the workflow template | String |
bizproc_workflow_template — workflow templates. This dataset contains workflow template data. Use this dataset to see which templates are used most often.
bizproc_workflow_template
| Field | Description | Data type |
|---|---|---|
| ID | Workflow template ID | Number |
| WORKFLOW_TEMPLATE | Workflow template ID and name | String |
| MODULE_ID | ID of the module that contains the workflow template | String |
| ENTITY | Name of the item associated with the workflow template | String |
| DOCUMENT_TYPE | Type of document associated with the workflow template | String |
| DOCUMENT_STATUS | Workflow template status | String |
| NAME | Workflow template name | String |
| MODIFIED | Date and time the workflow template was last modified | Date and time |
| IS_MODIFIED | Shows whether the workflow template was modified: Y — yes, N — no |
String |
| USER_ID | ID of the user who added the workflow template | Number |
| USER_NAME | Name of the user who added the workflow template | String |
| USER | ID and name of the user who added the workflow template | String |
| SYSTEM_CODE | System code of the template that distinguishes standard templates from templates created by applications | String |
| IS_SYSTEM | Shows whether the template is a system template: Y — yes, N — no. Only templates with the value N are included in the dataset |
String |
| ACTIVE | Shows whether the template is active: Y — yes, N — no |
String |
| TYPE | Template type code. Only templates with the value default are included in the dataset |
String |
Create a chart
Add information from a dataset to charts and track workflow progress. Charts show which workflows are running and their current stages.
- 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 active workflow assignments and their current stages 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 two datasets: workflow assignments (bizproc_task) and running workflows (bizproc_workflow_state). You can copy it to see how the query works. The query selects assignments in active workflows and shows their IDs, names, statuses, and participant names.
SELECT
bizproc_task.ID AS "Assignment ID",
bizproc_task.NAME AS "Assignment name",
bizproc_task.USER_NAME AS "Participant name",
bizproc_task.STATUS_NAME AS "Assignment status",
bizproc_workflow_state.STARTED AS "Workflow start date and time",
bizproc_workflow_state.STARTED_BY_NAME AS "Started by user"
FROM
bizproc_task
INNER JOIN
bizproc_workflow_state
ON bizproc_task.WORKFLOW_ID = bizproc_workflow_state.ID
WHERE
bizproc_workflow_state.COMPLETED = 'N'
SELECT determines which information to extract from the dataset. For example, the query SELECT bizproc_task.ID AS "Assignment ID":
- Selects the assignment ID
IDfrom thebizproc_taskdataset - Assigns it the name
Assignment ID - Displays the result in a separate table column
FROM specifies the main dataset that the data comes from. The query gets data from bizproc_task, a table with workflow assignment information.
INNER JOIN links the bizproc_task and bizproc_workflow_state tables. The query connects assignments to the workflows where they were created. INNER JOIN keeps only assignments that have a matching running workflow.
WHERE filters the results and shows only assignments in incomplete workflows. The condition bizproc_workflow_state.COMPLETED = 'N' keeps active workflows in the selection. To include completed workflows, remove this condition.
In brief
- A dataset contains information about workflows, SPAs, employees, and other Bitrix24 items.
- Each item has several datasets that automatically receive data from system and custom fields.
- Workflows have three datasets: workflow assignments (bizproc_task), running workflows (bizproc_workflow_state), and workflow templates (bizproc_workflow_template).
- Use datasets to create charts and analyze business efficiency metrics. Charts show assignment progress, active workflows, and which templates your company uses.
- Merge datasets to add information about active assignments and workflow progress to a report. Write an SQL query, save the result as a dataset, and create a chart.