Datasets and SQL in the Bitrix24 BI Constructor: A Technical Guide

Three Dataset Types: System, Custom, and Virtual SQL

Bitrix24 (Alaio) BI Constructor offers three dataset types - system (physical tables), custom (joined or extended physical tables), and virtual (saved SQL queries) - and choosing the right type for each report need determines how flexible, performant, and maintainable your dashboard will be.

The table below summarises when to reach for each type:

System datasets cannot be edited - you can read them but not modify their structure. Custom and virtual datasets are fully editable: you can rename columns, set display formats, define metrics, and add calculated columns directly in the dataset editor.

For a practical introduction to the report-building workflow, see How to Create a BI Report in Bitrix24 with BI Constructor.

How Bitrix24 Exposes Its Data: Tables, Schema, and Trino SQL

All Bitrix24 data accessible to BI Constructor lives in the bitrix24 schema on a trino database engine - formerly known as PrestoSQL - so SQL syntax follows Trino conventions, not MySQL or PostgreSQL.

When creating a dataset through the interface, you fill in three fields: Database → trino, Schema → bitrix24, Table → the entity table you need. In SQL Lab you simply select the bitrix24 schema and write your query.

Key entity tables include:

• crm_deal - deals (core fields, amounts, stage, responsible user)
• crm_deal_uf - user-defined (custom) fields for deals
• crm_deal_product_row - line items / products inside deals
• crm_lead - leads
• crm_lead_uf - custom fields for leads
• crm_lead_status_history - lead stage history
• crm_lead_product_row - products in leads
• crm_company - company records
• crm_company_uf - custom fields for companies

Because the SQL engine is Trino, type casting uses CAST(... AS TIMESTAMP) and date arithmetic uses functions like date_add('day', -30, current_date). Standard ANSI SQL for SELECT, JOIN, WHERE, GROUP BY, and window functions works as expected - just be aware of Trino-specific syntax when you hit edge cases.

Column naming tip: When you use calculated fields or metrics in the dataset editor, use Latin characters without spaces for column aliases. Column names with spaces or non-Latin characters can break formula recognition in the chart builder. In SQL, use SELECT field AS deal_name rather than SELECT field AS "Deal Name" for fields you plan to reference in formulas.

SQL Lab: The Analyst's Sandbox for Building Virtual Datasets

SQL Lab (found at BI Constructor → SQL → SQL Lab) is an interactive query editor where you compose, test, and save virtual datasets - think of it as a scratchpad that never affects production until you explicitly click "Save Dataset."

The workflow inside SQL Lab follows a deliberate iterative pattern:

1. Select the bitrix24 schema.
2. Write a basic SELECT ... FROM to confirm the table is accessible.
3. Add one JOIN at a time, clicking Run after each addition to inspect the preview table.
4. Debug column ambiguity errors (the most common beginner issue - prefix every column reference with its table alias).
5. Add WHERE filters, aggregations, or window functions.
6. Confirm the row count and column values match business expectations.
7. Click Save Dataset and give it a descriptive name.

A key practice: do not edit a saved production dataset directly. Instead, copy its SQL into SQL Lab, iterate freely there, then paste the validated query back. This preserves the working dataset while you experiment.

Useful debugging techniques in SQL Lab:

• Comment out sections with -- or /* ... */ to isolate issues at intermediate steps.
• Read Trino error messages carefully - "ambiguous column name" almost always means two joined tables share a field name and you need table-prefixed aliases.
• If the editor highlights a false syntax error, simply re-run the query; it is a known occasional UI quirk.
• For performance issues on large datasets, reduce the selected columns - avoid SELECT * in finalised datasets.

Step-by-Step Checklist: Building a Virtual Dataset

A well-designed virtual dataset is built iteratively in SQL Lab, saved with a clear name, and configured with human-readable column labels before any chart is attached to it.

Use this checklist for every virtual dataset you build:

