RTD Server

Data for live values is provided through an in-process RTD server module for Microsoft Excel that accepts a variety of formulas to address data. The “ProgID” identifier of the RTD server is “Rensen.Dataristix2.RTD”, which needs to be included in each formula.

Configuration

The connector’s Real-Time-Data Server sends live values to Microsoft Excel and provides various options as shown in the following image.

../_images/excel-config-rtdserver.png

Pause dataflow

Real-time data flow for the local user may be paused to retain the last values within spreadsheets referencing the RTD server. To pause the data flow, tick the “Pause live value updates” box; untick the box to resume updates. Pausing live data may be helpful while designing spreadsheets.

Configure data format

The following data format options are available for copied live values:

  • Header
    Include Header information for the options below
  • Connector Name
    Include the name of the connector that sends the live values
  • Topic Name
    Include the topic name that contains the live values
  • Tag Name
    Include the tag name of the live value
  • Tag Timestsamp (local time)
    Include a timestamp in local time for the live value
  • Tag Timestamp (UTC)
    Include a timestamp in UTC for the live value
  • Tag Status
    Include a numerical value that describes the quality of the live value. This is aligned with status codes defined for OPC UA. A zero value indicates a good quality.
  • Tag Status Text
    Include a human readable translation of the tag status code (for example, “Good” for values of good quality).

Remote access

Check the box to enable remote access to the Excel RTD server. This means that other users can access live data acquired on this computer for use in their Excel spreadsheets elsewhere on the Intranet by including the hostname into Excel’s RTD formula.

Warning

Configure your firewall to allow authorized access only.

Users elsewhere must install the free Excel RTD Server (Client) module or may use the Dataristix Application with Excel Connector to connect remotely. Please visit the Dataristix Download Index to download required modules.

Copy Live Data

Right-click on a connector topic and select “Copy Live Values (Excel RTD Server)” to copy live data. Any connector that can provide data may be used as a data source, for example the “OPC UA” or the “ODBC” connector. In the example below, live values of the OPC UA connector’s “Boiler1” topic are copied.

../_images/excel-copylivevalues.png

“Copy Live Values…” will place RTD server formulas for all tags within the topic onto the clipboard, ready for pasting into Microsoft Excel (if there are unwanted tags then you can remove them after pasting).

Goto to Microsoft Excel, click on a cell, and select “Paste” to paste the data links. In the example below the Excel Connector is configured to paste headers, tag names, tag values and local timestamps.

../_images/excel-spreadsheet.png

Timestamps may initially be shown in numeric form; right-click on a cell or column in Excel to change the cell format to Date or Time to display more readable date and time information.

Note

Live value update notifications are sent to Excel at the topic’s configured sampling rate. Actual spreadsheet updates may run at a lower rate, typically two seconds, depending on Excel’s RTD “ThrottleInterval”.

Live Value Formula

RTD formulas for live values are in the form:

=RTD("Rensen.Dataristix2.RTD",, "<Connector Name>", "<Topic Name>", "<Tag Name>")

The double comma after “Rensen.Dataristix2.RTD” indicates that the RTD server to talk to is on the local computer; this is always the case because the RTD server is implemented as an “in-process” module.

The connector name is the name of the connector sending the data, for example “OPC UA”.

The topic name is the topic that contains the live value tags, for example “Boiler1”.

The tag name identifies the live value.

Live Value Formula for Arrays

The RTD server supports linking to one-dimensional array values. Referenced array tags require additional information to address elements within the array. This additional information is not provided through the “Copy Live…” function but can be added manually. To address a specific array element use RTD server formula:

=RTD("Rensen.Dataristix2.RTD",, "<Connector Name>", "<Topic Name>", "<Tag Name>", "Value", <ValueIndex>)

Where <ValueIndex> is a place holder for the index of the array element. Example:

=RTD("Rensen.Dataristix2.RTD",, "OPC UA", "Boilers", "TempArray", "Value", 0)

This will access the first element (index zero) of the TempArray array tag. A quick way to generate formulas for many array elements is to fill RTD formula cells using a reference to a separate index cell containing index values, for example:

=RTD("Rensen.Dataristix2.RTD",, "OPC UA", "Boilers", "TempArray", "Value", A1)
=RTD("Rensen.Dataristix2.RTD",, "OPC UA", "Boilers", "TempArray", "Value", A2)
...
=RTD("Rensen.Dataristix2.RTD",, "OPC UA", "Boilers", "TempArray", "Value", A10)

with cells A1 to A10 filled with index values 0 to 9.

Extended Formula

The RTD formula may be extended to select specific properties of the live value:

  • Timestamp (local time)
=RTD("Rensen.Dataristix2.RTD",, "<Connector Name>", "<Topic Name>", "<Tag Name>", "Timestamp")
  • Timestamp (UTC)
=RTD("Rensen.Dataristix2.RTD",, "<Connector Name>", "<Topic Name>", "<Tag Name>", "TimestampUTC")
  • Status
=RTD("Rensen.Dataristix2.RTD",, "<Connector Name>", "<Topic Name>", "<Tag Name>", "Status")
  • Status Text
=RTD("Rensen.Dataristix2.RTD",, "<Connector Name>", "<Topic Name>", "<Tag Name>", "StatusText")

Remote RTD Formula

For remote RTD connections to a different host running Dataristix with Excel Connector, include the hostname or IP address of the Dataristix server within the formula like so:

=RTD("Rensen.Dataristix2.RTD",, "<Connector Name>@<hostname>", ... )

The connector name is followed by the ‘@’ character and hostname. For example:

=RTD("Rensen.Dataristix2.RTD",, "OPC UA@jupiter", "Boiler1", "Temperature")

Here the hostname is “jupiter” and the local RTD server will attempt to retrieve values from the Excel Connector installed on host “jupiter”.

If you are accessing Dataristix on a host other than “localhost” or the loopback IP address, then the “Copy Live…” context menu function will include the hostname in the formula for you.

Spreadsheet Design Considerations

Opening a spreadsheet containing references to the RTD server will result in “N/A” cell values on computers without RTD server installed. Consider the following approach to share spreadsheets with colleagues once the spreadsheet is updated with a live value snapshot.

1. Create a separate sheet within the Excel document to contain all references to the RTD server. This spreadsheet is used as a staging area for live values.

2. Create other sheets presenting the data by internally linking cells from the live value spreadsheet: Select live value cells that reference the RTD server in the staging sheet and “Paste Link” into other sheets to present the data.

3. When it is time to share the Excel document, go to the staging sheet, select all (CTRL-A), copy (CTRL-C), then select “Paste / Paste Values” from Excel’s Paste menu. This will replace all RTD server formulas in the staging sheet with current cell values and the linked cells in other sheets will retain those values.

4. Save the document under a different name and keep the original document containing RTD server references for future use; share the saved document with colleagues.

The Excel document can now be opened on other computers without resulting in “N/A” cells.