CONTENT

1. INTRODUCTION

CENTO is a multifunctional software platform for data collection, processing, and display. This guide describes the flexible reporting tool in CENTO.

It is assumed that the user, having read the User's Manual, is already familiar with the basic elements of the CENTO interface.

Flexible reports in CENTO are designed to create, edit, upload/download Excel reports based on the collected data. This function allows to create reports of any form and composition.

Flexible reports can be created both within a single time unit (day, month, year) and for a period specified by the user.

The manual contains a description of the following operations:

• Creating and editing report templates in the web interface;
• Generating reports from templates;
• Viewing and editing created reports;
• Downloading and uploading report files in Excel is supported with version control.

2. QUERY LANGUAGE

2.1. General information

The core of the flexible reports functionality is a query language that enables the extraction of archived data from the database. Excel (.xlsx) files are used to work with this data.

 There are two types of files:

• A template is used to define the layout and structure of a report, including queries to archived data.
• A report is a ready–made document generated from a template that contains the data received as a result of filling in the cells.

The template is generated by the user in .xlsx file and then uploaded via CENTO forms. Queries are entered into the cells of the template, and the system inserts the resulting value into the cell where the query was placed.

Editing of the report template is also available through the web interface, as described in section 3.

The template can contain multiple sheets, Excel formulas, charts, conditional formatting, and a summary report. An example of the generated sheet of an .xlsx file is shown in Figure 2.1.

2.2. Timestamps

Timestamps can be specified in the query language. They are designed to transmit information about the date and time associated with the report.  There are four types of timestamps:

#created displays the date and time when the report was created.

#related displays the date associated with the report (in flexible reports for the period, the timestamp is ignored).

#begin displays the start date of the report period (only for flexible reports for the period).

#end displays the end date of the report period (only for flexible reports for the period).

Each timestamp should be placed in a separate cell.

2.3. Query format

The query is divided into 4 parts/groups separated by the ":" symbol.

For reports for a day, month, and year, the query is built as described in Figure 2.2.

Fig.2.2 – Query example

For example, the query y-m-d:h10-m00:el-dev-30-ea_imp-3m:sum consists of the following parts. The last part, "sum," in the example above, is optional.

1) y-m-d: means that the year, month, and day values from the date associated with the report will be inserted into the query (specified when creating the template via the web-interface). Only part of the date associated with the report can be used. For example, y2024-m-d means that the month and day should be substituted, while the year (2024) remains fixed as specified in the request. y2024-m2-d means that only the day should be substituted with the year set to 2024 and the month set to 2 (February).

It is also possible to omit d (y-m, or omit m and d (y:), this means that we are interested in the entire interval (if only the year is specified, then the entire annual interval, if the year and month are specified, then the monthly interval).

The d can also be omitted (y-m:), or both m and d can be omitted (y:). This means the entire interval is considered, if only the year is specified, the entire annual interval is used, and if the year and month are specified, the monthly interval is used.

h10-m00 means that the values for the specified hour and minute will be inserted into the query. By omitting seconds, CENTO understands that the entire minute interval is of interest. This means the sample will be from 10:00 to 10:01 (excluding data for 10:01:00, effectively from 10:00:00 to 10:00:59). To make a sample for an hour, write h10 omitting minutes and seconds. The time does not need to be specified at all, for example, if the interest is in the data for the day. However, to ensure the query is correct, write “*” in place of the time, like this: **y-m-d::el-dev-30-ea_imp-3m:sum**.

The first two parts of the query can be replaced with another, more convenient formula with #date, which will be described below.

2) The entry el-dev-30-ea_imp-3m has the following parts:

• el (electricity) is electrical data. Data on other measurements (heat, pressure, emissions, etc.) are requested with the entry en (energy);
• dev-30 (device) is accessing a device with an id 30 for which data needs to be downloaded;

Instead of dev, bg (billing group) can be used to request data for a billing group, for example, y-m-d:h10:el-bg-22-30m:sum

• ea_imp is the channel of the device from which the information is taken. The following channels can be used for electricity:

• ea_imp - energy A+
• ea_exp - energy A-
• er_imp - energy R+
• er_exp - energy R-
• pa_imp - power A+
• pa_exp - power of A-
• pr_imp - power R+
• pr_exp - power R-
• ra_imp - readings of A+
• ra_exp - readings of A-
• rr_imp - readings of R+
• rr_exp - readings of R- 

