Creating a Report

Owned by Sergey Schiller (Unlicensed)
Last updated: Jun 11, 2022
12 min read

After clicking "New", a new report designer will open in the report selection window:

There are three blocks in the report designer:

  • Script, category - block in which the SQL query itself is written. Described in section Work with queries;

  • Tables - block in which report columns are added and configured. Described in section Work with columns;

  • Parameters - a block in which variable parameters are configured to run the report. Described in section Work with parameters;

Working With Queries

In this block, the user writes a sql query that will have to load data from tables in the database. Queries are written in SQL. User must have basic SQL programming skills to write queries. SQL at a basic level can be learned in the shortest possible time (up to 1 day).

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

This block of documentation describes possible actions with an already written block of sql code, working with basic queries that are necessary to create basic reports is described in the section Basic SQL queries.

The SQL query code is entered in the "Script, Category:" block:

After the script code is written, the following operations are possible with it:

  • Run to check - to run the report, click the button . After pressing the button, the script will start executing, it can take from several seconds to minutes, depending on the complexity and amount of data that is being downloaded. If the code compiler sees an error in the code, it will display it at the bottom in the red block:

  • If there are no errors, a message will appear at the bottom of the window that the report is loading, and upon completion of loading, a window with the result of the request will open. If the query can theoretically display more than 10,000 thousand rows, it is advisable to use the TOP 1000 or TOP 10000 limits after the SELECT command to check it;

  • Stop the report - if the report takes a very long time to load, or you just decided to stop the execution - this can be done by clicking on . This button turns red and active when the report is running.

  • Transfer of fields selected in SELECT to report columns. Columns are always added to the report with a request. To add columns, click the button . After clicking, the system will itself determine the columns and their names and add them to the report:

  • The system will also display these columns in the "Tables" block, where user can customize them. Column settings are described in the "Work with columns" section. The system automatically adds all columns. Unnecessary ones are deleted by clicking on . Later they can be added from the "Table" block.

  • Transfer the report parameters to the "Parameters" field for customization. To transfer parameters, click . After clicking, the system itself will determine what the parameters are and will transfer their names to the "Parameters" field. Parameters that have not been migrated are displayed in the sql query editor in red, and those that have been migrated in green. Each parameter must be configured, how to configure them and in what formats the parameters can be in the sql query code is described in the "Work with parameters" section.

Working With Columns

All fields selected through SELECT in sql query are displayed in two places - above the constructor and in the "Tables" block:

  • The system will also display these columns in the "Tables" block, where user can customize them. Column settings are described in the "Work with columns" section. The system automatically adds all columns. Unnecessary ones are deleted by clicking on . Later they can be added from the "Table" block.

  • Transfer the report parameters to the "Parameters" field for customization. To transfer parameters, click . After clicking, the system itself will determine what the parameters are and will transfer their names to the "Parameters" field. Parameters that have not been migrated are displayed in the sql query editor in red, and those that have been migrated in green. Each parameter must be configured, how to configure them and in what formats the parameters can be in the sql query code is described in the "Work with parameters" section.

All fields selected through SELECT in sql query are displayed in two places - above the constructor and in the "Tables" block:

Initially, all fields are automatically added to the report after clicking the button  in the sql query window, and are displayed above the constructor and in the "Tables" block. After deleting any field, you can then add it back by clicking on the + next to its name in the "Tables" block.User can change the order in which the fields are displayed - simply by dragging and dropping their names in the upper block. User can also change the size of the columns by stretching them. The appearance of the columns in the upper block fully corresponds to how they will be displayed in the report.

