1 Overview
- 1.1 What is the Report Composer?
2 Opening the Composer
- 2.1 Where is the Composer located?
3 Composer Interface
- 3.1 Main Composer Sections
  - 3.1.1 Top Panel
4 First SQL Report
5 Report Selection Window
6 Basic SQL Queries
- 6.1 SELECT
- 6.2 Basic SQL Functions
  - 6.2.1 SELECT DISTINCT
  - 6.2.2 SELECT TOP
  - 6.2.3 MIN/MAX
  - 6.2.4 COUNT, AVG, SUM
- 6.3 WHERE
- 6.4 Logical Operators
  - 6.4.1 AND
  - 6.4.2 OR
  - 6.4.3 NOT
  - 6.4.4 IN
  - 6.4.5 LIKE
  - 6.4.6 IS NULL / IS NOT NULL
- 6.5 ORDER BY
- 6.6 JOIN
  - 6.6.1 INNER JOIN
  - 6.6.2 LEFT JOIN
  - 6.6.3 RIGHT JOIN
  - 6.6.4 FULL JOIN
- 6.7 GROUP BY
7 Creating the First Report
8 Report Selection
- 8.1 Report Categories
- 8.2 Main Report Actions
9 Additional Actions
10 SQL Editor
- 10.1 SQL Editor Panel
  - 10.1.1 SQL Query Name
  - 10.1.2 Execute Button
  - 10.1.3 Editor Toolbar
  - 10.1.4 SQL Tabs
- 10.2 Recommendations
11 Working with Columns
- - 11.1.1 Expand By and Group By
- 11.2 Working with Groups
  - 11.2.1 Example use cases:
- 11.3 Configuration
12 Working with Parameters
- 12.1 Standard Use Cases for Parameters in SQL Queries
13 Working with the Database
- 13.1 Where to Find the Database Structure
14 Common Errors
- 14.1 GROUP BY Error
- 14.2 Parameter Not Found
15 Best Practices

Overview

What is the Report Composer?

The Report Composer is a tool for creating SQL reports and retrieving data directly from the MySales database.

The Composer allows users to:

create new reports;
edit existing reports;
configure SQL queries;
add execution parameters;
work with the database structure;
export results;
configure automatic email distribution.

The system uses the following SQL dialect:

T-SQL (Microsoft SQL Server).

Opening the Composer

Where is the Composer located?

To open the Report Composer:

Navigate to the Reports section in the top menu;
Click the "..." button in the upper-right corner;
Select "Composer".

After that, the Report Composer page will open.

Composer Interface

Main Composer Sections

The Composer consists of 5 main sections:

Section	Purpose

Section	Purpose
Top Panel	Report management
SQL Editor	SQL query editing
Columns	Display configuration
Parameters	Execution parameter configuration
Database	Database structure view

Top Panel

The top panel contains:

report selection;
creation of a new report;
editing;
SQL execution;
stopping execution;
deletion;
additional actions menu.

First SQL Report

Below is an example of the simplest SQL report:

SELECT
    p.SKU_ID AS SKU,
    p.ARTICLE_NAME AS NAME,
    SUM(s.SALES_VOLUME) AS SALES
FROM SALES_SKU s
JOIN PRODUCTS p
    ON p.SKU_ID = s.SKU_ID
WHERE s.WEEK >= 202401
GROUP BY
    p.SKU_ID,
    p.ARTICLE_NAME
ORDER BY SALES DESC

This report:

retrieves sales by SKU;
joins the SALES_SKU and PRODUCTS tables;
calculates the total sales volume;
sorts the results in descending order by sales.

After executing the SQL, the results will be displayed in the constructor’s results table.

In this example, it is important to pay attention to the column aliases: SKU, NAME, SALES.

These names are exactly what the system uses as field codes in the report column settings.

If a column in the SELECT statement does not have a clear alias, it will be more difficult to configure in the constructor.

Report Selection Window

After opening the Composer, the user enters the report selection window.

In this window, users can:

