Use the Worksheet Function dialogs

Each worksheet function provided by DeltaV Reporter has a dialog that aids setting up a call to the function. The worksheet functions are:

For more information about the worksheet functions, refer to Worksheet Function Reference in Books Online.

Important  During the installation of DeltaV Reporter, important sample Excel workbook files are copied to the C:\DeltaV Reporter\Definitions folder. You can use these as examples of using the DeltaV Reporter worksheet functions and as templates to create report definition files for automatically generating scheduled reports from the Event Chronicle database. For important system configuration settings required to automatically generate scheduled reports, refer to DeltaV Reporter in Books Online.

You can use a worksheet function anywhere within the maximum boundaries of an Excel worksheet. The maximum worksheet size In Excel is 1,048,576 rows by 16,384 columns; however, Excel does not support array functions generated by an add-in, such as DeltaV Reporter, that exceed 65,536 rows.

Note  If the worksheet function you configure returns a large enough array, it may exceed Excel limitations. Also, keep in mind that if your worksheet function is embedded in a call to the standard Excel Transpose function, it will be subject to any number-of-columns limits in Excel rather than the larger number-of-rows limits. For more information about using the Transpose function with the worksheet functions, refer to Use the Excel Transpose Function to switch array columns and rows.

Example

The following example illustrates the use of the Configure Calculated Data function to examine the use of min, max, and end values for a single tag and time span.

This scenario examines the min, max, and mean statistics relating to a single tag (FIC-101.PV) over a 24-hour period and breaks the day down into 24 one-hour intervals, for the purpose of reviewing trends.  The table produced will have the following column headers:

Minimum Value – Minimum Timestamp [Local] – Maximum Value – Maximum Timestamp [Local] – Average Value

To make the worksheet useful for future queries, the tag and time period will be specified in the worksheet. This will allow you to change the tag and/or date by simply changing the contents of cells A1 and B1 in order to see data for a different tag on a different day.

To create the Excel worksheet

  1. Open Microsoft Excel.
  2. In cell A1, enter the tag name FIC-101.PV.
  3. In cell B1, enter the start date and time in the format mm/dd/yyyy hh:mm:ss; for example, 10/25/2004 00:00:00.
  4. In cell C1, enter the end time using a formula, for example "=B1 + 1". This sets the end time for the period to one day (24 hours) from the start time.
  5. Select an appropriately dimensioned range of cells.

Note  A range of 25 rows by 7 columns will be needed (one row is for the column headers). Rather than select the entire range, you can select the top left cell of the range—A3, for example—and then use options in the worksheet function dialog to automatically extend the range to the appropriate size.

  1. From the menu bar select DeltaV | Continuous Historian | Configure Calculated Data Function. The Configure Calculated Data Function dialog appears.

To use the Configure Calculated Data Function dialog

The Connection field is pre-populated with the default Continuous Historian connection details. A browse button is available if it is necessary to browse to an alternate source.

  1. For Tag, click the cell reference button and, in the Cell Reference dialog, enter $A$1 to indicate that the tag name is stored in cell A1. Click OK to close the Cell Reference dialog. (Note that entering $A$1 directly into the Tag text box would not work, since $A$1 would be taken to be the tag, not a cell reference.)
  2. For Period, leave the Mode as Local Time.
  3. For the Period start time, select Cell Reference and enter $B$1 to indicate that the start time is stored in cell B1. (Note that there is no need to use the cell reference button here, since this Cell Reference text box is always interpreted as a cell reference and never as a timestamp.)
  4. For the Period end time, select Cell Reference and enter $C$1 to indicate that the end time is stored in cell C1.
  5. For Intervals, select Interval, enter 60 and select minute(s) from the drop-down list.
  6. Under Display Data, select Header Row to show the column names for the returned data.
  7. Select the Columns by holding the Control key and selecting Minimum Value, Minimum Timestamp, Maximum Value, Maximum Timestamp and Average Value.
  8. Click the right arrow button to add the selected columns to the Selected Columns list.

Note  As changes are made to the optional fields, the Worksheet Required Range is updated to show the dimensions of the array that will be yielded.

  1. Under Worksheet, select "Adjust selection to accommodate results (if necessary)." 

Note  Since there is no data below or to the right of the selected cell, there is no need to check the "Insert rows and columns" check box. Also, since the selected dates do not run across the daylight savings change, there is no need to check the box for Extra rows. If daylight savings were involved, there would be an extra hour (one 60-minute interval), requiring one extra row of data.

  1. Click the Try It button to preview the results.

Note  This button opens a grid containing the actual data as queried from 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 dialog can be closed before the query is complete. While the query is being performed, the title bar indicates that it is working. On completion, the title bar indicates the number of rows and columns and the time taken to perform the query.

  1. Close the Try It window and then press OK on the Configure Calculated Data Function dialog. The formula is inserted into the worksheet.