To change the parameters of a field, click on its name - after that a window with the following parameters will open:

  • Style All - editing the general style of the report. The process is similar to editing in any other text editor;

  • Expand by - this field is described in detail in the section Extend By and Group By;

  • Group by - this field is described in detail in the section Extend By and Group By;

  • Name - the user sets the name of this column in the report;

  • Key - indicates what data to pull into the column. For example, there is a request:
    SELECT SKU_ID SKU, STORE_ID STORE FROM SALES_SKU
    If user puts the STORE field code in the SKU column settings, then the data from the STORE column will be displayed in the SKU column in the report. But this is possible only if one SKU column is displayed, if both SKU and STORE columns are displayed - then adding the field code will not work.
    You cannot add a field code to the same field.

  • Data type - selection of which data type should be displayed in the field. The following data types are supported:

    • Text - for text fields - for example "Article name";

    • Number - for numeric fields - for example, "Sales volume";

    • Data - for fields with any date format - for example, "Week of sales" or "Date of sales";

  • Format - this field appears if you select the data type "Number" or "Date".

  • Link - allows user to add a link to another page, data or something else. For example, there is a request:
    SELECT SKU_ID SKU, SITE_LINK LINK FROM MONITORING
    The SKU column displays product codes, and the LINK column displays links to the site. In this case, if user select a link to LINK in the "Link" field in the SKU column settings, then in the report, when user clicks on the number in the SKU column, a link from the LINK column will open.
    The link can only be added to other columns;

  • Width - the user can limit the possible field width in the report. The value must be entered in pixels. If the width is auto, then the field will be stretched to fit the text.

  • Style Сell - editing a separate style for displaying information in the report. The process is similar to editing in any other text editor;

Extend By and Group By

These two column setting fields are always set together. These parameters allow you to implement cross-columns - when multiple data from other columns are displayed in one column.

For example, this allows user to display data on product sales for several weeks in one line, without manually adding a separate column for each week. This functionality will be explained in more detail in the example below.

  • The user created a request to display sales for the first week for the last 5 years for the selected item 56 across all stores (Store 1 and Store 2):

  • As a result, he received the following report:

You can see that the report displayed 10 lines, but in a not very convenient format. It would be much more convenient if all weeks and sales were displayed in one line with the product, then there would be only two lines for each store. In this form:

  • For such transformations, use Expand By. First, user needs to remove the column from the display, the data from which will need to be expanded and inserted into another column. In this example, this is the column with weeks - WEEK WK. Therefore, it is necessary at the top in the row of columns to click on  near the WEEK column;

  • The next step is to click on  near column in which you want to display another column. In our case, these are the Sales and Stock columns - therefore, click on the button next to both columns - after clicking, which column should be displayed inside - in our case it is WK (Week);

  • After that, WK is also displayed in the Expand By field in the settings of the corresponding columns. For Expand by, user can use any columns that are not displayed in the report, but are present in the query. Next, user needs to configure in which column the data will be grouped by. In this example, it is necessary that the data be grouped by stores - that is, in the Group by field, select STR (the name of the column that pulls data by stores in the sql query). User can select Group by only for one column, in other columns with Expand by this selection will be automatically pulled up.

  • After completing all the settings, user can click on  and see how the data is displayed:

Working With Parameters

Parameters are values that the user selects when running a report. This allows user to select only data that meets the user's criteria.

Parameters are automatically transferred from the code in the sql editor to the "Parameters" block after clicking the button . After the parameters have appeared in the "Parameters" block, they can be configured.

There are two types of parameters:

  • :parameter - such parameters pass one specific value and must be specified in the code as :
    where variable = :parameter;

  • [parameter_list] - such parameters pass a set of values and must be specified in the code as :
    where variable [parameter_list];

For example, if user needs to pass the number "5" as a parameter, user uses the format :parameter after the equal sign. As a result, after starting the report, the parameter will work in the code as:
where value = 5.

If user needs to transfer a set - for example (1,3,8), then user uses the format [parameter_list] without equals sign. As a result, after starting the report, the parameter will work in the code as:
where value in (1,3,8).

User can use any convenient parameter names, the main thing is to use the correct format.

