Using the HISTORIAN​.DETAIL Function

The HISTORIAN.DETAIL function retrieves detailed values for a specified list of tags.

Syntax

HISTORIAN.DETAIL(TagList, StartTime, EndTime, Retrieval, Format, Details, Down, Ascending, DiscreteFormat, UOMAddress)

• TagList - Required. The range of cells containing the list of tag names you want to retrieve information about.

• StartTime - Required. The date and time to start the data summary, expressed as a serial date number.

• EndTime - Required. The date and time to end the data summary, expressed as a serial date number.

• Retrieval - Required. Indicates the retrieval mode. Supported modes are full, delta.

• Format - Required. Indicates the output format. Supported modes are singleTag, allTags.

• Details - Required. A comma-separated list of properties to retrieve for the selected tags. The following properties are supported: DateTime, Resolution, OpcQuality, Unit, and Value.

• Down - Optional. If TRUE, the tags are listed in rows. If FALSE, the tags are listed in columns. If no value is specified, the default value of TRUE (rows) is used.

• Ascending - Optional. If TRUE, data is sorted in ascending order by time. Otherwise, the data is sorted in descending order by time. If no value is specified, the default value of TRUE (ascending) is used.

• DiscreteFormat - Optional. The format in which to display discrete values. Valid options are label, numeric, or boolean. If no value is specified, the default value of label is used.

• UOMAddress - Optional. A cell, or range of cells, containing the names of engineering units. When tag data is retrieved, it is converted to display in this unit if an appropriate conversion is defined in the Engineering Units Catalog. Otherwise, it displays in the tag's default engineering unit.

Example

Recreating the simple report from the previous example, we select the Function option instead of the Static option. This results in a formula being inserted into the spreadsheet, instead of static data. There is a brief pause while communicating with the server, then the retrieved data is displayed.

The formula generated using this example is:

=HISTORIAN.DETAIL(Sheet1!A2:A4, NOW() - 1/(24), NOW(), "full", "singleTag", "FQN,Value,DateTime,Resolution,OpcQuality,Unit", FALSE, TRUE, "label")

You can modify the formula, and the results refresh automatically to reflect your changes. You can also use the function directly, without using the add-in panel.

If your version of Excel does not support dynamic arrays, a message similar to the following displays:

In this case, although you can still use the custom function, the size of the output range cannot be refreshed automatically. You must manually size the range by selecting it, and then press Ctrl-Shift-Enter.