6. Flexible Reports Guide

• 1. Introduction

  Overview of the flexible reporting tool in CENTO — purpose, capabilities, and scope.

• 2. Query Language

  Syntax for extracting archived data: timestamps, query format, aggregation functions, device codes, and tags.

• 3. Managing Report Templates

  Uploading, configuring, editing, and deleting report templates in the Flexible Reports window.

• 4. Managing Access Rights to Reports

  Linking reports to information model objects and configuring role-based access rights.

• 5. Report Management

  Generating reports manually and automatically, versioning, downloading, and publishing reports.

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 Manual, is already familiar with the basic elements of the CENTO interface.

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

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

The manual describes 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 format with version control

2. Query Language

2.1. General information

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

There are two types of files:

• Template — defines the layout and structure of a report, including queries to archived data.
• Report — 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 as an .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 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 a period, the timestamp is ignored).
• #begin — displays the start date of the report period (only for flexible reports for a period).
• #end — displays the end date of the report period (only for flexible reports for a period).

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.

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, is optional.

1. y-m-d: — means the year, month, and day values from the date associated with the report will be inserted into the query. Only part of the date can be used. For example, y2024-m-d fixes the year to 2024 while substituting month and day. y2024-m2-d fixes the year to 2024, the month to February, and substitutes only the day.

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

   The first two parts of the query can be replaced with the #date formula as described in Table 2.3 below.

2. h10-m00 — means that values for the specified hour and minute will be inserted. Omitting seconds means the entire minute interval is of interest (from 10:00:00 to 10:00:59). To sample an hour, write h10 omitting minutes and seconds. To omit time entirely (e.g. for daily data), write * in place of the time: y-m-d::el-dev-30-ea_imp-3m:sum.

3. el-dev-30-ea_imp-3m has the following parts:

   • el — electrical data. Other measurements (heat, pressure, etc.) use en (energy).
   • dev-30 — device with ID 30. Instead of dev, use bg for billing groups (e.g. el-bg-22-30m).
   • ea_imp — the data channel. Available electricity channels: ea_imp, ea_exp, er_imp, er_exp, pa_imp, pa_exp, pr_imp, pr_exp, ra_imp, ra_exp, rr_imp, rr_exp.
   • 3m — interval. Options include 1m, 3m, 15m, 20m, 30m, 1h, 1d, and others supported by the device.

   For energy (en) measurements, channels are encoded by number: en-dev-30-15-3m requests 3-minute data for channel ID 15 on device 30.

   In query formulas, device IDs can be replaced with device codes. Device codes can contain Latin letters, e.g. P22S1Y8. The formula y-m-d1:*:el-dev-201-ea_imp-30m:sum can be written as y-m-d1:*:E123457ea_imp:sum.

4. sum — aggregation function. Available functions: sum, min, max, mean. An optional coefficient can follow: sum-k0.1 multiplies the result by 0.1.

For flexible period reports, use #begin-#end instead of a specific date (y-m-d). For example, #begin-#end:h10-m00:el-dev-30-ea_imp-3m:sum sums the active energy from device 30 from 10:00 at the start to 10:00 at the end of the user-defined period.

Period reports also support time offsets by day: #end+2d or #begin-1d. Queries using only #begin or #end will fill the cell with the value for that point in time only.

The #date formula provides an extended query toolkit. The comparison between y-m-d and #date formats is shown in Table 2.3.

2.4. Query examples

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

Data for 2019, substituting month and day from the report date, time from 10:00 to 11:00, electrical 3-minute data, device ID 30, channel ea_imp, sum aggregation with coefficient 0.001 (result in megawatts).

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

Data for the 1st month of the year from the report date. Energy resources table, daily records, device 220, channel 1021. Since the day is omitted, the full monthly range is sampled (e.g. 2019-01-01 to 2019-02-01, excluding the first day of the second month).

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

Data for 00:00 on the 1st of the month following the report date. Electricity readings for device ID 1202. Arithmetic like m+2, m+3, y+5 is also supported.

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

Data for 10:00 on the last day of the previous month for device 1202, channel ra_imp, 30-minute database.

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

