Data Management - 70 -
Columns
Columns Grid
Cell
This field indicates the cell containing the data to export to the configured column.
Column
This field indicates which column in the database the data will be exported to.
Fixed
This indicates that the cell reference will remain fixed in place even if the
management connection has been configured to export a dynamic range.
For example, a database table has 5 columns including a Timestamp column and 4
data columns, and a daily report has a timestamp in cell $A$1 and 4 columns
containing the day’s data within the cell range of $B$4:$E$27. We can configure
our data export to use the timestamp in $A$1 for each record in the cell range by
assigning the cell to the Timestamp column and checking Fixed.
Type
This field displays the data type of the selected column in the database. See the
Database Management section of this document for more information on data
types.
Note that when the data type of a column is modified in the database, the export
group needs to be re-configured. From the Setup tab, change the Export Table
section and then re-select the Table.
Commands
In the grid, specify SQL commands (some level of SQL is required). A command
can contain a cell reference enclosed in curly brackets e.g., {$A$1}. When a cell
reference uses the $ symbol, it remains fixed while the range is exported. If the $ is
not used, the value changes for every row/column in the Source range.
Auto Index
When exporting to more than one table, an Auto Index can be used to maintain a relationship between
the tables. The Auto Index is a number that is incremented after the completion of an UpdateSheet or
UpdateBook action so while the action is in progress, each export will use the same index value.
For example, suppose a database contains two tables. One table contains the columns Batch ID,
BatchStartTime and BatchEndTime, and the other table contains the process data for each batch. If an
Auto Index is used a unique relationship can be made between the tables during the export operation.
Creating the Auto Index Table
The Auto Index table can be created using the Database Manager application. Connect to the
database where the data will be exported.
Select Add to create a new table. Set Name to AutoIndex. Under Columns in the first row, set Name
to ID and Type to Number (long). Click OK to create the table.
Usage
To use the Auto Index feature each database table that will be used must contain a column for the
Index, and the data type must be Number (Long). When configuring the Cell for the Column, right
click and select Auto Index. Note that when exporting to tables in two separate databases, the
AutoIndex is determined by the table in the first export.
Example
A daily report is required showing hourly maximums over the day for tanks and extruders. In addition,
the daily maximum for each tank and extruder is displayed using MAX functions within the worksheet.
A second, monthly report is required to display the daily maximums for the month.
While it is possible to get the daily maximums for the month from the historian, this requires a lot of
number crunching on the historian. Since the daily report already has these values it would be less