Changes for page 6. Flexible Reports Guide
Last modified by Iaroslav Platonov on 2026/06/08 15:20
Summary
-
Page properties (1 modified, 0 added, 0 removed)
-
Attachments (0 modified, 1 added, 0 removed)
Details
- Page properties
-
- Content
-
... ... @@ -1,0 +1,134 @@ 1 += CONTENT = 2 + 3 += 1. INTRODUCTION = 4 + 5 +CENTO is a multifunctional software platform for data collection, processing, and display. This guide describes the flexible reporting tool in CENTO. 6 + 7 +It is assumed that the user, having read the User's Manual, is already familiar with the basic elements of the CENTO interface. 8 + 9 +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. 10 + 11 +Flexible reports can be created both within a single time unit (day, month, year) and for a period specified by the user. 12 + 13 +The manual contains a description of the following operations: 14 + 15 +* Creating and editing report templates in the web interface; 16 +* Generating reports from templates; 17 +* Viewing and editing created reports; 18 +* Downloading and uploading report files in Excel is supported with version control. 19 + 20 += 2. QUERY LANGUAGE = 21 + 22 +== 2.1. General information == 23 + 24 +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. 25 + 26 + There are two types of files: 27 + 28 +* A template is used to define the layout and structure of a report, including queries to archived data. 29 +* A report is a ready–made document generated from a template that contains the data received as a result of filling in the cells. 30 + 31 +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. 32 + 33 +Editing of the report template is also available through the web interface, as described in section 3. 34 + 35 +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. 36 + 37 +== 2.2. Timestamps == 38 + 39 +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: 40 + 41 +**#created** displays the date and time when the report was created. 42 + 43 +**#related** displays the date associated with the report (in flexible reports for the period, the timestamp is ignored). 44 + 45 +**#begin** displays the start date of the report period (only for flexible reports for the period). 46 + 47 +**#end** displays the end date of the report period (only for flexible reports for the period). 48 + 49 +Each timestamp should be placed in a separate cell. 50 + 51 +== 2.3. Query format == 52 + 53 +The query is divided into 4 parts/groups separated by the ":" symbol. 54 + 55 +For reports for a day, month, and year, the query is built as described in Figure 2.2. 56 + 57 +[[image:Screenshot 2025-12-10 at 19.43.28.png]] 58 + 59 +Fig.2.2 – Query example 60 + 61 +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. 62 + 63 +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). 64 + 65 +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). 66 + 67 +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. 68 + 69 +**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~*~*. 70 + 71 +The first two parts of the query can be replaced with another, more convenient formula with **#date**, which will be described below. 72 + 73 +2) The entry** el-dev-30-ea_imp-3m **has the following parts: 74 + 75 +* **el** (electricity) is electrical data. Data on other measurements (heat, pressure, emissions, etc.) are requested with the entry **en** (energy); 76 +* **dev-30** (device) is accessing a device with an id 30 for which data needs to be downloaded; 77 + 78 +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** 79 + 80 +* **ea_imp** is the channel of the device from which the information is taken. The following channels can be used for electricity: 81 + 82 +* ea_imp - energy A+ 83 +* ea_exp - energy A- 84 +* er_imp - energy R+ 85 +* er_exp - energy R- 86 +* pa_imp - power A+ 87 +* pa_exp - power of A- 88 +* pr_imp - power R+ 89 +* pr_exp - power R- 90 +* ra_imp - readings of A+ 91 +* ra_exp - readings of A- 92 +* rr_imp - readings of R+ 93 +* rr_exp - readings of R- 94 + 95 +The specified channels can be renamed to their decryptions in the config.toml configuration file of the sed_channels service. 96 + 97 +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. 98 + 99 +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. 100 + 101 +**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. 102 + 103 +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. 104 + 105 +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. 106 + 107 +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). 108 + 109 +Flexible period reports also allow time offsets in queries, but **only by day**. For example, **#end+2d** or **#begin-1d**. 110 + 111 +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. 112 + 113 +Table 2.3. Comparative table of y-m-d and #date formulas 114 + 115 +|**№**|(% style="width:150px" %)**Format with y-m-d**|(% style="width:151px" %)**Format with #date**|(% style="width:192px" %)**Description**|(% style="width:106px" %)**Type of report in which the formula is applied** 116 +|1|(% style="width:150px" %)y-m-d3:*:el-dev-201-ea_imp-30m:sum|(% style="width:151px" %)#date-d3:d:dev-201-ea_imp:sum|(% style="width:192px" %)Sum of all half-hours on the 3rd day of the month|(% style="width:106px" %)monthly, daily 117 +|2|(% style="width:150px" %)y-m-d1:h00-m30:el-dev-201-ea_imp-30m:sum|(% style="width:151px" %)#date-d1-h00-m30:*:dev-201-ea_imp|(% style="width:192px" %)Value of the half-hour is 00-30 on the first day|(% style="width:106px" %)monthly, daily 118 +|3|(% style="width:150px" %)y-m-d+1:h00-m30:el-dev-201-ea_imp-30m|(% style="width:151px" %)#date+1-h00-m30:*:dev-201-ea_imp|(% style="width:192px" %)Value of the half-hour is 00-30 of the day following the selected one|(% style="width:106px" %)daytime 119 +|4|(% style="width:150px" %)y-m-d+10:h00-m30:el-bg-42-30m|(% style="width:151px" %)#date+10-h00-m30:*:bg-42|(% style="width:192px" %)Value of the half-hour is 00-30 per day +10; checking the operation of the electro-billing group|(% style="width:106px" %)daytime 120 +|5|(% style="width:150px" %)y-m-d-1:h00-m30:el-dev-201-ea_imp-30m|(% style="width:151px" %)#date-1-h00-m30:*:dev-201-ea_imp|(% style="width:192px" %)Value of the 00-30 half-hour per day is -1|(% style="width:106px" %)daytime 121 +|6|(% style="width:150px" %)y-m-d1-1:h01-m00:el-dev-201-ea_imp-30m|(% style="width:151px" %)#date-d1-1-h01-m00:*:dev-201-ea_imp|(% style="width:192px" %)Value of the 01-00 half-hour for the last day of the previous month|(% style="width:106px" %)monthly, daily 122 +|7|(% style="width:150px" %)y-m-d1-2:h00-m30:el-dev-201-ea_imp-30m|(% style="width:151px" %)#date-d1-2-h00-m30:*:dev-201-ea_imp|(% style="width:192px" %)Value of the half-hour is 00-30 for the penultimate day of the previous month|(% style="width:106px" %)monthly, daily 123 +|8|(% style="width:150px" %)y-m-d:*:el-dev-201-ea_imp-30m:sum|(% style="width:151px" %)#date:d:dev-201-ea_imp:sum|(% style="width:192px" %)Sum of all half-hours for the selected day|(% style="width:106px" %)daytime 124 +|9|(% style="width:150px" %)y-m:*:el-dev-201-ea_imp-30m:sum|(% style="width:151px" %)#date:m:dev-201-ea_imp:sum|(% style="width:192px" %)Sum of all half-hours for the selected month|(% style="width:106px" %)annual, monthly, daily 125 +|10|(% style="width:150px" %)y:*:el-dev-201-ea_imp-30m:max|(% style="width:151px" %)#date:y:dev-201-ea_imp:max|(% style="width:192px" %)The maximum half-hour for a selected year|(% style="width:106px" %)annual, monthly, daily 126 +|11|(% style="width:150px" %)y-m-1m:*:el-dev-201-ea_imp-30m:min|(% style="width:151px" %)#date-1m:m:dev-201-ea_imp:min|(% style="width:192px" %)Minimum half-hour for the previous month|(% style="width:106px" %)annual, monthly 127 +|12|(% style="width:150px" %)y-m1:*:en-dev-220-1021-1h:mean|(% style="width:151px" %)#date-m2:m:ch-1021-1h:mean|(% style="width:192px" %)Output the average of all hours from channel 1021 at device 220 for February of the year selected by the user|(% style="width:106px" %)annual, monthly, daily 128 +| |(% style="width:150px" %) |(% style="width:151px" %)**New functions available only with #date**|(% style="width:192px" %) |(% style="width:106px" %) 129 +|13|(% style="width:150px" %) |(% style="width:151px" %)#date-h08-#date-h17:*:dev-201-ea_imp:sum|(% style="width:192px" %)Sum of energy as of the report date from 8 a.m. to 5 p.m.|(% style="width:106px" %)daytime 130 +|14|(% style="width:150px" %) |(% style="width:151px" %)#date+1-h08-#date+1-h17:*:dev-201-ea_imp:sum|(% style="width:192px" %)Sum of energy for the day following the reporting day from 8 a.m. to 5 p.m.|(% style="width:106px" %)daytime 131 +|15|(% style="width:150px" %) |(% style="width:151px" %)#date-d1-h08-#date+1-h17:*:dev-201-ea_imp:sum|(% style="width:192px" %)Sum of energy from the first day of the month at 8 a.m. to the day following the reporting day at 5 p.m.|(% style="width:106px" %)daytime 132 +|16|(% style="width:150px" %) |(% style="width:151px" %)#date-d1-1-h08-#date+1-h17:*:dev-201-ea_imp:sum|(% style="width:192px" %)Sum of energy from the last day of the previous month, 8 a. m. to the day following the reporting one, up to 5 p.m.|(% style="width:106px" %)daytime 133 + 134 +
- Screenshot 2025-12-10 at 19.43.28.png
-
- Author
-
... ... @@ -1,0 +1,1 @@ 1 +XWiki.Lana - Size
-
... ... @@ -1,0 +1,1 @@ 1 +36.8 KB - Content