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:
- catalog_store — warehouses
- catalog_store_product — current warehouse stock
- catalog_store_document — inventory documents
- catalog_store_document_item — products in inventory documents
- sale_document_saleorder — sales orders
- sale_document_saleorder_item — products in sales orders
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.
- 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 sales order item data with warehouse data to analyze product sales by warehouse.
- 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: 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
TITLEfrom thecatalog_storedataset - 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.