create a new report;
find an existing report;
edit a report;
save changes;
configure access;
configure distribution;
export or import a report.

Basic SQL Queries

The Report Composer uses the following SQL dialect:

T-SQL (Microsoft SQL Server).

Basic SQL knowledge is required to create reports.

You can learn about the principles behind SQL queries by following this link.

Below are the main SQL commands required for basic work with scripts: SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY.

SELECT

SELECT is a basic command used to retrieve data from a database. Usually, this command is used together with the FROM command, which defines the table from which the data should be retrieved.

It is also possible to retrieve data from multiple tables — this will be covered in the JOIN section.

A standard SELECT query has the following structure:

SELECT column1, column2, ...
FROM table_name;

column1, column2, ... in this query are the names of the fields (columns) in the table from which you want to retrieve data. If you need to select all data, * is used:

SELECT * FROM table_name;

For example, the result of the query

SELECT sku_id, 
       article_name 
FROM products;

will be two columns with the SKU code and the name from the PRODUCTS table.

The result of the query

SELECT * FROM products;

will include all columns from the PRODUCTS table.

When creating SELECT queries, it is recommended to assign so-called aliases (conditional names) to columns:

SELECT column_1 SKU, column_2 STORE FROM table_name;

In this example, SKU and STORE are aliases for the column_1 and column_2 columns — they will later be used when working with the report constructor.

You can learn more about SELECT and try how it works by following the link.

You can learn more about the concept of aliases by following the link.

Basic SQL Functions

In addition to standard field selection using SELECT, it is also possible to retrieve specific data. Below are the basic functions that can be used directly in a SELECT query:

SELECT DISTINCT

Used to select unique values from a field.
For example:

SELECT DISTINCT column_1
FROM table_name;

This query will return only unique values in the column_1 column.

SELECT DISTINCT store_id
FROM sales

Returns only unique values.

You can learn more about SELECT DISTINCT and try how it works by following the link.

SELECT TOP

Used to select the first N values from a field.
For example:

SELECT TOP 100 column_1 FROM table_name;

SELECT TOP 100 *
FROM sales

This query will return only the first 100 values in the column_1 column.

You can learn more about SELECT TOP and try how it works by following the link.

MIN/MAX

Used to display the minimum or maximum value in a column.
For example:

SELECT MIN(column_1) FROM table_name;

This query will return the smallest value in the column_1 column.

SELECT MIN(price),
       MAX(price)
FROM products

MIN returns the smallest value in the column.
MAX returns the largest value in the column.

You can learn more about MIN/MAX and try how it works by following the link.

COUNT, AVG, SUM

COUNT() counts the number of rows in a column.
AVG() displays the average value of a column.
SUM() displays the sum of all values.

For example:

SELECT COUNT(*),
       AVG(price),
       SUM(sales_qty)
FROM sales

SELECT COUNT(column_1) FROM table_name;

Returns the number of rows in the column_1 column.

SELECT AVG(column_1) FROM table_name;

Returns the average value in the column_1 column.

SELECT SUM(column_1) FROM table_name;

Returns the sum of all values in the column_1 column.

You can learn more about COUNT, AVG, SUM and try how it works by following the link.

WHERE

SELECT sku_id
FROM products
WHERE sku_id = 100

The WHERE command is used to create a filtering condition:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example, if you need to select all values greater than 10, the WHERE condition should be written as:

SELECT column1
FROM table_name
WHERE column1 > 10;

The WHERE command also supports mathematical operators such as greater than, less than, not equal to, and others. These operators are used when defining conditions.

You can learn more about WHERE, the available operators, and test how they work by following the link.

Logical Operators

There can be multiple conditions in a WHERE clause. Logical operators are used to define them:

AND

Used when both conditions must be true;

WHERE sku_id = 100
AND store_id = 1

OR

Used when at least one of several conditions must be true;

WHERE sku_id = 100
OR sku_id = 200

NOT

Used when data should be returned if the condition is not true.

WHERE NOT store_id = 1

