...
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;
Work 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 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.
Work 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.
Work with columns
All fields selected through SELECT in sql query are displayed in two places - above the constructor and in the "Tables" block:
...
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.
...
After completing all the settings, user can click on and see how the data is displayed:
...
Work 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.
...
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 запросе для разных типов данных:
...
The above examples of setting parameters in the code are relevant for all types of parameters and fields.
Work 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.
...