The Functions Tab

In the Functions tab, you can create functions in the Excel sheet to Read/Write to items in the connected Core namespace. You can execute real-time and history reads on items and the value will be retrieved and returned in the selected worksheet cells. Expand the ReadValue part of the tab to see the options:

Functions - ReadValue
Figure 1. Functions - ReadValue

ReadValue

The items to read can be configured in the following ways:

  • Manual - Enter the path of the item to be read manually

  • Selected - All the items added to the Selected tags field (see previous section) will be read

  • Range - Paths of items to be read can be selected from a range of cells in the worksheet

It can also be selected to return the quality and timestamp from the items by checking the appropriate check boxes. For this example we will use the "Selected tags" added in the System Explorer tab and check the quality and timestamp boxes. Click Create to retrieve the values and return the selections in the worksheet:

ReadValue Example
Figure 2. ReadValue Example

To use the Range option, select Range in the panel then click "…". The paths from the previous example can be selected in the worksheet.

ReadValue - Range option
Figure 3. ReadValue - Range option

Click OK, then select an empty cell in the worksheet and click Create.

ReadValue - Range option Values
Figure 4. ReadValue - Range option Values

To refresh the values in the cells you can click on Force Calculate button in the "INMATION" tab of the Excel sheet.

Force Calculate button
Figure 5. Force Calculate button

ReadHistoricalData

The ReadHistoricalData function works in a similar manner to ReadValue with the same method for selecting paths. If the Selected paths is chosen, the items selected in the Explorer tab are used.

ReadHistoricalData function
Figure 6. ReadHistoricalData function

The period of time for which the historical data will be retrieved is defined by the Start and End properties. The number of data points which will be retrieved for this period is either defined explicitly by the value set for the Intervals property or implicitly by the Length property.

If the Length property is provided, the period defined by the Start and End properties is divided into equal intervals of the duration configured in the Length property. For this, the period defined by the Start and End properties needs to be evenly dividable by the value set for the Length property. Otherwise no data will be retrieved.

The Intervals property will only be taken into account if the Length property is empty or set to 0 or 0d 0h 0min 0s. In this case the period is equally divided into the given number of intervals.

The Length property can be set in 'd h min s' format, e.g. (1d 2h 3min 4s). If no unit is provided, the given number will be treated as seconds.

Clicking Create will place the historical data in the worksheet with the object paths for the selected items heading the columns of retrieved data.

Retrieved Historical Data in Worksheet
Figure 7. Retrieved Historical Data in Worksheet - 5 intervals of 5 hours in a 25 hour period; Intervals being ignored
If RAW data (Aggregate: AGG_TYPE_RAW) is retrieved with history calls, the number specified in the Intervals property defines the maximum number of data points to be read from the time specified in the Start property on. The retrieved data points are either limited by said maximum number or the time specified in the End property - whichever is reached first. This is different to the way Raw history calls are handled in the rest of the system and is specific to the inExcel Template. To retrieve the maximum number of raw historical data for the selected time period, the Length property should be set to 1s.

ReadHistoricalDataAtTime

The ReadHistoricalDataAtTime function works in a similar manner to ReadValue with the same method for selecting paths. If the Selected paths is chosen the items selected in the Explorer tab are used.

If raw historical data is to be read, please use the ReadRawHistoricalDataAtTime function.

ReadHistoricalDataAtTime function
Figure 8. ReadHistoricalDataAtTime function

The timestamp for the data can be selected.

Clicking Create will place the historical data in the worksheet with the object paths for the selected items heading the columns of retrieved data.

