# Spreadsheet ### Basic Concept ### Configuration The configuration defines which BuildingPro Suites instance to connect to and which reports to create with which template. #### BuildingPro Suites Instance 
{"eliona_handler": {
    "host": "experimental.eliona.io",
    "api": "[https://experimental.eliona.io/api/v2](https://experimental.eliona.io/api/v2)",
    "tenant_id": 1,
    "api_key": "slidfglsadbföabg$$äasfnasilbg"}
}
| Configuration | Description | Example | Note | | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------- | ------------------------------------- | | `host` | Address of the BuildingPro Suites instance without HTTPS. | develop.eliona.cloud | Optional. Ideally as a .env variable. | | `api` | Address of the API used. In this case, it is always `/api/v2`. Here with HTTPS. | | Optional. Ideally as a .env variable. | | `tenant_id` | Tenant number on the BuildingPro Suites instance used. (You get this when you edit the tenant in the settings and look at the address bar.) | | Necessary | | `api_key` | The API key for connecting to the BuildingPro Suites instance. | ... | Optional. Ideally as a .env variable. | | `dbTimeZone` | The database's time zone. | Europe/Zurich | Optional. Ideally as a .env variable. | ### Reports The report configuration is located within a list. This can therefore contain one or more reports. Each report requires at least these configurations. ```json "reportConfig": [ { "name": "Monthly Report Level 1", "schedule": "monthly", "type": "DataEntry", "templateFile": "./storage/reports/templates/Template_Report_Level_1.xlsx", "sheet": "Sheet1", "separator":";", "firstRow": "0", "fromTemplate": true, "reportPath": "Report_Level1.xlsx", "tempPath": "" }, { "name": "Monthly Report Level 2", "schedule": "monthly", "type": "DataEntry", "templateFile": "./storage/reports/templates/Template_Report_Level_2.xlsx", "sheet": "Sheet1", "separator":";", "firstRow": "0", "fromTemplate": true, "reportPath": "Report_Level2.xlsx", "tempPath": "" } ] ``` | Configuration | Description | Example | | -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- | | `name` | Name of the report definition. | "Report Level 1 Monthly" | | `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 parallel) = "DataEntry" (Individual entries in the cell) | | `templateFile` | Path to the template file. | ./templates/syn\_001.xlsx | | `sheet` | Table name when using Excel spreadsheets. | Table1, Sheet1 | | `fileType` | Sets the file format to use 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 with `fromTemplate` active. | | | `fromTemplate` | Indicates whether the file should be newly created or taken from the template. A .csv file should not be used to append data. With an XLSX file, the formatting can be retained with this option. (Formulas, cell formatting, etc. are kept) | true / false. Please pay attention to the spelling. | | `reportPath` | Storage path including the name of the report. | ./report\_XY\_001.xlsx | ### Templates The templates should be stored in Excel format and must contain the configuration of the assets. The link to the assets/attributes in BuildingPro Suites 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. | Key | Example Value | Note | | ------------- | --------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `"assetGai"` | | GAI of the asset used. | | `"attribute"` | | Here, the attributes of the asset type to be read are entered. | | `"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 MONTH raster must be used. | | `"mode"` | last first sum average | The mode to be read from the raster. For attributes that have an average value, "average" must be used. Attributes with a sum or cumulative summation must use "sum". | In Google Sheets exportieren ### Calculations Calculations are performed in Excel. All Excel functions can be used here. Referencing values from the same table and using values from BuildingPro Suites is possible. When using values from BuildingPro Suites, simply use the JSON part for referencing. *Example: Here, a cell content is subtracted from an BuildingPro Suites asset attribute.* `= ({"assetGai":"K01 Grid Energy Meter 1005", "attribute":"Energy", "raster":"MONTH", "mode":"sum"} - J6)` ATTENTION: Entering such a formula is prevented by Excel. To do this, the format of the cell must be adjusted. After entering the formula, the cell type can be reverted. It is recommended to choose the number format. ### DataListSequential (List one below the other) Has not been used so far. Was only intended for a tenant in the offer phase. ### DataListParallel (List parallel) *Example Table:* *Example Template:* | *Timestamp* | *\[m³] Volume IWB Service Water Cooling System 1004* | *\[°C] Cooling System 1004 Water Temperature Return WAR* | *\[] IWB Service 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:* | From Date | To Date | Consumption | | ----------------------------- | --------------------------- | ------------------------------------------------------------------------------------------------------------------------------ | | {"timeStampStart":"%Y-%m-%d"} | {"timeStampEnd":"%Y-%m-%d"} | {"assetGai":"Drinking Water Cold WRO 1007P UG-8.OG Fresh Water Station", "attribute":"Volume", "raster":"MONTH", "mode":"sum"} | | {"timeStampStart":"%Y-%m-%d"} | {"timeStampEnd":"%Y-%m-%d"} | {"assetGai":"Softened Water Gastro Supply Fresh Water", "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 the App.py can be called with arguments. There is a help menu that can be accessed with -h. ```bash (env311) spreadsheet-report-app % python ./src/spreadsheet-reportapp/spreadsheet_report_app.py -h usage: spreadsheet_report_app.py [-h] [-d DATE] [-c CONFIG] [-u USER] [-r REPORT] [-o OUTPUT] options: -h, --help show this help message and exit -d DATE, --date DATE Date in the format: dd.mm.yyyy -c CONFIG, --config CONFIG Path to the used configuration file. For Example: "./config/config.json" -u USER, --user USER User name that's requested. Name can be read from config.json file. -r REPORT, --report REPORT Report name that's requested. Name can be read from config.json file. -o OUTPUT, --output OUTPUT Export file path. If empty will be stored under "./temp/file_name" (env311) spreadsheet-report-app % ``` | Argument | Description | Example | | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------- | | -h, --help | Displays the help text. | python sp...app.py -h | | -d, --date | Input of the desired report date in "dd.mm.yyyy" format. For monthly reports, the 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 reports and user settings. If this is left blank, the standard directory will be used. | python sp...app.py -c "./config/config.json" | | -u, --user | Either select a user or a report configuration. Depends on the existing configuration which are available. In the case of a user, all of the user's reports are created. In the case of a report, the individual report is created. One must be set, however. | python sp...app.py -u | | -r, --report | | python sp...app.py -r | | -o, --output | Output path for the created reports. If this parameter is left blank, the "./tmp\_reports/manual\_created/" directory will be used. | python sp...app.py -h "./tmp\_files/export/" |