You can learn more about AND, OR, NOT and test how they work by following the link.

The IN and LIKE operators are comparison operators used in the WHERE clause:

IN

Allows filtering when there are multiple possible values, for example:

SELECT SKU_ID, ITEM_CODE 
FROM PRODUCTS 
WHERE SKU_ID in (2,4,8)

This query will return only those SKU_ID values that are equal to 2, 4, or 8. You can learn more about IN by following the link.

LIKE

Allows searching for a specified pattern in column data, for example:

SELECT SKU_ID, ARTICLE_NAME 
FROM PRODUCTS 
WHERE ARTICLE_NAME LIKE ('%Milk%')

This query will return SKUs whose name contains the word "Milk".

You can learn more about LIKE by following the link.

You can also work with columns containing NULL values in the WHERE clause.

IS NULL / IS NOT NULL

WHERE price IS NULL

WHERE price IS NOT NULL

Used to check for empty values.

Standard WHERE operators do not work with NULL values, so the IS NULL and IS NOT NULL operators must be used instead.

You can learn more about them and test how they work by following the link.

ORDER BY

The ORDER BY command allows sorting the output data according to specified rules:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 asc/desc

You can learn more about ORDER BY and test how it works by following the link.

JOIN

The JOIN command is used after FROM and is applied to display columns from different related tables in a query.

Example

SELECT p.sku_id,
       g.group_name
FROM products p
JOIN groups g
    ON p.group_id = g.group_id

For example, if there is a PRODUCTS table with the columns SKU_ID, GROUP_ID and a GROUPS table with the columns GROUP_ID and GROUP_NAME, JOIN allows selecting SKU_ID and GROUP_NAME data from different tables by linking them through GROUP_ID.

There are several types of JOIN:

INNER JOIN

Can be used with or without the INNER keyword; only records that have matches in both tables are returned.

LEFT JOIN

Returns all records from the left table (from FROM) and the matching records from the right table (from JOIN).

RIGHT JOIN

Returns all records from the right table (from JOIN) and the matching records from the left table (from FROM).

FULL JOIN

Returns all records if there is a match in either the left or right table.

You can learn more about the JOIN concept and test how it works by following the link.
More details about each JOIN type can be found at the following links: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

GROUP BY

The GROUP BY command is used after WHERE and is applied to group data by similarity.

For example, you can group all products with the same code. It is used when aggregate functions such as COUNT, MAX, MIN, SUM, AVG, etc. are present in the SELECT statement:

SELECT column1, sum(column2)
FROM table_name
WHERE condition
GROUP BY column1
ORDER BY column1 asc/desc

A good example of using GROUP BY is summarizing sales for 4 weeks for two stores.
In the SALES_SKU table, sales are split by weeks. To calculate sales for 4 weeks, the following query is used:

SELECT sku_id, store_id, sum(sales_volume) vol
FROM sales_sku
WHERE sku_id = 93 and week between 201910 and 201913 and store_id in (1,2)
GROUP BY sku_id, store_id
ORDER BY vol asc

The result of this query will show the total sales for 4 weeks for each store.

It is important that GROUP BY should include not only SKU_ID, but also STORE_ID, because these fields are not used in aggregate functions. Otherwise, the system will return an error.

You can learn more about GROUP BY and test how it works by following the link.

Creating the First Report

To create a new report, click the “New” button.

The system will open a new SQL editor.

Enter the SQL Query

In this block, the user writes an SQL query that should retrieve data from tables in the database. Queries are written using the SQL language. To write queries, basic SQL programming experience is required. There are many introductory SQL courses available online to gain basic skills.

The basic queries required to create simple reports are described in the Basic SQL Queriessection.

This documentation block describes possible actions with an already written SQL code block. Working with the basic queries required to create simple reports is described in the Basic SQL Queries section.

After the script code has been written, the following operations are available:

To run the report, click the corresponding ▶️ button. After clicking the button, script execution will begin. It may take from a few seconds to several minutes depending on the complexity and volume of the data being retrieved. If the code compiler detects an error in the code, it will display it below in a red block.

