A dataset contains information about tasks, projects, leads, deals, and other Bitrix24 items. Each item has several datasets that automatically receive information from system and custom fields. Use datasets to create charts and analyze business metrics.
In this article, you'll learn about datasets for tasks and projects, how to merge them, and how to create charts.
Datasets:
- task — tasks
- task_stages — task stages
- task_uf — task custom fields
- task_elapsed_time — task duration until completed
- flow — flows
- task_efficiency — task efficiency
- socialnetwork_group — projects
Examples:
Choose a dataset
To analyze information in a dashboard, choose a suitable dataset and create a chart.
task — tasks. This dataset contains data from task system fields, including names, statuses, deadlines, creators, assignees, participants, observers, stages, and dependent tasks. Use this dataset to assess task deadlines and how work is distributed among employees.
task
| Field | Description | Data type |
|---|---|---|
| ID | Task ID | Number |
| TITLE | Task name | String |
| DESCRIPTION | Task description, up to 128 characters | String |
| MARK | Rating from the creator: "Positive," "Negative," or "No rating" | String |
| PRIORITY | Task priority: high or normal | String |
| STATUS | Task status: "Pending," "In Progress," "Pending review," "Completed," or "Deferred" | String |
| GROUP_ID | ID of the workgroup linked to the task | Number |
| GROUP_NAME | Name of the workgroup linked to the task | String |
| GROUP_INFO | ID and name of the workgroup linked to the task | String |
| MULTITASK | Shows whether the task has multiple assignees: Y — yes, N — no |
String |
| STAGE_ID | ID of the stage the task is in | Number |
| STAGE_NAME | Name of the stage the task is in | String |
| STAGE | ID and name of the stage the task is in | String |
| CREATED_BY_ID | ID of the task creator | Number |
| CREATED_BY_NAME | Name of the task creator | String |
| CREATED_BY | ID and name of the task creator | String |
| CREATED_DATE | Date and time the task was created | Date and time |
| RESPONSIBLE_ID | ID of the task assignee | Number |
| RESPONSIBLE_NAME | Name of the task assignee | String |
| RESPONSIBLE | ID and name of the task assignee | String |
| ACCOMPLICES_IDS | IDs of task participants | String |
| ACCOMPLICES_NAMES | Names of task participants | String |
| ACCOMPLICES | IDs and names of task participants | String |
| AUDITORS_IDS | IDs of task observers | String |
| AUDITORS_NAMES | Names of task observers | String |
| AUDITORS | IDs and names of task observers | String |
| PARENT_ID | ID of the parent task | Number |
| CHANGED_BY_ID | ID of the employee who last modified the task | Number |
| CHANGED_BY_NAME | Name of the employee who last modified the task | String |
| CHANGED_BY | ID and name of the employee who last modified the task | String |
| CHANGED_DATE | Date and time the task was last modified | Date and time |
| STATUS_CHANGED_BY_ID | ID of the employee who last changed the task status | Number |
| STATUS_CHANGED_BY_NAME | Name of the employee who last changed the task status | String |
| STATUS_CHANGED_BY | ID and name of the employee who last changed the task status | String |
| STATUS_CHANGED_DATE | Date and time the task status was last changed | Date and time |
| CLOSED_BY_ID | ID of the employee who completed the task | Number |
| CLOSED_BY_NAME | Name of the employee who completed the task | String |
| CLOSED_BY | ID and name of the employee who completed the task | String |
| CLOSED_DATE | Date and time the task was completed | Date and time |
| ACTIVITY_DATE | Date and time of the last event in the task | Date and time |
| DATE_START | Date and time work on the task started | Date and time |
| DEADLINE | Task deadline | Date and time |
| START_DATE_PLAN | Planned date and time to start work on the task | Date and time |
| END_DATE_PLAN | Planned date and time to complete work on the task | Date and time |
| ALLOW_CHANGE_DEADLINE | Shows whether the assignee can change the task deadline: Y — yes, N — no |
String |
| ALLOW_TIME_TRACKING | Shows whether task time tracking is enabled: Y — yes, N — no |
String |
| TASK_CONTROL | Shows whether the task requires a review after completion: Y — yes, N — no |
String |
| ADD_IN_REPORT | Shows whether the task is added to the daily plan: Y — yes, N — no |
String |
| TIME_ESTIMATE | Planned task completion time, in seconds | Number |
| MATCH_WORK_TIME | Shows whether the task duration automatically extends when its dates fall on weekends or holidays: Y — yes, N — no |
String |
| DURATION_PLAN | Planned duration of work on the task, in seconds | Number |
| DURATION_TYPE | Unit of measurement for the planned duration: "Second," "Minute," "Hour," "Day," "Week," "Month," or "Year" | String |
| CRM_TASK | ID and type of CRM items linked to the task. Supported types: L — lead, D — deal, C — contact, CO — company |
String |
| TAGS | Task tags | String |
| DEPENDS_ON | IDs of dependent tasks | Number |
| FLOW_ID | ID of the flow the task was added to | Number |
task_stages — task stages. This dataset contains data from task stage system fields, including names, sort order, colors, and related projects. Use this dataset to analyze task stages within projects.
task_stages
| Field | Description | Data type |
|---|---|---|
| ID | Task stage ID | Number |
| TITLE | Task stage name | String |
| SORT | Stage sort order | Number |
| COLOR | Stage color | String |
| GROUP_ID | ID of the project linked to the stage | Number |
| GROUP_NAME | Name of the project linked to the stage | String |
| GROUP_INFO | ID and name of the project linked to the stage | String |
task_uf — task custom fields. This dataset contains data from custom fields you created, such as delivery date and order number.
task_uf
| Field | Description | Data type |
|---|---|---|
| TASK_ID | Task ID | Number |
| TASK_CREATED_DATE | Date and time the task was created | Date and time |
| UF_CRM_TASK | Names of CRM items linked to the task | String |
| UF_MAIL_MESSAGE | ID of the email the task was created from | Number |
| UF_AUTO_"field number" | Value of the custom task field with the specified number. The number is assigned automatically when the field is created | String |
task_elapsed_time — task duration until completed. This dataset contains task time tracking data, including start dates, duration in seconds, and information about entry authors. Use this dataset to see who worked on tasks and for how long.
task_elapsed_time
| Field | Description | Data type |
|---|---|---|
| ID | Time tracking entry ID | Number |
| TASK_ID | Task ID | Number |
| USER_ID | ID of the time tracking entry author | Number |
| USER_NAME | Name of the time tracking entry author | String |
| USER | ID and name of the time tracking entry author | String |
| DATE_START | Date and time work on the task started | Date and time |
| ELAPSED_TIME | Time spent on the task, in seconds | Number |
| COMMENT_TEXT | Comment for the time tracking entry, up to 100 characters | String |
flow — flows. This dataset contains data from task flow system fields, including names, creators, administrators, task distribution settings, related projects, and tasks. Use this dataset to analyze task distribution and overdue tasks in flows.
flow
| Field | Description | Data type |
|---|---|---|
| ID | Flow ID | Number |
| NAME | Flow name | String |
| FLOW | ID and name of the flow | String |
| CREATOR_ID | ID of the employee who created the flow | Number |
| CREATOR_NAME | Name of the employee who created the flow | String |
| CREATOR | ID and name of the employee who created the flow | String |
| OWNER_ID | ID of the employee administering the flow | Number |
| OWNER_NAME | Name of the employee administering the flow | String |
| OWNER | ID and name of the employee administering the flow | String |
| PLANNED_COMPLETION_TIME | Planned task completion time in the flow, in seconds | Number |
| DISTRIBUTION_TYPE | Task distribution type in the flow: "Use queue," "Manually," or "Self-assign tasks" | String |
| HAS_TEMPLATE | Shows whether the flow has a task template: Y — yes, N — no |
String |
| ACTIVE | Shows whether the flow is active: Y — yes, N — no |
String |
| GROUP_ID | ID of the project the flow belongs to | Number |
| GROUP_NAME | Name of the project the flow belongs to | String |
| GROUP_INFO | ID and name of the project the flow belongs to | String |
| TASKS_IDS | IDs of tasks added to the flow | String |
| EXPIRED_TASKS_IDS | IDs of overdue tasks added to the flow | String |
task_efficiency — task efficiency. This dataset contains data about events related to task completion, including overdue tasks, remarks, and deadline changes. Use this dataset to track overdue tasks and changes to their deadlines.
task_efficiency
| Field | Description | Data type |
|---|---|---|
| ID | Event record ID | Number |
| TASK_ID | ID of the task linked to the event record | Number |
| DATETIME | Date and time the event record was created | Date and time |
| DATETIME_REPAIR | Date and time the deadline was changed for an overdue task | Date and time |
| IS_VIOLATION | Shows whether the event is a remark: Y — yes, N — no |
String |
This dataset contains data from system fields for workgroups, projects, and Scrum teams, including names, owners, members, types, activity, project start/end dates, and Scrum settings. Use this dataset to analyze project activity and membership.
socialnetwork_group
| Field | Description | Data type |
|---|---|---|
| ID | Workgroup ID | Number |
| SITE_ID | Code of the site the workgroup belongs to | String |
| NAME | Workgroup name | String |
| DESCRIPTION | Workgroup description | String |
| DATE_CREATE | Date and time the workgroup was created | Date and time |
| DATE_MODIFY | Date and time the workgroup was last modified | Date and time |
| OPENED | Shows whether the workgroup is public: Y — yes, N — no |
String |
| SUBJECT | Workgroup subject | String |
| OWNER_ID | ID of the workgroup owner | Number |
| OWNER_NAME | Name of the workgroup owner | String |
| OWNER | ID and name of the workgroup owner | String |
| KEYWORDS | Workgroup tags | String |
| MODERATORS_IDS | IDs of workgroup moderators | String |
| MEMBERS_IDS | IDs of workgroup members | String |
| TYPE | Workgroup type: group — workgroup, project — project, scrum — Scrum |
String |
| NUMBER_OF_MEMBERS | Number of workgroup members | Number |
| DATE_ACTIVITY | Date and time of the last activity in the workgroup | Date and time |
| CLOSED | Shows whether the workgroup is archived: Y — yes, N — no |
String |
| PROJECT | Shows whether the workgroup is a project: Y — yes, N — no |
String |
| PROJECT_DATE_START | Project start date and time | Date and time |
| PROJECT_DATE_FINISH | Project end date and time | Date and time |
| SCRUM_MASTER_ID | Scrum master ID | Number |
| SCRUM_MASTER_NAME | Scrum master name | String |
| SCRUM_MASTER | ID and name of the Scrum master | String |
| SCRUM_SPRINT_DURATION | Sprint duration for the Scrum team, in seconds | Number |
| SCRUM_TASK_RESPONSIBLE | Default task assignee for the Scrum team: A — creator, M — Scrum master |
String |
Create a chart
You can add information from a dataset to charts to track task completion. Charts show employee activity, project work stages, and whether deadlines are met.
- 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 an employee's overdue tasks to a report and see when deadlines were changed.
- 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 information 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: tasks (task) and efficiency of work on tasks (task_efficiency). You can copy it to see how the query works. It selects all overdue tasks for a specific assignee and shows the task name, deadline, and time of the last deadline change. Replace the assignee ID before running the query.
SELECT
task.ID AS "Task ID",
task.TITLE AS "Task name",
task.RESPONSIBLE_NAME AS "Assignee name",
task.DEADLINE AS "Task deadline",
task_efficiency.DATETIME_REPAIR AS "Last deadline change time"
FROM
task
LEFT JOIN
task_efficiency ON task_efficiency.TASK_ID = task.ID
WHERE
task.RESPONSIBLE_ID = 12345 -- Replace with the required assignee ID
AND task.DEADLINE < NOW();
SELECT determines which information to extract from the dataset. For example, the query SELECT task.ID AS "Task ID":
- Selects the task ID
IDfrom thetaskdataset - Assigns it the name
task ID - Displays the result in a separate table column
FROM specifies the main dataset that the data comes from. In this example, it is task, the table with the main task information.
LEFT JOIN joins the task table with task_efficiency. The query links tasks to the time their deadlines were changed. LEFT JOIN displays all tasks, even if no deadline change records exist.
WHERE filters the results and displays only overdue tasks for a specific assignee. To get data for another assignee, replace 12345 with the required ID. To view all overdue tasks regardless of assignee, remove the RESPONSIBLE_ID condition.
In brief
- A dataset contains information about tasks, projects, leads, deals, and other Bitrix24 items.
- Each item has several datasets that automatically receive data from system and custom fields.
- Datasets for tasks and projects: tasks (task), custom fields (task_uf), task duration until completed (task_elapsed_time), task stages (task_stages), task efficiency (task_efficiency), task flows (flow), and projects (socialnetwork_group).
- Use datasets to create charts and analyze metrics, such as time spent on a task, project status, and the number of completed tasks.
- Merge datasets to add information about overdue tasks, deadline changes, and other parameters to a report. Write an SQL query, save the result as a dataset, and create a chart.