Configure Single Value Function dialog

Use this dialog to configure a worksheet function to retrieve a single sample for a tag at a specified time (previous sample, next sample, or interpolated).  The data returned can be:

For more information about the Configure Single Value Function dialog, refer to Worksheet Function Reference in Books Online.

The dialog contains the following areas:

Tag Specification  Specifies the Continuous Historian server to use and the tag to be analyzed.

Connection  Enter or browse to the Continuous Historian connection string for accessing the database. This is the node name of the Continuous Historian server. 

Tag  Enter or browse to the item for which you want to retrieve historical data, for example, FIC101.PV. Alternatively, use the cell reference selector button to open the Cell Reference dialog. On that dialog you can click on a cell in any Excel workbook that contains the tag. You can also type the name of a cell (such as A1 or $A$1) that contains the tag in the current workbook. Another method is to use a defined name that is already configured in the workbook. (Refer to Microsoft Excel help on defined names for more information.)

       

Timestamp  Indicates the timestamp for which you want data returned.

The Mode and Offset controls tell the Continuous Historian server how to interpret the time you specify.

Mode  Select the mode for the timestamp: Local Time or GMT. If GMT, you can specify an offset in hours and minutes. If local, the timestamp is treated as being in the time zone of the client PC.

Offset  If GMT is selected as the mode, you can select a time offset in +/-hh:mm.

Date/Time  The timestamp you wish to examine. This allows selection of a date/time with precision of 1 second.

Cell Reference  If the timestamp is a cell reference, the contents of the referenced cell must be a timestamp. (This allows selection of times in Excel standard format with millisecond precision, although it should be noted that the Continuous Historian records data on quarter-second boundaries: .000, .250, .500, .750.)

Return Data (Choice of 3 radio buttons) Indicates how you want to select data in relation to the time specified.

Previous sample  If selected, the sample immediately previous to the requested timestamp (or the sample exactly on the timestamp) is returned.

Next sample  If selected, the sample immediately after (or exactly on) the requested timestamp is returned.

Interpolated value  If selected, the value at the supplied timestamp is interpolated.

Display Data  Allows you to specify the requested data to be shown in the worksheet function results.

Header Row (check box)  Indicates whether you want the returned data to include a header row that contains standard headers for each column. The text will be specific to the data in the column and any attributes you choose. If you require different column headings, clear this check box and enter the headings you require directly into the worksheet.

Columns  Defines the columns that will be included in the tabular results of the worksheet function. Each column definition is made up of the column name and, optionally, various attributes relating to that specific column.

Available columns  To add a column, select the column name and click the right arrow button. The standard Windows Shift and Control keys can be used to select multiple columns. Note that you can add a column to the results more than once; you might do this to see the column with different attributes, for example, a timestamp in GMT and the same timestamp in Local time.

Selected columns  To remove a Selected column, click the column name and click the left arrow button. The Up and Down arrow buttons can be used to rearrange the order of the columns.

Attributes  For the selected column, click Attributes to view the parameter attributes in a separate dialog box. Select the attribute by clicking a radio button or choosing the attribute from a drop-down list. The Attributes button is disabled if multiple columns are selected in the Selected Columns list, or if the selected column does not support attributes.

Worksheet  Use this section of the dialog to set up what will be an array formula in the worksheet. You can select a rectangular region of the correct size before invoking the dialog. Or you can use the "Adjust selection to accommodate results (if necessary)" check box together with the "Insert rows and columns" check box. For more information about array formulas, refer to the Microsoft Excel online help.

Required Range  Displays the dimensions of the array that will be yielded based on the current selections in the dialog fields.

Current Range  This field displays the current selected range and the dimensions of that range. If this range is smaller than the Required Range, then data will be truncated in the workbook, unless "Adjust selection to accommodate results" is used (see below).

Adjust selection to accommodate results (if necessary)  If the Required Range is larger or smaller than the Current Range, use this check box (and the "Insert rows and columns" check box if necessary) to cause the dialog to adjust the current range when the OK button is clicked.

Insert rows and columns  Use this check box (in combination with the "Adjust selection to accommodate results" check box) to set up a rectangular region of cells that will contain the results. Use this check box if the worksheet contains additional data below or to the right of the selected cell; otherwise, that data may be overwritten. 

Try It Button  Opens a grid view in which the results can be previewed. This dialog will show the real results from querying the Continuous Historian database. The Try It window shows the row and column at which the data will be inserted. (Thus, if the formula is inserted at B7, the Try It grid starts at B7.)  The Try It window can be closed at any time to abort a query that is taking too long to complete.

OK Button  Accepts your changes and closes the dialog. A tooltip for the OK button shows the formula that will be inserted when the OK button is clicked. Note that until all fields in the dialog are completed with valid values, the OK button is disabled. If a cell reference is used, the OK button is disabled if the referenced cell does not contain a valid value (for example, the referenced cell is blank or contains a tag name as the value for a Date/Time field). If the OK button is disabled, a warning icon appears on the OK button; a tooltip for the warning icon provides further information.

Cancel Button  Closes this dialog without accepting the changes.