BI Builder datasets: Employees, company structure, and calls

Author: Stacy Smith

Last update: January 24, 2025.

Datasets contain information on employees, company structure, calls, and other items. Each item has multiple datasets that automatically include both standard and custom fields. Use these datasets to create charts and analyze business performance indicators.

In this article, we'll explore datasets for employees, company structure, and calls. Learn how to blend them and how to create charts.

Datasets:

Employees — user
Company structure — org_structure
Calls — telephony_call

Examples:

How to create a chart
How to blend datasets

Datasets

To start analyzing data on a dashboard, select a dataset and create a chart based on it.

Employees — user: This dataset provides information about employees, including their activity status, departments, and positions within the company. Use it to calculate the number of employees and analyze the company structure.

Employees — user

Field	Description	Data type
ID	Unique key	Number
ACTIVE	Shows whether the user is on staff ("Y") or dismissed ("N")	String
NAME	User name	String
DEPARTMENT	Department position in the company hierarchy is shown as `[ID] Company name / Company department`, starting with the root department, which is the company name.	String
DEPARTMENT_IDS	IDs of all departments the employee is in	String
DEPARTMENT_ID	ID of the department the employee is in	String
DEPARTMENT_NAME	Name of the department the employee is in	String
DEPARTMENT_ID_NAME	ID and name of the department the employee is in	String
DEP1	Name of the first level department in the structure	String
DEP2	Name of the second level department in the structure	String
DEP3	Name of the third level department in the structure	String
DEP1_ID	ID of the first level department in the structure	String
DEP2_ID	ID of the second level department in the structure	String
DEP3_ID	ID of the third level department in the structure	String
DEP1_N	ID and name of the first level department in the structure	String
DEP2_N	ID and name of the second level department in the structure	String
DEP3_N	ID and name of the third level department in the structure	String

Company structure — org_structure: This dataset contains data about company departments, such as their names, types, statuses, and links to other departments. You can view active and inactive departments, identify their supervisors, and track changes in the company structure.

Company structure — org_structure

Field	Description	Data type
ID	Department ID in the company structure	Number
ACTIVE	Shows whether the department is active ("Y") or not ("N")	String
NAME	Department name	String
ID_NAME	Department ID and name	String
TYPE	Type in the structure: DEPARTMENT - department, COMPANY - company, TEAM - cross-functional team	String
PARENT_ID	Parent department ID	Number
ID_PARENT_NAME	Parent department ID and name	String
HEAD_ID	Department supervisors' IDs	String

Calls — telephony_call: This dataset includes information about calls, such as agent IDs, customer phone numbers, and call types. You can analyze each call's duration and link calls to customers, deals, and leads.

Calls — telephony_call

Field	Description	Data type
CALL_ID	Unique key	String
PORTAL_USER_ID	Agent ID	Number
PORTAL_USER	Agent ID and name	String
PORTAL_USER_DEPARTMENT	Agent's department in the company structure	String
PORTAL_NUMBER	Agent's extension number linked with the call	String
PHONE_NUMBER	Customer's phone number	String
CALL_TYPE	Call type: 1 - outgoing, 2 - incoming, 3 - forwarded incoming, 4 - callback	Number
CALL_DURATION	Call duration in seconds	Number
CALL_START_TIME	Call start time	Date
CALL_STATUS_CODE	Call code	String
CALL_STATUS_CODE_ID	Call result code	String
CALL_STATUS_CODE_NAME	Call result description	String
CALL_STATUS_REASON	Call code description	String
RECORD_FILE_ID	Call recording file ID	Number
CALL_VOTE	Call rating (available only for internal calls)	Number
COST	Call cost	Number
COST_CURRENCY	Call currency	String
CRM_ENTITY_ID	ID of the CRM item to which the activity is bound	Number
CRM_ENTITY_TYPE	Type of CRM item to which the activity is bound, like lead or deal	String
CRM_ACTIVITY_ID	ID of the CRM activity created based on the call	Number
REST_APP_ID	External telephony integration application ID	Number
REST_APP_NAME	External telephony integration application name	String
TRANSCRIPT_PENDING	Shows whether the transcript is ready ("N") or still in progress ("Y")	String
TRANSCRIPT_ID	Call transcript ID	Number
REDIAL_ATTEMPT	Number of dial attempts	Number
COMMENT	Call comment	String

How to create a chart

Use datasets to create charts and track how many incoming and outgoing calls each employee has. The charts will display call trends and their average duration.

Go to the BI Builder and follow these steps:

Open the Charts tab.
Click + Chart.
Select the dataset from the list. If you don't see the one you need, click Add a dataset to create a new one.
How to create a dataset
Choose a format for showing the data: big number, table, line chart, or others.
Click Create new chart.

How to blend datasets

Combine data about calls and employees to see employees who made calls, their department, and how long each call lasted.

Go to the BI Builder and do the following:

Go to SQL > SQL Lab.
Select the bitrix24 scheme.
Enter the SQL query and click Run.

The new set includes information from the fields that you specified in the SQL query. You can save it as a dataset to create a chart for the dashboard.
Create a chart based on a virtual dataset

If you're blending datasets for the first time, refer to the sample query.

Example of an SQL query

Here's an SQL query to extract data from two datasets: employees (user) and calls (telephony_call). The query will show data about the employee's calls, including their name, department, and each call duration. Replace the employee ID with the needed one before running the query.

SELECT 
    NAME AS "User name",
    DEPARTMENT_NAME AS "Department name",
    CALL_DURATION AS "Call duration"
FROM 
    telephony_call
LEFT JOIN 
    user ON ID = PORTAL_USER_ID
WHERE 
    PORTAL_USER_ID = 5;

SELECT: Chooses which data to extract. For example, the SELECT NAME AS "User name" part does the following:

Selects the user name NAME from the user dataset
Assigns it the name User name
Displays the result in a separate column

FROM: Specifies the main table for the query, such as the telephony_call table containing information about calls.

LEFT JOIN: Connects the telephony_call table to user. The query connects calls to employees' data.

WHERE: Filters the query results to show only a specific employee's calls. The condition WHERE PORTAL_USER_ID = 5; shows calls made by the employee with that ID. To view calls from all employees, remove the WHERE condition.

In brief

Datasets contain information on employees, company structure, calls, and other items.
Each item has multiple datasets that automatically include both standard and custom fields.
There are three datasets for employees, company structure, and calls: employees (user), company structure (org_structure), and calls (telephony_calls).
Use datasets to create charts and track how many incoming and outgoing calls each employee has. The charts will display call trends and their average duration.
Blend datasets to find out which employees call more often and how long their calls last. To do this, write an SQL query, save the result as a dataset, and create a chart.

Was this information helpful?

Integration specialist assistance

That's not what I'm looking for

Complicated and incomprehensible text

The information is outdated

It's too short. I need more information

I don't like the way this tool works

Go to Bitrix24

Don't have an account? Create for free

Bitrix24 Messenger: Getting Started FAQ: Webmail Configure field show rules in CRM forms Disable showing CRM calls in calendar Add company details Select workflow type Create and send an SMS campaign BI Builder datasets: Invoices and estimates Select email recipients in CRM Add new recipients in Bitrix24 Mail

Bitrix24Care