The specified channels can be renamed to their decryptions in the config.toml configuration file of the sed_channels service.

When referring to energy measurements instead of electrical, the channels are encoded with numbers. For example, it would be recorded as en-dev-30-15-3m, which represents 3-minute data for the channel with id = 15 in the device with id = 30.

Possible interval options include: 1m for minute data, 3m for 3-minute data, 15m for 15-minute data, 20m for 20-minute data, 30m for 30-minute data, 1h for hourly data, 1d for daily data, 1m for monthly data, as well as other intervals that the device may return.

sum is an aggregating function that, in this case, calculates the sum. In addition to sum, other functions may include min for minimum, max for maximum, and mean for average.

The aggregating function may be followed by an optional parameter, the coefficient, written as a k number, where the number is the coefficient value, for example, k0.1 (i.e. sum-k0.1): the result of query execution will be multiplied by it.

The data request form for flexible period reports is similar to reports for a day, month, or year. However, in the date block at the beginning of the query, instead of a specific date (y-m-d), the start and end of the report period are used as #begin-#end. For example, the query #begin-#end:h10-m00:el-dev-30-ea_imp-3m:sum means that the total active energy value (reception) is requested for the user-defined period from 10:00 a.m. at the beginning of the period (#begin) to 10:00 a.m. at the end of the period (#end) from a device with ID 30.