Data for the period from 00:00 of #begin to 00:00 of #end. Active received energy from device ID 46, summed from 3-minute values.

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

Half-hour value starting at 6:30 on the start date of the period. Active energy output from device ID 58.

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

Half-hour starting at 2:00, two days after the end of the user-specified period. Data for billing group ID 22, received active energy.

2.5. Output of device information. Device codes and tags

The following requests display device attributes directly in report cells:

• #dev.201.IP — displays the IP address of device ID 201. A device code can be used instead of the ID.
• #dev.201.Name — displays the device name.
• #dev.201.VendorNumber — displays the device serial number.

The serial number can also be retrieved from a tag if the device supports it. For example, for a SATEC device queried via Modbus, write y-m-d:*:ti11001 where ti11001 is the tag code. Any tag from the configuration can be added to flexible reports in the same way.

3. Managing Report Templates

Flexible reports are managed in the Flexible Reports window, accessible via Reports → Flexible Reports.

A template prepared according to the query language in section 2 is uploaded by clicking the Create Template button in the upper-right corner of the template list.

After clicking (see Figure 3.1), a dialog box opens. Enter the template name, type, object, and category, then select the Excel template 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 to complete.

The template type can be annual, monthly, daily, or for-the-period (annual, monthly, or daily). It affects the calendar type used when generating reports, availability of auto-generation (only annual, monthly, and daily reports are auto-generated), and the applicability of timestamps and query types described in section 2.

The Object field links the report to an information model object, differentiating access rights and filter templates in the table.

The Category field enables report generation based on the Balances module.

All templates can be downloaded to a PC using the Download button and uploaded using the Select a file button in the template settings window, which opens by clicking 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 opens the report in the web version of Excel spreadsheets (Figure 3.3).

Interactive mode enables working with the report directly in the web interface without downloading or uploading an Excel file. In this mode, you can view all calculation results, select the display start time, download the report as .xlsx, and edit the report using the button.

In editing mode (Figure 3.4), both Excel formulas and CENTO queries within the report can be viewed and edited as described in section 2.

Report sheets are shown in the lower-left corner of the editing mode interface for switching between them (new sheets can only be added via Excel when uploading a template). The lower-right corner contains buttons and a zoom slider. To edit a cell’s contents, double-click it.

After editing, save the report and switch back to view mode to verify the results. All changes are applied instantly.

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, a separate structure can be created for flexible reports, or an existing structure can be reused.

To link a report to an object, open the template editing form (see Figure 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 Settings → System → User Access Rights and use the checkmarks in the Flexible Reports row to select which roles will have access to the flexible report bound to the object (Figure 4.1).

5. Report Management

Reports are generated from uploaded templates using the Create report button . Report generation can be performed in two ways:

1. Manually — click the Create report button in the template row in the Template list. In the pop-up window (Figure 5.1), enter the report name, select In a day or Per interval, and click .

2. Automatically — configure auto-generation in the template settings . In the pop-up window (Figure 5.2), define the report generation schedule using a Cron expression or the UI-based Cron editor (Figure 5.3), then confirm with .

When Past period is set, the previous day/month/year is used when generating the report. When Current period is set, the current period is used.

Automatically generated reports can be saved to a network resource and/or sent by email. Select the corresponding items in the settings window (Figure 5.2).

To display reports generated from the selected templates on the right side of the screen (Report list), select the templates in the Template list using the appropriate checkboxes (Figure 5.4). Templates can be filtered by name and type.

A created report can be downloaded in Excel format or deleted by marking the unwanted report with a checkmark. The generated Excel file preserves all formulas, charts, and cell formatting specified in the template (Figure 5.5).

The report manager supports versioning. Clicking the Copy button copies the report, adding the suffix “– v1”, “– v2”, etc. to its name. The new version appears in a nested list under the root report (Figure 5.6) and can be renamed, copied, downloaded, uploaded, or deleted independently.

Report versions can also be created by uploading a previously downloaded and edited Excel file: click the Download button on the report version, select the file in the pop-up window, and click .

Reports created by a user can be hidden from or published to other users using the Hide/Publish button . Hidden reports are visible only to the user who generated them.