Use this dialog to configure a worksheet function to retrieve all available samples for a single tag within a time period. For each of the samples in the time period the data returned can be:
When you use the Raw Data function, the Required Range for the array is not known until the data has been read, unless you enable the Maximum samples option. You can use the Try It button to determine the dimensions of the array needed for the returned results. For more information, see the description under Worksheet below.
For more information about the Configure Raw 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 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. For more information about cell references and defined names, refer to the Microsoft Excel online help.
Period Defines the overall time period, the maximum number of samples, and the boundary definitions for the analysis.
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.
Maximum samples To specify a maximum, check Maximum samples and enter the maximum number of samples you wish to retrieve. Any samples within the time period in excess of this number will not be retrieved. If unchecked, all available samples will be retrieved. (In this case, the final argument in the function formula will show as "-1.") If unchecked, it is not possible to calculate a required range value unless you first use the Try It feature. In this case, the Required Range will show 1 row for data and 1 row for the header (if enabled); the Extra row(s) feature can be used to expand the selection beyond these two rows even without using the Try It feature.
For more information about the maximum boundaries of a worksheet, refer to Use the Worksheet Functions.
From Defines the start of the time period.
Boundary Defines how the start boundary is handled. If Inside is selected, the first sample returned will be the first sample within the time period. If Outside is selected, the first sample returned will be the first sample outside of the time period. If Interpolate is selected, an interpolated sample is created for the period start.
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.)
Note If the start time of the specified time period is later than the end time of the specified time period, events are retrieved in reverse chronological order from the (later) start time toward the (earlier) end time.
To Defines the end of the time period.
Boundary Defines how the end boundary is handled. If Inside is selected, the first sample returned will be the first sample within the time period. If Outside is selected, the first sample returned will be the first sample outside of the time period. If Interpolate is selected, an interpolated sample is created for the period end.
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.)
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. When you use the Raw Data function, the Required Range for the array is not known until the data has been read, unless you enable the Maximum samples option. (It is limited to one row for the data and one for the column header, if that is selected.) Therefore, it is recommended that you use the Try It button to determine the dimensions of the array needed for the returned results. After closing the Try It feature's Query Results window, the worksheet Required Range is automatically adjusted to the required size. 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. Use the Try It feature to determine the correct array size, as described above.
Current Range This field displays the current selected range and the dimensions of that range. If the 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.