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: Tasks and projects

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:

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

socialnetwork_group — projects. 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.

  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 an employee's overdue tasks to a report and see when deadlines were changed.

  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 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 ID from the task dataset
  • 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.
Go to Bitrix24
Don't have an account? Create for free