To configure a parameter, click on the parameter name in the "Parameters" block. After clicking, a window opens with the following parameter settings:

  • ID - parameter identifier - it pulls up automatically according to the field it is applied to, it cannot be changed;

  • Name - parameter name - must be equal to the parameter name in the code;

  • Display - parameter name that will be displayed when the report is run;

  • Type - selection of the parameter type - it is very important to specify the correct type for correct operation of the parameter and to avoid errors. The following parameter types are supported:

    • Text - the parameter will pass a text value, can only be used with the parameter format: parameter;

    • Number - the parameter will pass a numeric value, can only be used with the parameter format: parameter;

    • Data - the parameter will transmit the date in the YYYY-MM-DD format, can be used only with the parameter format: parameter;

    • Week - the parameter will transmit the date in ISO format week - YYYYWW, can only be used with the parameter format: parameter;

    • Month - the parameter will transmit the date in the ISO format month - YYYYMM, can only be used with the parameter format: parameter;

    • List - the parameter will pass a set of values in any format. It is used when it is necessary to pass more than one value. For example (1, 3, 8) or (store, warehouse) or (202015, 202025). Can only be used with the parameter format [parameter_list];

    • Select - the parameter will pass a set of values selected by a special request, which is indicated in the "Query" field. Can only be used with the parameter format: parameter;

  • Min - minimum parameter value. Can be used for numbers, dates, weeks, months;

  • Max - the maximum value of the parameter. Can be used for numbers, dates, weeks, months;

  • Query - filled only for the "Select" format. To display possible queries, user have to enter the text 'select' in the field, available queries will automatically appear in a list. When the report is run, the information pulled from the request will be displayed in a drop-down list when the parameter value is selected. The following queries are now automatically supported (but you can also create additional ones):

    • report_select_stores - used to select a store from the list for the parameter value;

    • report_select_groups - used to select a group from the list for the parameter value;

    • report_select_managers - used to select a manager from the list for the parameter value;

    • report_select_contracts - used to select a contract from the list for the parameter value;

    • report_select_suppliers - used to select a supplier from the list for the parameter value;

    • report_select_whs - used to select a warehouse from the list for the parameter value;

    • report_select_fcst - used to select a forecast number from the list for the parameter value;

  • Index - can be entered only for parameters such as Date, Week, Month. The index allows user to automatically subtract or add a value to the parameter.
    So if user sets the Date and Index parameter to -10, then by default when the report is run, the date value will be displayed 10 days less than the specified default date in the "Value" field. If the value field is left blank, the date will be subtracted from the current one;

  • Value - a default value is entered that will be displayed when you select options before running the report.

Standard Cases For Using Parameters in SQL Query

Ниже приведены варианты задания параметров в sql запросе для разных типов данных:

where sku_id = :sku_id - used to select a specific sku by the MySales code. When the report is run, one SKU number is passed to the parameter. The parameter type must be "Number";
where sku_id [sku_id_list] - used to select multiple sku by MySales code. When the report is run, a set of SKU numbers separated by commas is passed to the parameter. Parameter type there should be a "List";
where item_code = :item_code - is used to select a specific SKU using the internal code. When the report is run, one text code is passed to the parameter. The parameter type must be "Text";
where item_code [item_code_list] - is used to select several sku by the internal code. When the report is run, a comma-separated set of SKU SCP text codes is passed to the parameter. Parameter type must be "List";
where ((sku_id = :sku_id) or (:sku_id = 0)) - complex parameter - used for the ability to display all sku or one specific one. In this case, to display all sku, the value 0 is put down for the parameter when starting the report, or the number of a specific sku to display one sku;

The above examples of setting parameters in the code are relevant for all types of parameters and fields.

Working With Database

To work with the report composer, it is very important to understand the structure of the database in order to understand what data to get from which tables, what are the names of the fields, how to link data from different tables.

User can call the structure of the base by clicking on  and choosing "Show DB". After selection, a window will open in which all tables and all fields in them will be displayed.

A search works in the database window, which allows user to find the tables and columns.