Reporting Functions
General Information
The Reporting Functions are a collection of functions available as MS Excel formulas. They can be used in any MS Excel sheet even when the sheet is not formatted as a TSM template.
By using these functions, it is possible to create custom reports and data sheets, using live time series data directly from the database.
The following functions are available:
| Function Name | Description |
|---|---|
| ReadTimeSeriesValues | Reads time series values using the basic parameters. |
| ReadColumnValues | Reads time series data via specific plug-ins and an extended list of parameters. |
| ReadSetting | Reads the value of a single TSM setting from the HAKOM.Config configuration file. |
The Reporting Functions are available as Excel formulas and can be inserted via the Excel "Insert Function" feature:
Example
In this example the time series data is loaded into a prepared table and the diagrams which are based on these tables are created automatically.
TSM_ReadTimeSeriesValues
The function ReadTimeSeriesValues provides the basic functionality to load time series data.
The following parameters are available:
| Parameter | Description | Data Type |
|---|---|---|
| DataSource | Name of the database connection used to load the time series data. | String |
| Name | Name of the time series containing the data. | String |
| FromDate | Start of load period. | Date stamp ("MM/DD/YYYY") |
| ToDate | End of load period. | Date stamp ("MM/DD/YYYY") |
| Interval | Interval in which the time series data will be loaded. | Numeric
|
| Unit | Unit in which the time series data will be loaded. | String (using "DB" selects the unit stored in the database) |
| ShowMissingValues | Controls if missing time series data is shown. | Boolean (TRUE/FALSE) |
| Horizontal | Controls if the time series data array will be inserted horizontally or vertically. | Boolean (TRUE/FALSE) |
The loaded data is inserted into the Excel template as a data array (both vertical and horizontal arrays are supported, please refer to the parameter "Horizontal"). The length of the array is determined by the load period and the configured interval. The data for each interval step will be inserted in a separate cell.
TSM_ReadColumnValues
This function is an extension of the function TSM_ReadTimeSeriesValues and supports the loading of time series data using specific plug-ins.
| Parameter | Description | Data Type |
|---|---|---|
| DataSource | Name of the database connection used to load the time series data. | String |
| Application | Name of the TSM plug-in that should be used to load the time series data. | String |
| P1-P5 | Plug-in specific parameters which are contained in the cells C10 - C14 in TSM templates (P1 = C10 ... P5 = C14). More information regarding the the parameters contained in these cells and their function can be found here: Reading Data into Excel, Reading Time Series | |
FromDate | Start of load period. | Date stamp ("MM/DD/YYYY") |
| ToDate | End of load period. | Date stamp ("MM/DD/YYYY") |
| Interval | Interval in which the time series data will be loaded, | Numeric
|
| Unit | Unit in which the time series data will be loaded. | String (using "DB" selects the unit stored in the database) |
| ShowMissingValues | Controls if missing time series data is shown. | Boolean (TRUE/FALSE) |
| Horizontal | Controls if the time series data array will be inserted horizontally or vertically. | Boolean (TRUE/FALSE) |
Example
Using this function allows the loading of archive or quotation data from time series. In order to load these values, the archive and/or quotation datetime stamp must be provided in the dedicated parameter field, including the information if the data should be loaded as "exact" or "not exact". More information about working with archive and quotation data can be found here: Audit, Quotations
TSM_ReadSetting
This function allows to read TSM settings from the HAKOM.Config file currently in use and display its value in MS Excel.
<HAKOMConfiguration>
<products>
<TSM>
<settings>
<!-- TSM specific settings -->
</settings>
</TSM>
</products>
</HAKOMConfiguration>
The following parameter is available for this function:
| Parameter | Description | Data Type |
|---|---|---|
| Setting | Name of the setting, the value of which will be displayed in MS Excel. | String (the name of the setting must be entered without the angle brackets) Example: "LanguageKey" |