Retrieved Historical Data (at time) in Worksheet
Figure 9. [Retrieved Historical Data (at time) in Worksheet
The internal call to the Historian, which is used to retrieve the value at the specified time is using the Interpolated Aggregate. StartTime is the provided timestamp, EndTime is StartTime + 1 millisecond.

ReadRawHistoricalDataAtTime

This function provides the same functionality as ReadHistoricalDataAtTime except that it reads raw historical value at a specific time. In case no value exists at the provided timestamp, the previous value is returned (if it exists).

ReadRawHistoricalDataAtTime function
Figure 10. ReadRawHistoricalDataAtTime function

ReadAdvancedEndpoint

The ReadAdvancedEndpoint function can be used to utilize WebAPI Advanced Endpoints in Excel Template. Use-case specific Lua code can be developed, which returns data to Excel Template. The Lua code is processed by the Core - this allows efficient server side processing of the data.

The ReadAdvancedEndpoint function works in a manner similar to ReadValue with the same method for selecting paths. If Selected is chosen, the items selected in the System Explorer tab are used.

ReadAdvancedEndpoint function
Figure 11. ReadAdvancedEndpoint function

The Endpoint field specifies the address of a WebAPI Advanced Endpoint.

The address of the endpoint can either be entered manually (like in the Read functions) or selected from a list of preconfigured endpoints. Follow this link to learn How to prepare the Endpoint List.

Selecting an Endpoint
Figure 12. Selecting an Endpoint

In the later case, additional parameters needed for this endpoint can be entered in the Parameters text box or edited using a dynamically created form. To use this form, simply click on the …​ button next to Parameters value field.

Additional Parameters
Figure 13. Additional Parameters
  • The Start and End fields can be used to define the earliest and latest data to retrieved from the system. These limits can be provided as valid timestamps or as a references to the field containing a valid ISO8601 UTC timestamp. Can be empty.

  • The fields Result Rows and Result Columns are used to specify the number of rows and columnes returned from the Advanced Endpoint.

  • If more parameters are needed for the query, these can be specified in the Parameters field. The contents of this field can either be empty, a valid JSON string or a reference to the field containing JSON string.

Clicking Create will place the data in the worksheet with the object paths for the selected items heading rows of retrieved data.

Retrieved Advanced Endpoint data in Worksheet
Figure 14. Retrieved Advanced Endpoint data in Worksheet

An Example of Advanced Endpoint Functions:

local lib = {}
local STR = require "esi-string"

-- this function provides time and paths using standard fields of "args" object
function lib:echo(args, req, hlp)
    local matrix = {}
    matrix.rows = {}
    local row1 = {}
    local row2 = {}
    local row3 = {}
    local row4 = {}

    if args.paths then
        row1[1] = args.paths[1]
        row1[2] = args.paths[2]
    end

    row2[1] = args.tstart
    row2[2] = args.tend

    row3[1] = args.numberOfRows
    row3[2] = args.numberOfColumns

    row4[1] = args.parameters.a
    row4[2] = args.parameters.b

    matrix.rows[1] = row1
    matrix.rows[2] = row2
    matrix.rows[3] = row3
    matrix.rows[4] = row4
    return matrix
end


-- this function provides time and path using custom "parameters" object
function lib:Sum(args, req, hlp)
    local path = args.parameters.path
    local startT = args.parameters.startT
    local endT = args.parameters.endT

    local time_start = syslib.gettime(startT)
    local time_end = syslib.gettime(endT)

    local sum = 10
    local rs, more = syslib.getrawhistory(path, false, time_start, time_end)

    for T, v, q in rs() do -- note the order: timestamp, value and quality
        if q == 0 and type(v)=="number" then
            sum = sum + v
        end
    end

    local result = {}
    result.rows = {}
    result.rows[1] = {}
    result.rows[1][1] = sum

    return result
end

return lib

The Advanced Endpoint function should return an Lua table which contains only one field: rows. The contents of this field should be two-dimensional array. The dimensions should be equal to Result Rows and Result Columns.
This is an example of the table, converted to a JSON string:

{
    "rows":[
        ["System/Core/Speed", "System/Core/Temp"],
        ["2021-03-05T15:24:59.000Z", "2021-03-05T16:24:59.000Z"],
        [120,110],
        [1,2]
    ]
}

An example for the contents of the args parameter:

{
    "paths": ["/System/Core/Speed", "/System/Core/Temp"],
    "tstart": "2021-03-05T15:24:59.000Z",
    "tend": "2021-03-05T16:24:59.000Z",
    "numberOfRows": 4,
    "numberOfColumns": 2,
    "parameters": {"a":1, "b":2}
}

WriteValue

The WriteValue functions work in a similar manner to ReadValue with the same method for selecting paths.

WriteValue function
Figure 15. WriteValue function

To write a value to the selected object path(s), enter a value in the field and click Create.

WriteValue Results
Figure 16. WriteValue Results

The worksheet displays the paths of the selected objects and the result of the write operation.

Users can also specify the data type for the WriteValue operation by selecting from the dropdown menu. The written value will then be saved as the selected data type.

WriteValue choose data type
Figure 17. WriteValue choose data type

By default, the timestamp of the execution of the write process will be stored in the object, to which the value is written. The Timestamp input field allows to provide a custom timestamp instead of the default behavior.

Overwriting the Timestamp
Figure 18. Overwriting the Timestamp

If a value is written to multiple objects, individual timestamps may be provided in form of a range of cells. Click on the …​ icon, right of the input field to enter or select this range. The selected list of timestamps is applied top to bottom to the objects at the corresponding positions in the 'Selected tags' list in the System Explorer tab.

Providing Multiple Timestamps
Figure 19. Providing Multiple Timestamps

If you want to write an array value then the Array checkbox should be selected and the array value entered using the vertical bar character as a delimiter (for example 1|6|99 or a|b|c|z).

WriteValue Array
Figure 20. WriteValue Array