The system will:

check the SQL;
execute the query;
display the result.

If there are no errors, a report loading message will appear at the bottom of the window, and after loading is completed, a window with the query results will open.

If the query can potentially return more than 10,000 rows, it is recommended to use TOP 1000 or TOP 10000 after the SELECT command during validation.

Stop Report Execution - if the report is running for too long or needs to be stopped, this can be done by clicking the ◼︎ button. This button becomes red and active while the report is running.
Adding Columns to the Report - the fields selected in SELECT can be added as report columns. Columns are added based on the query. To do this, click the corresponding button. After clicking, the system will automatically detect the columns and their names and add them to the report.

These columns are also displayed in the “Table” block, where they can be configured.

The system automatically adds all columns. Unnecessary columns can be removed by clicking the ❎ button. If needed, they can be added again from the “Table” block.

Adding Report Parameters to the “Parameters” Field - to transfer parameters, click the ➡️ button. After that, the system will automatically detect the parameters and add their names to the “Parameters” field.

Parameters that have not been transferred are highlighted in red in the SQL query editor, while transferred parameters are highlighted in green. Each parameter must be configured.

A description of parameter configuration and possible formats for using them in SQL queries is provided in the “Working with Parameters” section.

Report Selection

The report list is located:

in the "Report" field at the top of the Composer.

Users can:

search reports;
browse categories;
open existing reports.

Report Categories

The system automatically groups reports into categories:

Catalog
Control
Data Quality
Forecast
Orders
Pricing
Promo
Sales
Stock
Usage

Main Report Actions

Edit — opens the report edit mode.
New — creates a new empty report.
Execute — runs the SQL query.

During execution:

– SQL is validated;

– the result is generated;

– errors are displayed at the bottom of the editor.

Cancel — stops SQL query execution.

Used for:

– long-running queries;

– incorrect JOIN operations;

– accidental execution of large datasets.

Delete — deletes the current report.

Additional Actions

Click the:

«...» button

located to the right of the control buttons.

Edit Description

Allows users to:

– add a description;

– explain report logic;

– describe parameters.

Database

Opens the database structure.

Allows users to:

– browse tables;

– browse fields;

– find aliases;

– search columns.

Save As

Creates a copy of the report.

Useful for:

– testing;

– creating new versions;

– backup copies.

Schedule

Configures automatic email distribution.

Users can specify:

– date;

– time;

– recipient list;

– email subject;

– message text.

Export Report

Supported formats:

– XLSX;

– CSV.

Import Report

Imports an SQL query from a file.

SQL Editor

The SQL editor is used for:

writing SQL queries;
editing existing reports;
testing SQL.

SQL Editor Panel

The upper section of the SQL editor contains:

SQL query name;
SQL execution button;
editor toolbar;
tab creation button.

SQL Query Name

The name displays the current SQL query or report name.

Execute Button

The ▶ button executes the current SQL query.

Editor Toolbar

The toolbar is used for quick actions related to the SQL query.

SQL Tabs

The system supports working with multiple SQL tabs simultaneously.

Recommendations

For testing large reports, it is recommended to use:

TOP 1000

or

TOP 10000

Working with Columns

The user can:

change the order;
hide columns;
change column width;
configure formatting;
add links;
configure Group By;
configure Expand By.

The user can change the order of columns using drag-and-drop in the upper block of the constructor.

Style All - edits the overall report style. The process is similar to editing in a text editor.
Expand By - a detailed description is provided in the “Expand By and Group By” section.
Group By - a detailed description is provided in the “Expand By and Group By” section.
Name - the user defines the column name that will be displayed in the report.
Field Code

Defines which data will be displayed in the column.

For example:

SELECT SKU_ID SKU, STORE_ID STORE FROM SALES_SKU

If the field code STORE is assigned to the SKU column in the column settings, then the report will display data from the STORE column inside the SKU column. This is only possible when the report displays a single SKU column. If both SKU and STORE columns are displayed at the same time, changing the field code will not be possible. It is also not possible to assign the same field as its own field code.