• Define the business question - write it in plain language before touching SQL ("Which product combinations appear most often in the same deal?")
• Identify the source tables - list every physical table you need and the join keys between them
• Open SQL Lab - select database trino, schema bitrix24
• Build the query incrementally - start with one table, add JOINs one at a time, run after each step
• Include all filter dimensions - any field a user might want to filter or group by must be a column in the dataset
• Include a date field at row level - without a per-row date, time-series charts are impossible
• Add human-readable fields - IDs are needed for join logic, but expose named fields (e.g., product name, manager name) for the chart layer
• Create a unique combination key if cross-filtering is needed - concatenate entity IDs into a single combo_key field so multiple charts can share a common filter dimension
• Avoid double-aggregating window function results - if a column already contains a total (computed via a window function), use MAX() in the chart, not SUM()
• Save the dataset with a clear, descriptive name (e.g., deal_product_combinations)
• Rename columns for readability - open Datasets → Edit → Columns tab; set display names, formats, and descriptions
• Define Metrics and Calculated Columns in the dataset editor if needed
• Sync columns from source after modifying the SQL - click "Synchronise columns from source" on the Columns tab
• Verify in Bitrix24 - close and reopen the report inside Bitrix24 to confirm changes render correctly

Data flows from raw Bitrix24 tables through the dataset layer into charts and finally into a published report.

flowchart TD
    T1[crm_deal] --> SL[SQL Lab / Trino]
    T2[crm_deal_product_row] --> SL
    T3[crm_deal_uf\nCustom Fields] --> SL
    T4[crm_lead\ncrm_company] --> SL
    SL -->|Save Dataset| VD[Virtual Dataset]
    VD --> C1[Bar / Line Chart]
    VD --> C2[Summary Table]
    VD --> C3[KPI Metric Tile]
    C1 --> DB[Dashboard]
    C2 --> DB
    C3 --> DB
    DB -->|Publish| B24[Bitrix24 Report]

Report Parameters and Jinja Templates: Dynamic SQL Filtering

Report parameters in BI Constructor are dynamic variables injected into a virtual dataset's SQL query via Jinja templating - they allow end users to filter a report by date range or other criteria without the developer rewriting the query.

The standard use case is date filtering. A static WHERE date_create >= DATE '2023-01-01' hard-codes a range that never changes. By replacing it with Jinja conditional blocks, the dataset responds to the date picker controls visible to the report user.

The principle works as follows:

1. The dataset's SQL query contains conditional Jinja blocks that check whether a parameter variable (e.g., a start date or end date) has been provided by the user.
2. If the variable is present, the corresponding WHERE clause fragment is included; if absent, it is skipped.
3. A trailing true statement ensures the query remains syntactically valid even when no date is selected.

This pattern means a single saved dataset can serve both an unfiltered overview and a date-scoped drill-down - the user simply selects dates on the dashboard and the SQL adjusts automatically.

Workflow for adding date parameters:

1. Create or open your virtual dataset in SQL Lab.
2. Replace the static date condition in WHERE with a Jinja conditional block referencing from_dttm and to_dttm variables.
3. Save the updated dataset.
4. Open the Columns tab and click Synchronise columns from source to register any new fields.
5. Create or update a chart on this dataset; confirm the date filter field appears.
6. Save the chart and verify the filter works inside the Bitrix24 report view.

Parameters are not limited to dates - the same Jinja mechanism can be extended to other filter dimensions depending on report requirements. The approach ensures the report stays interactive for business users while all the complexity remains inside the SQL layer.

For teams building reports where data sensitivity matters - for instance, restricting which records a manager can see - this pattern integrates cleanly with role-based access controls. See also Self-Hosted Bitrix24 Security Hardening: 25-Point Checklist for access control considerations in on-premise deployments.

The Dataset → Chart → Report Pipeline

Every Bitrix24 BI report follows a strict three-layer hierarchy: a dataset defines the data shape, a chart defines the visualisation of that data, and a report (dashboard) assembles multiple charts into a published analytical view.

Understanding this hierarchy prevents the most common mistakes beginners make - such as building charts directly from raw tables when a virtual dataset was needed, or trying to filter at the report level what should have been filtered at the dataset level.

Layer 1 - Dataset: Defines which fields are available, their types, display names, and any pre-calculated metrics or computed columns. One dataset can power many charts.

