Spread Sheet
Basic concept
Configuration
The configuration defines which Eliona instance the connection should be made to and which reports are created with which template.
Eliona instance
host
Address of the Eliona instance without HTTPS.
develop.eliona.cloud
Optional. Ideally as
.env variable
api
Address of the API used. In this case it is always /api/v2. Here with HTTPS.
https://develop.eliona.cloud
/api/v2
Optional. Ideally as
.env variable
project_id
Project number on the used Eliona instance. (You get this when you edit the project in the settings and look at the address bar.)
Required
api_key
Key of the API for the connection to the Eliona instance
...
Optional. Ideally as
.env variable
dbTimeZone
Time zone of the database.
Europe/Zurich
Optional. Ideally as
.env variable
Reports
The configuration of the reports is located within a list. So it can contain one or more reports. Each report needs at least these configurations.
name
Name of the report definition
"Monthly Report Level 1"
schedule
Call frequency
= yearly (annual report is sent in January)
= monthly (monthly report is sent every month)
type
Report type definition. This determines which type is used for reporting.
= "DataListSequential" (list one below the other)
= "DataListParallel" (list in parallel)
= "DataEntry" (individual entries in the cell)
templateFile
Path specification for the template file
./templates/syn_001.xlsx
sheet
Worksheet name when using Excel tables
Table1, Sheet1
fileType
Set the file format to be used for saving and reading the data
csv, xls, xlsx
separator
Separator to be used when reading and saving CSV files.
";" // "," // " "
firstRow
First row to be used when reading the template configuration and saving the data when fromTemplate is active.
fromTemplate
Indicates whether the file should be created anew or taken from the template. A .csv file should not be used to append the data. With an XLSX file, the formatting can be retained with this option. (Formulas, cell formatting, etc. are preserved)
true / false
Please pay attention to the spelling
reportPath
Storage path including the report name
./report_XY_001.xlsx
Templates
The templates should be stored in Excel format and must contain the configuration of the assets. The linking to the assets / attributes in Eliona looks like this:
{"assetGai":"H01 district heating energy meter IWB", "attribute":"Energy", "raster":"MONTH", "mode":"sum"}
The required information is entered here in JSON format.
"assetGai"
GAI of the used asset.
"attribute"
The attributes of the asset type to be read are entered here
"raster"
M1 , M5, M10, M15 H1, H2, H4, H8, H12 DAY
WEEK MONTH YEAR
Depending on the report to be created, the raster must be entered here. For a monthly report, the raster MONTH must be used.
"mode"
last first sum
average
The mode that is to be read from the raster.
For attributes that have an average calculation, "average" must be used.
Attributes with a sum or cumulative summation must use "sum".
Calculations
Calculations are carried out in Excel. All Excel functions can be used here. Referencing values from your own table as well as using values from Eliona is possible. When using values from Eliona, simply use the JSON part for referencing.
Example: Here a cell content is subtracted from an Eliona asset attribute.
= ({"assetGai":"K01 network energy meter 1005", "attribute":"Energy", "raster":"MONTH", "mode":"sum"} - J6)
ATTENTION: Entering such a formula is prevented by Excel. To do this, the cell format must be adjusted. After entering the formula, the cell type can be set back. It is recommended to choose the number format.
DataListSequential (list one below the other)
Has not been used so far. It was only intended for one project in the quotation phase
DataListParallel (list in parallel)
Example table
Example template
Example template
Timestamp
[m³] Volume IWB domestic water cooling system 1004
[°C] Cooling system 1004 water temperature return WAR
[] IWB domestic water cooling system 1004 connection OK
{"timeStamp":"%Y-%m-
%d %H:%M:%S",
"raster":"H1"}
{"assetGai":"IWB cooling system 1004", "attribute":"Volume", "mode":"sum"}
{"assetGai":"IWB cooling system 1004", "attribute":"Temperature", "mode":"average"}
{"assetGai":"IWB cooling system 1004", "attribute":"Status", "mode":"last"}
DataEntry (individual entries in the cell)
Example table:
Example template:
{"timeStampStart":"%Y-%m-%d"}
{"timeStampEnd":"%Y-%m-%d"}
{"assetGai":"Cold drinking water WRO 1007P UG-8.OG
Fresh water station", "attribute":"Volume", "raster":"MONTH", "mode":"sum"}
{"timeStampStart":"%Y-%m-%d"}
{"timeStampEnd":"%Y-%m-%d"}
= {"assetGai":"H02 energy meter BWW 1005", "attribute":"Energy", "raster":"MONTH", "mode":"sum"} * 1000
{"timeStampStart":"%Y-%m-%d"}
{"timeStampEnd":"%Y-%m-%d"}
= (1 - ((J4) / ((J2+J3)*64 + 68000)))*100
Manual creation of a report
For manual creation of a report, the Docker container can be opened and App.py can be called with arguments. There is help that can be called with -h.
-h, --help
Help text output
python sp...app.py -h
-d, --date
Input of the desired report date in the format "dd.mm.yyyy". For monthly reports, month and year are important.
For annual reports, the year is important.
python sp...app.py -d "01.01.2021"
-c, --config
Path to the settings file. This is needed to load the reportings and user settings. If this remains empty, the standard directory will be used.
python sp...app.py -c "./config/config.json"
-u, --user
Either select user or report configuration. Which ones are available depends on the existing configuration. In the case of the user, all reports of the user are created. In the case of a report, the individual report is created.
One of them must be set.
python sp...app.py -u
-r, --report
python sp...app.py -r
-o, --output
Output path for the created reports. If this parameter remains empty, the directory "
./tmp_reports/manual_created/" is used.
python sp...app.py -h "./tmp_files/export/"
Last updated
Was this helpful?