Description — is used to add a column description.
Data Type - defines the display format of the data in the field. The following types are supported:

– Text — for text values (for example, “Product Name”);

– Number — for numeric values (for example, “Sales Quantity”);

– Date — for date values (for example, “Sales Date” or “Sales Week”).

Formula - allows applying formulas to display values in the column. Supported standard formats:
- Quantity;
- Pack Quantity.

Can be used for:

– recalculating values;

– displaying quantities;

– changing data formats.

Link - allows adding a link to another page or resource.

For example:

SELECT SKU_ID SKU, SITE_LINK LINK FROM MONITORING

In this case, the SKU column contains the product code, and the LINK column contains the website link. If the LINK column is selected in the “Link” field of the SKU column settings, then clicking the value in the SKU column in the report will open the corresponding link. Links can only be added to other columns.

Width - allows limiting the width of the column in the report. The value is specified in pixels. If the value is set to auto, the column width automatically adjusts to the content.
Style Cell - edits the style of an individual cell. The process is similar to editing in a text editor.
Style ? - allows configuring the overall display style of the column.

Can be used for:

– formatting all column values;

– changing colors;

– alignment;

– data styling.

Condition - allows setting conditional formatting rules for values.

Can be used for:

– highlighting values;

– changing colors;

– changing styles depending on the value.

Example:

%s > 10 && %s != 25

The Hide button hides the constructor settings panel.

Used for:

increasing the working area;
convenient SQL editing;
viewing large queries.

Expand By and Group By

These two column settings are always used together. They allow implementing cross-columns — when one column displays values from several other columns.

For example, this allows displaying product sales data for several weeks in a single row without manually creating a separate column for each week. This functionality will be explained in more detail in the example below.

The user created a query to display sales for the first week over the last 5 years for the selected product with code 56 in all stores (Store 1 and Store 2):

As a result, the following report was generated:

It can be seen that the report returned 10 rows in a format that is not very convenient. It would be much more convenient if all weeks and sales were displayed in a single row together with the product, resulting in only two rows — one for each store. For example:

To perform such transformations, the Expand By function must be used.

First, it is necessary to remove from display the column whose data should be expanded and inserted into another column. In this example, this is the WEEK (WK) column. To do this, click the delete button next to the WEEK column in the top row of columns.

The next step is to click the button next to the column where data from another column should be displayed. In this case, these are the Sales and Stock columns, so the button should be clicked for each of them.

After this, the system will display which column will be nested inside.

WK will then appear in the Expand By field in the settings of the corresponding columns.

Any columns that are not displayed in the report but are present in the query can be used for Expand By.

Next, it is necessary to configure which column the data should be grouped by. In this example, the data should be grouped by stores, so STR must be selected in the Group By field (the name of the column that retrieves store data in the SQL query).

Grouping (Group By) can only be configured for one column. For other columns using Expand By, this value is applied automatically.

After completing all settings, the user can click ▶️ and see how the data is displayed.

The user created a query to display sales for the first week over the last 5 years for the selected product with code 56 in all stores (Store 1 and Store 2).

As a result, the following report was obtained:

It can be seen that the report contains 10 rows, and the data is presented in a format that is not very convenient. It would be much more convenient if all weeks and sales were displayed in a single row together with the product. In this case, the report would contain only two rows — one for each store. For example:

To perform such transformations, the Expand By function must be used.

First, it is necessary to remove from display the column whose data should be expanded and inserted into another column. In this example, this is the WEEK (WK) column. To do this, click the delete button next to the WEEK column in the top row of columns.

The next step is to click the button next to the column where data from another column should be displayed. In this case, these are the Sales and Stock columns, so the button should be clicked next to each of them. After that, the system will display which column will be nested inside — in this case, WK (Week).

After this, WK will appear in the Expand By field in the settings of the corresponding columns. Any columns that are not displayed in the report but are present in the query can be used for Expand By.

