...
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 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:
...
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.
...
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.
...
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.
...