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: Inventory management

A dataset contains information about warehouses, inventory documents, products, deals, 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 inventory management, 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.

catalog_store — warehouses. This dataset contains data about warehouses. Use this dataset to analyze product movement and documents for specific warehouses.

catalog_store

Field Description Data type
ID Warehouse ID Number
TITLE Warehouse name String
ACTIVE Shows whether the warehouse is active: Y — yes, N — no String
DATE_CREATE Date and time the warehouse was created Date and time

catalog_store_product — current warehouse stock. This dataset contains data about product stock at each warehouse. Use this dataset to analyze product availability and reserves.

catalog_store_product

Field Description Data type
ID ID of the stock record Number
PRODUCT_ID Product ID Number
STORE_ID Warehouse ID Number
AMOUNT Total product quantity at the warehouse Number
QUANTITY_RESERVED Reserved product quantity at the warehouse Number

catalog_store_document — inventory documents. This dataset contains data about inventory documents that track product movement. Use this dataset to analyze warehouse operations and processed or canceled documents.

catalog_store_document

Field Description Data type
ID Inventory document ID Number
TITLE Inventory document name String
DOC_TYPE Inventory document type String
DOC_TYPE_CODE Inventory document type code String
DATE_CREATE Date and time the inventory document was created Date and time
DATE_MODIFY Date and time the inventory document was modified Date and time
STATUS Inventory document status String
STATUS_CODE Shows whether the inventory document is processed: Y — yes, N — no String
TOTAL Total cost of products in the inventory document Number
CURRENCY Inventory document currency String
RESPONSIBLE_ID ID of the employee responsible for the inventory document Number
RESPONSIBLE_NAME Name of the employee responsible for the inventory document String
RESPONSIBLE ID and name of the employee responsible for the inventory document String
WAS_CANCELLED Shows whether the inventory document is canceled: Y — yes, N — no String

catalog_store_document_item — products in inventory documents. This dataset contains data about products in inventory documents. Use this dataset to analyze product movement and prices in these documents.

catalog_store_document_item

Field Description Data type
ID ID of the product record in the inventory document Number
DOCUMENT_ID Inventory document ID Number
DOCUMENT_DATE_CREATE Date and time the inventory document was created Date and time
PRODUCT_ID Product ID Number
STORE_FROM ID of the warehouse the product is written off from. Used in sales, write-off, and transfer documents Number
STORE_TO ID of the warehouse where the product is received. Used in stock receipt, stock adjustment, and transfer documents Number
AMOUNT Product quantity Number
PURCHASING_PRICE Product purchase price Number
PRICE Product sale price Number

sale_document_saleorder — sales orders. This dataset contains data about sales orders. Use this dataset to analyze shipments and canceled sales orders.

sale_document_saleorder

Field Description Data type
ID Sales order ID Number
DATE_CREATE Date and time the sales order was created Date and time
DATE_UPDATE Date and time the sales order was last modified Date and time
PRICE_DELIVERY Delivery total for the sales order Number
DEDUCTED Shows whether shipment is complete: Y — yes, N — no String
DATE_DEDUCTED Date and time of shipment Date and time
DELIVERY_NAME Delivery service name String
WAS_CANCELLED Shows whether the sales order is canceled: Y — yes, N — no String
CURRENCY Sales order currency String
RESPONSIBLE_ID ID of the employee responsible for the sales order Number
RESPONSIBLE_NAME Name of the employee responsible for the sales order String
RESPONSIBLE ID and name of the employee responsible for the sales order String

sale_document_saleorder_item — products in sales orders. This dataset contains data about products in sales orders. Use this dataset to analyze sales by product and warehouse.

sale_document_saleorder_item

Field Description Data type
ID ID of the product record in the sales order Number
DOCUMENT_ID Sales order ID Number
DOCUMENT_DATE_CREATE Date and time the sales order was created Date and time
PRODUCT_ID Product ID Number
NAME Product name String
PRICE Product sale price Number
PRICE_CURRENCY Sale price currency String
DATE_INSERT Date and time the product was added to the sales order Date and time
STORE_ID Warehouse ID Number
AMOUNT Product quantity Number
COST_PRICE Product cost Number
COST_CURRENCY Cost currency String

Create a chart

Add information from a dataset to charts and track inventory operations. Charts show product movement between warehouses and document activity.

  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 sales order item data with warehouse data to analyze product sales by warehouse.

  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 data from two datasets: products in sales orders (sale_document_saleorder_item) and warehouses (catalog_store). You can copy it to see how the query works. The query shows product sales by warehouse for the selected period.

SELECT
    catalog_store.TITLE AS "Warehouse",
    sale_document_saleorder_item.NAME AS "Product",
    SUM(sale_document_saleorder_item.AMOUNT) AS "Amount",
    SUM(sale_document_saleorder_item.AMOUNT * sale_document_saleorder_item.PRICE) AS "Total amount"
FROM sale_document_saleorder_item
JOIN catalog_store
    ON catalog_store.ID = sale_document_saleorder_item.STORE_ID
WHERE sale_document_saleorder_item.DOCUMENT_DATE_CREATE >= date_add('day', -30, current_date)
GROUP BY
    catalog_store.TITLE,
    sale_document_saleorder_item.NAME
ORDER BY
    SUM(sale_document_saleorder_item.AMOUNT * sale_document_saleorder_item.PRICE) DESC

SELECT determines which information to extract from the dataset. For example, the query SELECT catalog_store.TITLE AS "Warehouse":

  • Selects the warehouse name TITLE from the catalog_store dataset
  • Assigns it the name Warehouse
  • Displays the result in a separate table column

FROM specifies the main dataset that the data comes from. The query gets data from sale_document_saleorder_item, a table with products, quantities, and prices in sales orders.

JOIN links the sale_document_saleorder_item table to catalog_store. The query connects products in sales orders to the warehouses they were shipped from.

WHERE filters the results by the creation date of products in sales orders and shows data for the last 30 days.

GROUP BY groups data by warehouse and product to calculate the amount and total amount for each group.

ORDER BY sorts the results by total amount in descending order.


In brief

  • A dataset contains information about warehouses, inventory documents, products, deals, and other Bitrix24 items.
  • Each item has several datasets that automatically receive data from system and custom fields.
  • Inventory management has six datasets: warehouses (catalog_store), current warehouse stock (catalog_store_product), inventory documents (catalog_store_document), products in inventory documents (catalog_store_document_item), sales orders (sale_document_saleorder), and products in sales orders (sale_document_saleorder_item).
  • Use datasets to create charts and analyze business efficiency metrics. Charts show product movement between warehouses, inventory document operations, and sales by warehouse.
  • Merge datasets to analyze sales by warehouse. 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