Configure Calculated Data Function dialog

Use this dialog to configure a worksheet function to retrieve calculated data relating to a single tag over a time period which is broken down into a number of equal duration subintervals.

The data calculated for each subinterval can be:

For more information about the Calculated Data 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 cell reference (such as A1 or $A$1) that contains the tag in the current workbook. Another method is to use a defined name (such as MyTag) that is already configured in the workbook. For more information about cell references and defined names, refer to the Microsoft Excel online help.

Period  Defines the overall time period and the subintervals for the analysis. You specify the overall time period using the From and To group boxes.  The results obtained by the worksheet function will be in the time order used here, so if you want the latest data to appear first in your worksheet, specify the latest time in the From group box and the earliest time in the To group box.

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

Mode  Select the mode for the start and end timestamps: 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.

From   Defines the start of the time period.

Date/Time  The timestamp of the start of the period you wish to examine. This allows selection of date/times 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.)

To   Defines the end of the time period.

Date/Time  The timestamp of the end of the period you wish to examine. This allows selection of date/times 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.)

Intervals  Defines the intervals for the samples to be retrieved.

Interval  The duration of each interval. Enter a positive number followed by a unit string, selected from the dropdown list. The dialog warns if the overall time period is not an exact multiple of this interval. This would lead to a final interval that is smaller than the others. If this situation is allowed to stand, the worksheet function will extend the overall period by adjusting the To date. 

Number of Intervals  The number of intervals for which you want data. By configuring this value, the dialog automatically calculates the interval duration based on the currently specified start and end times. If the number is capped, the background color of the control turns from gray to yellow to indicate that capping has occurred.

For more information about the maximum boundaries of a worksheet, refer to Use the Worksheet Functions.

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. 

Extra row(s)  This can be used to expand the selection beyond the current number of rows even without using the Try It feature. Select the check box and specify the number of rows to be added. This feature might be useful where there is a possibility that the time period could cross a daylight savings boundary. For example, a shift report gathering data every 30 minutes for what is normally an 8-hour shift, could yield 9 hours worth of data if the clocks change during the shift. By inserting two extra rows, there would be space for the additional data.

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.