Next, it is necessary to configure which column the data should be grouped by. In this example, the data should be grouped by stores, so STR must be selected in the Group By field (the name of the column that retrieves store data in the SQL query).

Grouping (Group By) can only be configured for one column. For other columns using Expand By, this value is applied automatically.

After completing all settings, the user can click the execution button and view the final data display result.

Important

Columns in the constructor are created based on the SQL query result.

If a new column needs to be added to the report, it must first be added to the SELECT query, then the SQL query must be executed, and only after that should the columns in the constructor be refreshed.

If a column exists in the SQL query but is not needed in the final report, it can be hidden or removed from display.

Working with Groups

The Groups tab is used for configuring data grouping within a report.

Allows users to:

group rows;
merge data;
create hierarchical display;
configure grouped cells.

Group Cells

The Group Cells parameter is used to merge identical values within grouped cells.

Example use cases:

sales by weeks;
sales by months;
stock balances by dates.

Configuration

Remove the column that should be expanded.

Set:

Expand By

for the required column.

Specify:

Group By

After that, the system automatically groups the data.

Working with Parameters

Parameters are values that the user specifies when running a report. They allow retrieving data according to defined criteria.

Parameters are automatically transferred from the SQL editor code to the “Parameters” block after clicking the corresponding button. After that, they can be configured.

There are two types of parameters:

:parameter — passes one specific value. In an SQL query, it is used, for example, as follows:

WHERE variable = :parameter

[parameter_list] — передає набір значень. У SQL-запиті використовується, наприклад, так:
WHERE variable IN ([parameter_list])

For example, if it is necessary to pass the value “5” as a parameter, the :parameter format is used after the equals sign. After running the report, the parameter is substituted into the code as follows:

WHERE value = 5

If it is necessary to pass a set of values, for example (1, 3, 8), the [parameter_list] format is used without the equals sign. After running the report, the parameter is substituted into the code as follows:

WHERE value IN (1, 3, 8)

Parameter names can be arbitrary — the main requirement is to use the correct format.

To configure a parameter, click its name in the “Parameters” block. After that, a parameter settings window will open:

ID - the parameter identifier. It is automatically determined according to the field to which it is applied and cannot be changed.
Name - the parameter name. It must match the parameter name in the code.
Display Name - the parameter name that will be displayed when running the report.
Type - defines the parameter type. It is important to select the correct type for proper operation and to avoid errors. The following types are supported:
- Text
  Text value.
- Number
  Numeric value.
- Date
  YYYY-MM-DD format.
Min - the minimum parameter value. Used for numbers, dates, weeks, and months.
Max - the maximum parameter value. Used for numbers, dates, weeks, and months.
Index - available only for parameters of the “Date”, “Week”, and “Month” types. Allows automatically offsetting the parameter value.

For example, if an index of -10 is set for a parameter of type “Date”, then by default, when running the report, the displayed date will be reduced by 10 days from the value specified in the “Value” field. If the “Value” field is left empty, the calculation is performed relative to the current date.

Value - the default value displayed when selecting parameters before running the report.
Query - used only for the “List” type. To view available options, simply enter the word select in the field — after that, a list of available queries will appear.

When running the report, values retrieved from the query will be displayed as a dropdown list for parameter selection.

The following standard queries are currently available (custom queries can also be created):

– report_select_stores — store selection;
– report_select_groups — group selection;
– report_select_managers — manager selection;
– report_select_contracts — contract selection;
– report_select_suppliers — supplier selection;
– report_select_whs — warehouse selection;
– report_select_fcst — forecast number selection.

Standard Use Cases for Parameters in SQL Queries

Below are examples of using parameters in SQL queries for different data types:

