Developing 1C-Bitrix sales reports

Developing sales reports for 1C-Bitrix

Stock analytics of sale module in 1C-Bitrix outputs flat order table with minimal grouping. For managing sales this is not enough: you need segmentation by managers with returns accounted for, breakdown by product categories, average check dynamics, repeat purchase analysis. Ready reports can't connect data from different tables — orders, basket, payments, order properties — into single analytics query.

Custom sales reports — this is set of SQL/ORM queries, visualization in admin panel and Excel export for passing to management or finance department.

Data architecture: where the numbers come from

All sales data distributed across multiple sale module tables:

Connection with catalog. The PRODUCT_ID field in basket — is ID of iblock element (trade offer). To group by catalog sections you need JOIN with b_iblock_section_element and b_iblock_section. If using SKU — additional JOIN via PROPERTY_CML2_LINK to reach parent product.

Deep-dive: revenue report with manager analytics

Most demanded report — revenue by periods with manager breakdown. Task: show how much each manager brought in, from how many orders, what's average check and cancellation percentage.

ORM query:

use Bitrix\Sale\Internals\OrderTable;
use Bitrix\Main\Entity\ExpressionField;

$result = OrderTable::getList([
    'select' => [
        'RESPONSIBLE_ID',
        'MONTH' => new ExpressionField('MONTH', "DATE_TRUNC('month', %s)", ['DATE_INSERT']),
        'REVENUE' => new ExpressionField('REVENUE', 'SUM(CASE WHEN %s NOT IN (\'F\', \'CA\') THEN %s ELSE 0 END)', ['STATUS_ID', 'PRICE']),
        'CANCELLED' => new ExpressionField('CANCELLED', 'SUM(CASE WHEN %s IN (\'F\', \'CA\') THEN %s ELSE 0 END)', ['STATUS_ID', 'PRICE']),
        'ORDER_COUNT' => new ExpressionField('ORDER_COUNT', 'COUNT(%s)', ['ID']),
        'AVG_CHECK' => new ExpressionField('AVG_CHECK', 'AVG(CASE WHEN %s NOT IN (\'F\', \'CA\') THEN %s END)', ['STATUS_ID', 'PRICE']),
    ],
    'filter' => [
        '>=DATE_INSERT' => DateTime::createFromPhp(new \DateTime('2024-01-01')),
    ],
    'group' => ['RESPONSIBLE_ID', 'MONTH'],
    'order' => ['MONTH' => 'ASC', 'REVENUE' => 'DESC'],
]);

This query returns data for table and graph. Manager with RESPONSIBLE_ID linked with b_user for displaying full name.

Manager metrics being calculated:

• Revenue (without cancellations and returns)
• Cancellation sum — shows work quality
• Average check — comparison between managers
• Order count — workload
• Conversion from "New" to "Completed" — COUNT(status=F) / COUNT(*) by statuses
• Average processing speed — AVG(DATE_STATUS - DATE_INSERT) for transition from initial status

Report by products and categories

Second most important report. Shows which products and categories generate revenue, which are dead weight.

Structure: JOIN BasketTable with OrderTable (filter by paid orders) and with catalog iblock. Grouping by sections with aggregation: revenue, units sold, average sale price, average discount.

ABC-analysis embedded into this report: products sorted by revenue descending, cumulative total calculated, and each assigned category — A (80% revenue), B (15%), C (5%). Implemented via window function SUM() OVER (ORDER BY revenue DESC) or programmatically after query.

Visualization and dashboard

Reports placed on custom page in /local/admin/. Visualization stack:

• Chart.js — line graphs of dynamics, bar charts for manager comparison
• HTML tables with sorting — for detailed data
• PhpSpreadsheet — export to xlsx with formatting, TOTAL formulas, auto column width

Dashboard built as single-page with tabs: "Overall revenue", "By managers", "By products", "ABC-analysis". Data loaded via AJAX endpoint, filter parameters (period, manager, category) passed in request.

Caching and performance

On store with 50,000+ orders ORM queries with grouping execute 3-10 seconds. Solutions:

• Materialized views (PostgreSQL) or summary table, recalculated by agent once an hour
• Indexes — mandatory: (DATE_INSERT, STATUS_ID) on b_sale_order, (ORDER_ID, PRODUCT_ID) on b_sale_basket
• Report cache in Bitrix managed_cache with TTL 1 hour and invalidation on order change

Development timeline

Total timeline — 1-2 weeks depending on number of reports and metrics complexity. Result — admin dashboard with export, replacing manual Excel export and processing.