Using the inExcel Template

To open the inExcel Template after installing system:inmation, go to the Windows Start menu and look for the inmation folder in the alphabetically listed applications. Click on the inExcel Template to open.

Launching the inExcel Template
Figure 1. Launching the inExcel Template

Click on the INMATION tab in the Excel workbook to view the available inExcel Template options.

inExcel Template - INMATION Tab
Figure 2. inExcel Template - INMATION Tab
The inExcel Template replaces the inExcel Add-in. To get information on how to upgrade from the inExcel Add-in to the inExcel Template for existing installations, please visit the inExcel Upgrade and Migration section.

The inExcel Template has two main functionalities available:

Action Pane

This graphical helper for the inExcel Template allows you to browse the different models in the system, select tags, perform read/write actions and configure connection settings to the Core.

VBA Functions

The inExcel Template will add inmation functions to the current worksheets and retrieve data into Excel cells.

The Force Calculate button is used to recalculate all functions on the worksheet.

Action Pane

With an Excel workbook open, select the "INMATION" tab and click on Show Actions Pane (this button toggles between show/hide). The Action Pane is displayed and contains four tabs:

  • System Explorer

  • Object Search

  • Functions

  • Settings

Actions Pane - Tabs
Figure 3. Actions Pane - Tabs

Settings

The Settings tab contains the Core connection settings and other information regarding time, language and OPC settings. The Settings tab also contains the Session Log that reports any errors that occur with the inExcel Template.

Web API Connection

The inExcel Template uses the Web API to communicate with the Core. To successfully connect to the Core the following fields need to be entered:

  • Web API address - The hostname or IP address of the Web API server (for example "http://localhost")

  • Port - The communication port for the Web API server (default is 8002)

The WebAPI supports both Windows authentication and Profile Credentials. Select which authentication method you wish to use from the dropdown menu.

If using Profile Credentials, you will also need to enter:

  • Profile - Profile name (must match a Profile object in the Access Model) to connect to Core

  • Password - Password for the profile used to connect to the Core

Settings tab - Actions Pane
Figure 4. Settings tab - Web API connection

Click Initialize to connect the inExcel Template to the Web API.

After installation, the connection settings will be automatically set to the the Core server settings configured during installation and the "so" profile and password defined at the time of setup. These connection settings can be changed to connect to other Core servers (for example, a remote server) at any time.

Connection Information

The Current connection section displays information about the existing connection and if the UDFs (User Defined Functions) are OK (UDFs are covered in the the description of the "Functions" tab.

Session Log and Write to log file options

The Session Log displays any encountered errors with the inExcel Template.

Session Log Errors
Figure 5. Session Log Errors

If errors occurs whilst using other tabs, the settings tab will display a red dot indicating that there is a problem.

Red dot indicating Session log errors
Figure 6. Red dot indicating Session log errors

A log file for all session log entries can also be created by checking the Write Log File checkbox.

The file will be created in the user directory in the following location:

"C:\Users\<current user>\AppData\Local\inmation\ExcelTemplate\Log"

System Explorer tab

When connected to the Core, the System Explorer will display the Model Panels available in DataStudio. You can select the model panel to display from the drop down menu (I/O, KPI, Server etc.). The tree in the system display can be expanded and navigated as you would in DataStudio.

I/O Model in System Explorer
Figure 7. I/O Model in System Explorer

Tags can be selected in the tree and then moved to the "Selected tags" field by drag and drop.

Drag and drop tags to Selected Tags
Figure 8. Drag and drop tags to Selected Tags

Double-clicking on the items in the tree will also add them to the Selected Tags section. All items added are then available to perform Read/Write functions on in the "Functions" tab (this will be covered in a following section).

The Object Search tab allows you to search for objects in the namespace in much the same way as the Object Search Panel in DataStudio. Enter a search term in the field to retrieve items with matching object names:

Object Search tab
Figure 9. Object Search tab

Items retrieved in the search can be directly added to the "Selected tags" by drag and drop. The search options can be changed to also search the object path name or to only search for dynamic objects. To do this select the drop down field to the right of the search field and check the appropriate check boxes.

Object Search Options
Figure 10. Object Search Options

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 11. 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 12. 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 13. ReadValue - Range option

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

ReadValue - Range option Values
Figure 14. 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 15. 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

The Start and End time for the data can be selected, as well as the aggregate and number of retrieved intervals for the history call.

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
If the RAW aggregate is used for history calls then the maximum number of values retrieved will be equal to the entered Intervals number. 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 full raw historical data for the selected time period, a high interval number (equal or greater than the estimated number of raw values) should be entered.

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.

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
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.

ReadAdvancedEndpoint

The ReadAdvancedEndpoint function can be used to utilize WebAPI Advanced Endpoints in Excel Template. Use-case specific Lua code can be developed, that 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 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.

ReadAdvancedEndpoint function
  • The Start and End time field can be empty or can be a valid timestamp or it can contain a reference to the field containing a valid ISO8601 UTC timestamp.

  • The fields Result Rows and Result Columns should contain the number of rows and columns returned from Advanced Endpoint.

  • The field Endpoint should contain the address of a WebAPI Advanced Endpoint.

  • If more parameters are needed for the query, then Parameters field can be used. The contents of this field can be empty, can be valid JSON string or can contain 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

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]
]
}

WriteValue

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

WriteValue function

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

WriteValue Results

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

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

WriteValue choose datatype

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

Functions

The Functions button in the "INMATION" tab of Excel allows you to call the Read and Write functions without using the Action Pane. Select a cell in the worksheet and click on Functions to show the drop-down menu.

Functions menu
Figure 16. Functions menu

Selecting ReadValue from the menu will open up a dialog where the arguments for the functions can be added. The Paths arguments can be added by selecting a range of cells from the worksheet or by entering a valid path manually. The ShowValue, ShowTimestamp and ShowQuality arguments are all boolean so TRUE or FALSE should be added.

Function Arguments
Figure 17. Function Arguments

Click OK to enter the formula and the retrieved values will be returned. The function can also be edited manually in the cell with a valid path name and arguments.

inExcel functions - Editing cell value
Figure 18. inExcel functions - Editing cell value
For the function to return a valid value, ALL arguments must be filled in the cell. For example the ReadValue function, all the ShowValue, ShowTimestamp and ShowQuality arguments must have a value for the function to work.

When retrieving timestamps using an inExcel function, the cell format must be changed to display the timestamp properly. For example, using the custom format shown below.

Timestamp cell format
Figure 19. Timestamp cell format
This format should also be used for the Start and End time arguments for the history read functions