WHERE sku_id = :sku_id - used to select a single SKU by MySales code. When running the report, one numeric value is passed. Parameter type — “Number”.
WHERE sku_id IN ([sku_id_list]) — used to select multiple SKUs by MySales code. When running the report, a set of values separated by commas is passed. Parameter type — “List”.
WHERE item_code = :item_code— used to select a single SKU by ERP code. When running the report, one text code is passed. Parameter type — “Text”.
WHERE item_code IN ([item_code_list]) — used to select multiple SKUs by ERP code. When running the report, a set of text values separated by commas is passed. Parameter type — “List”.
WHERE (sku_id = :sku_id OR :sku_id = 0) — a complex parameter that allows either selecting a specific SKU or displaying all SKUs. If the value 0 is specified when running the report, all SKUs will be displayed. If another value is specified, only the corresponding SKU will be shown.

The examples above are universal and can be applied to different parameter types and fields.

Typical Examples of Parameter Usage

Single Value

WHERE sku_id = :sku_id

List of Values

WHERE sku_id IN ([sku_list])

Complex Parameter

WHERE (sku_id = :sku_id OR :sku_id = 0)

Allows:

– displaying a single SKU;

– or displaying all SKUs if the parameter value equals 0.

Important

The parameter name in the SQL query must exactly match the parameter name in the “Parameters” block.

For example, if :sku_id is used in SQL, the parameter in the settings must also be named sku_id.

If the names do not match, the report will not execute correctly.

Working with the Database

To work with the report constructor, it is important to understand the database structure: what data is stored in the tables, what the field names are, and how to link data from different tables.

You can open the database structure by clicking the corresponding button and selecting the “Database” option. After that, a window will open displaying all tables and their fields.

Where to Find the Database Structure

Click the:

"..." button.

Select:

"Database"

Users can:

browse tables;
browse fields;
search columns;
find aliases;
analyze relationships.

Common Errors

Query Executes Too Long

Recommended actions:

use TOP;
limit WHERE;
test JOIN operations separately.

Invalid Column Name

The SQL contains a non-existent field.

Check the column name.

Incorrect Syntax Near

SQL syntax error.

Check:

commas;
brackets;
table names;
JOIN.

Ambiguous Column Name

The field exists in multiple tables.

Use table aliases:

p.SKU_ID

Timeout Expired

The query takes too long to execute.

Recommended:

add WHERE;
reduce the period;
avoid SELECT *.

GROUP BY Error

All fields without aggregate functions must be included in GROUP BY.

Parameter Not Found

Check:

parameter format;
name;
transfer to Parameters.

Best Practices

Using Aliases

It is recommended to use aliases to simplify working with columns.

Example:

SELECT sku_id SKU,
       article_name NAME
FROM products

Aliases are used by the system as field codes in the constructor.

Do Not Use SELECT *

SELECT * loads all table columns, even if some of them are not used.

In large reports, this can significantly slow down execution.

Minimize JOIN Usage

A large number of JOIN operations:

increase system load;
slow down SQL execution.

Use Parameters

Parameters:

make reports universal;
reduce the number of separate reports.

First Test the Query on a Small Data Set

Before running a large report, it is recommended to test the SQL query on a limited sample:

SELECT TOP 1000 ...

This helps identify errors faster and avoids running a heavy query on the entire data set.

Reports Composer

Overview

What is the Report Composer?

Opening the Composer

Where is the Composer located?

Composer Interface

Main Composer Sections

Top Panel

First SQL Report

Report Selection Window

Basic SQL Queries

SELECT

Basic SQL Functions

SELECT DISTINCT

SELECT TOP

MIN/MAX

COUNT, AVG, SUM

WHERE

Logical Operators

AND

OR

NOT

IN

LIKE

IS NULL / IS NOT NULL

ORDER BY

JOIN

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

GROUP BY

Creating the First Report

Report Selection

Report Categories

Main Report Actions

Additional Actions

SQL Editor

SQL Editor Panel

SQL Query Name

Execute Button

Editor Toolbar

SQL Tabs

Recommendations

Working with Columns

Expand By and Group By

Working with Groups

Example use cases:

Configuration

Working with Parameters

Standard Use Cases for Parameters in SQL Queries

Working with the Database

Where to Find the Database Structure

Common Errors

GROUP BY Error

Parameter Not Found

Best Practices