Add Calculated Fields

Performs calculations using data in multiple data fields.

It is recommended that all fields based on expressions are given a description in the database view which includes the expression used. This makes it clear that an expression has been used to calculate the data, and what that expression is. This approach is followed in the supplied sample views.

Note:
Summary field results cannot be used in calculated fields.

1. On the Field List window, click the Field List tab and select the required field item.

   

2. Right-click and select Add Calculated Field from the drop-down menu. The calculated field is added to the field list.

   

3. On the Field List window, click the created field to display the field properties on the Properties window.

   

4. Click the ellipsis next to Expression to display the Expression Editor window.

   

   Click Fields to display the field list. Double-click field names to add them to the expression string. Use the toolbar to add operators between field names.

   Two custom functions are available to support the extraction of values, units or dimensions from a unit qualified attribute returned from string. These functions are:

   Note:
   Calculated fields are not included in the list of fields in the Expression Editor window. Input the name of a calculated field into the expression to use the result of that calculated field in that expression.

   Two custom functions are available to support the extraction of values, units or dimensions from a unit qualified attribute returned from string. These functions are:

   GetDouble([FieldName])

   Returns the value of a unit qualified value.

   GetUnits([FieldName])

   Returns the unit.

   GetDimension([Attribute name])

   Returns the dimension.

   The function FormatDouble([attribute], ‘units’, precision, 'label', fraction) can be used in a calculated field to convert the value of a real attribute from current units to given units and precision. Label and fraction parameters are optional where label is a string for overriding the default unit label, and fraction is a boolean value to indicate whether a decimal or fractional value for the unit is displayed. For example, the following converts HBOR to millimeters with two decimal places, regardless of the units and precision of the current session.

   FormatDouble([HBOR], ‘millimeter’, 2)

   FormatDouble([HBOR], 'inch', 2, 'in', true) converts the HBOR to inches with two decimal places and displays the inches value with the unit label 'in' and fraction.

   Quick reports created from Tags, where the units of a real attribute can be modified are displayed in the same units by creating calculated fields for each real attribute using this function.

   Please note:

   • These functions are not available from the list of functions available in the expression builder, and must be manually typed into the expression. The function name should be used followed by brackets.

   • GetDouble, GetUnits and GetDimension do not work in conjunction with other functions. For example GetDouble([HBOR]) + [NUMB] or GetDouble([HBOR]) + GetDouble([HBOR]) does not work. A calculated field must contain only one of the above functions as an expression.

     Whenever the calculated field uses the attribute name having ] in the attribute name, use the escape sequence \] for correct calculation of the expression.

     For example, to get the length of attribute named test pressure [1], use the expression Len([test pressure[1\]]).

     This also applies to GetDouble, GetUnits, GetDimension and FormatDouble functions.

     In addition, the following Summary Functions are available for use with unit qualified attributes:

   • Min

   • Max

   • Avg

   • Sum

     These are available in the expression builder, and when setting the summary function for a form control bound to a field.

5. Click OK to save the expression and close the Expression Editor window.

6. Drag and drop the selected field item onto the report. A control is created which displays the name of the field item and the yellow database icon.

   

7. Click the Print Preview tab to display a preview of the current report layout with the created control.

   

Functionality is available to perform calculations within a single data field. Refer to Add Report Totals for further information.