iFIX Historical Reporting by stariya


									Report Generation in Dynamics iFIX
Using the Historical ODBC Driver to Build Summary Reports

Using the Dynamics iFIX historical ODBC driver, build a Crystal Reports query to retrieve hourly average
data over a 24-hour period.

Similar reports can be built in Microsoft Excel (using Microsoft Query, part of the Custom Install for Office
97), or in Access 97 (using the Access Query Wizard).

This demonstrates that the Dynamics iFIX historical data system exposes its data via open protocols,
thereby enabling powerful reporting, with any standard reporting package.

1. Start Crystal Query Designer (included with Crystal Reports version 6.0)
2. Select File / New Query
3. Select the „Use Crystal Query Expert‟ option
4. In the „Create Query expert‟, select the „SQL/ODBC‟ button
5. To „Log on Server‟, page down to „ODBC – FIX Dynamics Historical Data
6. Select „OK‟ & you should see „Log onto ODBC – FIX Dynamics Historical Data server succeeded
7. Under Choose SQL Table, select the FIX Dynamics node name & click „ADD‟ & „DONE‟
8. Select the „Fields‟ tab & add fields as desired (e.g., TAG, DATETIME, VALUE, MODE)
9. Select „Preview Query‟

Now the result of the SQL/ODBC query is displayed (see Figure 1). Each row is one hourly historical
record, which includes the tag name, date stamp, value and mode (sample, average, high or low over the
specified time interval). The query can be saved and used as the basis for a report which fills in the data
at generation time. Reports can be generated at run-time by using the Dynamics iFIX Time & Event
Scheduler, or on demand in the Workspace run-time environment..

The following fields (i.e., columns) are supported by the Dynamics iFIX historical ODBC driver:

Historical Field    Attribute
NODE                Node Name
TAG                 Tag Name
FIELD               Field Name
VALUE               Stored Historical Value
DATETIME            Date/Time Stamp
MODE                Sample, Average, High or Low
STATUS              Valid / Not Valid
ALARM               Alarm State
INTERVAL            Fixed Interval

Table 1. Dynamics iFIX historical ODBC driver supported process database fields.
Figure 1. Example of Dynamics iFIX Hourly Average Historical Data Query in Crystal Reports

NOTE: Currently, the Dynamics iFIX Historical ODBC driver supports only the SQL Select command.
This means that the data retrieved by the Historical ODBC driver is read-only, which is ideal for reporting

Building SQL Queries Using the Historical ODBC Driver

The Dynamics iFIX historical ODBC driver enables viewing (i.e., querying) the Dynamics iFIX historical
database as though it were a relational database table. In the examples below, the name „FIX‟ is the
default name of the ODBC table linked to the historical files on the node „FIX‟.

Here are a few examples of how to build SQL queries using the Dynamics iFIX Historical ODBC driver.
These examples are valid in Crystal Reports. Other SQL implementations may use slightly different
syntax. For example, Microsoft Access requires a semicolon to terminate a SQL query; Crystal Reports
does not. Access does not require double quotes delineating the column names, whereas Crystal
Reports does require them.

SQL Query                                              English Translation
SELECT                                                 Retrieve all records & fields from the linked
 FIX."STATUS", FIX."ALARM", FIX."INTERVAL",            ODBC historical table „FIX‟; i.e., retrieve all
 FIX."NODE", FIX."TAG", FIX."FIELD",                   historical data & all supported fields (the
 FIX."VALUE", FIX."DATETIME", FIX."MODE"               default query for the iFIX Historical ODBC
FROM                                                   driver retrieves sample data for 1-minute
 "FIX" FIX                                             intervals over the last 2 hours)
SQL Query                                             English Translation
SELECT                                                Retrieve historical hourly average data (tag
 FIX."TAG", FIX."DATETIME", FIX."VALUE",              name, date stamp, value, mode) for the tag
 FIX."MODE"                                           „Temperature1‟ from the node „FIX‟ for the
FROM                                                  duration beginning at midnight on 11-27-98
 "FIX" FIX                                            & ending at midnight on 11-29-98
  FIX."MODE" = 'AVERAGE' and
  FIX."INTERVAL" = '01:00:00' and
  FIX."DATETIME" > {ts '1998-11-27 00:00:00'}
  FIX."DATETIME" < {ts '1998-11-28 23:59:59'}

Table 2. Sample Dynamics iFIX historical queries (from Crystal reports 6.0).

NOTE: In the conditional (where) clause above, the historical data mode is case-sensitive (i.e., must be
upper case. Also note the single quotes (required) delineating parameters.

The time stamp notation, including braces, is based on one of the SQL standard date formats.

Building Variable SQL Queries in VBA Using the Historical ODBC Driver

The real power of the Dynamics iFIX Historical ODBC driver comes from using variables that get filled in
by users in the run-time environment (or is executed by the Dynamics iFIX Time & Event Scheduler). A
single template screen (i.e., a user interface integrated into Dynamics iFIX) can be built that creates
generic historical reports, depending on the tag name, duration, interval and mode selected by the user at
For example , the following sample SQL string was created in the Dynamics iFIX VBA environment ( for
details, please refer to the example supplied with the Dynamics iFIX graphics screen histreport.grf ):

Dim strQueryAvg As String

strQueryAvg = "Select DATETIME, VALUE FROM FIX " & _
 "WHERE TAG = '” & strTag & “' and MODE = 'AVERAGE' " & _
 "and INTERVAL = '” & strInterval & “' and " & _
 "(DATETIME > {ts '" & strStartTime & "'} and " & _
 "DATETIME < {ts '" & strEndTime & "'})"

„ Note again the use of single and double quotes.
„ Then ADO connection and recordset objects are defined, &
„ the connection is made to the Dynamics iFIX Historical ODBC server.
„ Then the recordset is populated with data from the historical query:

Dim cnADO As Connection
Dim rsADO As Recordset

 This example requires that the ADO 2.0 object libraries be installed & registered on the machine (see
www.microsoft.com/ado to download). Also, a grid control such as Microsoft FlexGrid is required.
 A project reference must be made to the ADO 2.0 Object Libraries. This can be at the picture level in
FIX Dynamics.
If cnADO Is Nothing Then
    Set cnADO = New ADODB.Connection
    cnADO.ConnectionString = "DSN = FIX Dynamics Historical Data; UID = sa; PWD = ;"
    cnADO.Open "FIX Dynamics Historical Data", "sa", ""
End If

If rsADO Is Nothing Then
    Set rsADO = New ADODB.Recordset
End If

rsADO.Open strQueryAvg, cnADO, adOpenForwardOnly, adLockBatchOptimistic

„ Populate a grid control from the recordset results

Dim r As Integer
Dim c As Integer
While rsADO.EOF <> True
  grdHist.Row = r
  For c = 0 To 1
  grdHist.Col = c
  If rsADO(c) <> "" Then grdHist.Text = rsADO(c)
  Next c

To top