Queries for flexible reports for a period can be generated, indicating only the beginning of the period (#begin) or only the end (#end). In this case, CENTO will fill the cell with the value only for the specified time, ignoring the formula at the end of the query (if there is one).

Flexible period reports also allow time offsets in queries, but only by day. For example, #end+2d or #begin-1d.

The formulas described above can be replaced with formulas with #date. Using formulas with #date to expand the query toolkit. The comparison of queries is performed in Table 2.3. Additional queries are described in lines 13 - 16 in the specified table.

Table 2.3. Comparative table of y-m-d and #date formulas

2.4. Query examples

y2019-m-d:h10:el-dev-30-ea_imp-3m:sum-k0.001

The is data for 2019, the month and day values from the date associated with the report, the time from 10:00 a.m. to 11:00 a.m., data from the electricity table (el) for three minutes (3m), data on the device with id = 30, for the ea_imp channel, apply the sum aggregating function with the coefficient 0.001 (result is in megawatts).

y-m1:*:en-dev-220-1021-1d:sum

The data is for the 1st month of the year from the date associated with the report. The data is taken from the energy resources table for daily records for device 220 and channel 1021. The day is skipped here, so there will be a selection based on the range of all days from the 1st to the 2nd month. If, for example, the year is 2019, then the range is: from 2019-01-01 to 2019-02-01 excluding the 1st day of the 2nd month.

y-m+1-d1:h00-m00:el-dev-1202-ra_imp-30m

The data is for 00:00 on the 1st of the month following the date associated with the report. This request outputs electricity readings for a device with id = 1202. You can also write m+2, m+3, y+5, etc.

y-m-d1-1:h10-m00:el-dev-1202-ra_imp-30m

The data is for10:00 a.m. on the last day of the previous month of the device with Id 1202, the ra_imp channel from the database of 30 minutes.

#begin-#end:h00-m00:el-dev-46-ea_imp-3m:sum

The data is for the period from 00:00 of the date #begin to 00:00 of the date #end. The data is downloaded using the active received energy from the device with ID 46. The value is given as the sum of the three-minute values of the specified interval.

#begin:h06-m30:el-dev-58-ea_exp-30m

The data of the half-hour starting at 6:30 a.m., the date indicated at the beginning of the requested period. The data is on the output of active energy from a device with ID 58.

#end+2d:h02-m00:el-bg-22-30m

The data of the half-hour starting at 2:00 a.m., the date running 2 days after the end of the period requested by the user. The data is requested for the billing group with ID 22 according to the readings of the received active energy.

2.5. Output of information on devices to the report. Device codes and tags.

The request #dev.201.IP displays the IP address of the device with id = 201201 from the configuration in the report. Instead of the device ID, the device code can be specified.

#dev.201.Name displays the device name with id = 201201 from the configuration in the report. Instead of the device ID, the device code can be specified.

#dev.201.VendorNumber displays the serial number of the device with id = 201 from the configuration in the report. Instead of the device ID, the device code can be specified.

The serial number of the device can also be taken from the tag if the device can output its number according to any protocol. For example, the SATEC device can be queried via Modbus, and the serial number can be retrieved from the ti11001 tag. To display this tag in the report, write the formula y-m-d:*:ti11001 where ti11001 can be replaced with the tag code. In the same way, add any tags from the configuration to flexible reports.

In query formulas, device IDs can be replaced with device codes. Unlike the id, the code can contain Latin letters, which allows it to encode a lot of useful information. For example, the code P22S1Y8 may mean that the device collects data on PS22 in the first bus section in cell 8. To write the code in the request, add the channel to it, for example, Energy A+, which is encrypted as ea_imp by default.

Thus, the formula y-m-d1:*:el-dev-201-ea_imp-30m:sum can be replaced by the formula y-m-d1:*:E123457ea_imp:sum. A similar logic applies to all the formulas described above.

3. MANAGING REPORT TEMPLATES

Flexible reports are managed in the Flexible Reports window accessible using the Reports menu: Reports -> Flexible Reports.

A template generated in accordance with the query language specified in Section 1 is uploaded to the Flexible Reports page (see Figure 3.1) by clicking the (Create Template) button   in the upper-right corner of the template list. 

Figure 3.1 – Flexible Report Template Management window

After clicking the button  (see Figure 3.1), a download dialog box will open. Enter the template name, type, object, and category in this dialog box. Then, select the template file (Excel file), configure auto generation if needed, select a network resource and/or email address for saving automatically generated reports (see Figure 3.2), and click the button  to complete. 

Fig.3.2 – Template upload form

The template type can be annual, monthly, daily, as well as annual for the period, monthly for the period, and daily for the period. It is selected depending on the requirements for the report. The template type affects the type of calendar when generating reports, the possibility of autogeneration (only annual, monthly, and daily reports are automatically generated), as well as the applicability of timestamps and query types in section 2 of this manual.

The Object field is used to select an object of the information model. Linking a report to an object differentiates access to reports and filter templates in the table.

The Category field is used to generate reports based on the "Balances" module.

All the templates can be downloaded to the PC using the "Download" button  and uploaded using the ”Select a file” button  in the template settings window, which opens when clicking on the "Template setup" button .

A template can be deleted using the "Delete selected" button  after selecting the checkboxes next to the templates in the first column of the template list (see Figure 3.1).

The “Interactive mode” button  enables the report mode in the web interface. When interactive mode is enabled, the report opens in the web version of Excel spreadsheets (Fig. 3.3). 

Fig.3.3 – Interactive mode

The interactive mode enables working with the report directly through the web interface without downloading or uploading an Excel file. In this mode, the report and all calculation results can be viewed, the display start time can be selected, the report can be downloaded as an .xlsx file, and the report can be edited using the button .

In editing mode (see Fig. 3.4), both Excel formulas and CENTO queries within the report can be viewed and edited as described in Section 2 of this manual.

Fig.3.4 – Editing mode

In the editing mode interface, report sheets are shown in the lower-left corner, allowing switching between them (however, new sheets can only be added via Excel when uploading a template). The lower-right corner contains buttons and a zoom slider.

To edit the contents of a cell, double-click on the cell with the left mouse button.

After editing, the report should be saved  and switched back to view mode  to verify the results. All changes made are applied instantly, allowing immediate result checking.

4. MANAGING ACCESS RIGHTS TO REPORTS

Access rights management for flexible reports is based on the information model. The configuration of the information model is detailed in the Information Model Configuration Manual.

Depending on the task, either a separate structure can be created for flexible reports, or an existing one can be used.

To link a report to an object, open the template editing form (see Fig. 3.2) by clicking the template settings button next to a template, and select an information model object in the Object field. 

Next, find the selected object in the User access rights interface (Settings – System – User Access Rights) and use the check marks in the Flexible Reports line to select which roles will have access to the flexible report bound to the object (Fig. 4.1).

Figure 4.1 – Rights to flexible reports in the user settings

Users without roles that have access to flexible reports bound to an object will not see those reports in the list of available templates.

5. REPORT MANAGEMENT

Reports are generated based on the uploaded templates using the "Create report" button .

Report generation can be performed in the following ways:

1. manually – by clicking the "Create report" button  in the field of the template in the "Template list". In the pop-up window (see Figure 5.1), enter the report name, select In a day or Per interval for which the report(s) will be generated, and then click the button  to proceed.