Layer 2 - Chart: Created in the Charts section by selecting a dataset and a visualisation type. The chart builder offers two query modes:

• Aggregation - for totals, comparisons, and grouped metrics (e.g., total deal value by stage)
• Raw Records - for row-level detail views

Fields are assigned to chart zones via drag-and-drop:

• Dimensions - qualitative breakdowns (product name, category, date)
• Metrics - numeric values with an aggregation function (SUM, COUNT, AVG, MAX, MIN, first_value)

Choose the aggregation function to match the metric's nature: SUM for cumulative values, COUNT for record counts, MAX or MIN for boundary values. When a column already contains a pre-aggregated total (e.g., from a window function), use MAX() in the chart to avoid re-summing.

Layer 3 - Report (Dashboard): Charts are added to a report/dashboard either during chart creation ("save to report") or from the dashboard editor. After publishing, changes in BI Constructor require closing and reopening the report in Bitrix24 to take effect.

Practical shortcut: To create a new chart quickly, open an existing similar chart, reconfigure its fields, then use "Save as New Chart" - never overwrite the original.

For a broader look at how Bitrix24's built-in analytics complement BI Constructor, see Bitrix24 CRM Analytics and Sales Dashboards: Reports, Funnels and Forecasting.

Generating SQL with an AI Copilot: A Practical Shortcut

SQL generation via an AI copilot - feeding the dataset field descriptions into ChatGPT or Bitrix24's built-in AI assistant - is a verified, time-saving technique that produces working query drafts, though the developer must review join logic and data granularity before saving.

The technique works because BI Constructor's dataset documentation already describes table names, column names, and relationships in structured form. By copying that documentation and describing the business question in plain language, an AI model has enough context to generate syntactically correct Trino SQL.

Steps:

1. Open the dataset descriptions inside BI Constructor (the column metadata for the tables you need).
2. Copy the full field list and table descriptions.
3. Paste into your AI assistant of choice, then describe what you want: "Given these tables and columns, write a Trino SQL query that returns deals created in the last 30 days, grouped by responsible manager, with total deal value and count."
4. Review the generated SQL: check that JOIN conditions are correct, that there are no unintended duplicate rows, and that the granularity matches your charting intent.
5. Test in SQL Lab, iterate if needed, then save.

This approach is particularly useful for analysts who are comfortable reading and editing SQL but less practiced at writing complex JOINs from scratch. It is not a replacement for understanding the data model - an incorrect JOIN in a dataset silently returns wrong numbers, which is worse than no report at all.

For teams with non-technical stakeholders, the end-user experience remains fully no-code: once a developer has built and published a dashboard, business users interact only with filters, date pickers, and drill-downs. The SQL stays invisible behind the published report.

Best Practices for Production-Ready Datasets

Production BI datasets in Bitrix24 should be narrow (only necessary columns), consistently named in Latin without spaces, documented with column descriptions, and tested end-to-end in Bitrix24 - not just in SQL Lab - before being shared with end users.

A summary of the most important practices drawn from real implementation experience:

BI Constructor caches dashboard data for ~1 hour from first load. If a report needs near-real-time data, instruct users to manually refresh via the settings menu rather than expecting live updates.

For organisations considering whether BI Constructor is sufficient versus a dedicated BI platform, the answer typically depends on data volume and cross-system reporting needs. When Bitrix24 is the primary system of record for CRM, deals, and lead data, BI Constructor with virtual SQL datasets covers the vast majority of analytical requirements - including cohort analysis, pipeline velocity, and product mix reporting - without any external tooling. If you are still evaluating Bitrix24 as a platform, Bitrix24 vs HubSpot: An Honest Comparison for SMBs and Bitrix24 CRM Analytics and Sales Dashboards provide relevant context on the broader analytics capabilities.

For manufacturing or logistics companies building operational dashboards on top of Bitrix24 deal data, virtual SQL datasets are especially powerful when combined with ERP integration - see Bitrix24 ERP & Accounting Integration: Two-Way Data Sync Guide for how external data can flow into the tables BI Constructor queries.