Add Calculated Fields to a Report

The main purpose of calculated fields is to perform calculations using data in multiple data fields. To learn how to perform calculations within a single data field, refer to Add Totals to a Report.

Notes: 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 to the user that an expression has been used to calculate the data, and what that expression is. This is approach is followed in the supplied sample views.

Summary field results cannot be used in calculated fields.

To add a calculated field to a report, follow the instructions below:

1. To create a calculated field, right-click any item in the Field List and choose Add Calculated Field from the displayed menu.

   

   The calculated field is added to the field list.

2. In the Field List, select the created field to show its properties in the Property Grid. Change the Field Type property to an appropriate value.

   

3. To create an expression for the calculated field, click the ellipsis in the Expression field of the Property Grid.

   The Expression Editor is then displayed.

4. The user can also open the editor by right-clicking on the field in the field list and choosing Edit Expression from the displayed menu.

   

5. Click Fields to display the field list.

6. Double-click field names to add them to the expression string. Use the toolbar to add operators between field names.

   Note: Calculated fields are not included in the list of fields in the Expression Editor. Users must type the name of a calculated field into the expression to use 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) may be used in a calculated field to convert the value of an real attribute from current units to given units and precision. Label and fraction parameter are optional where label is string for overriding the default unit label and fraction is boolean value to indicate whether decimal or fractional value for the unit should be 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) will convert the HBOR to inches with two decimal place and will display the inches value with unit label 'in' and fraction.

   Quick reports created from Tags, where the units of a real attribute can be changed, will be 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 will not work in conjunction with other functions. For example GetDouble([HBOR]) + [NUMB] or GetDouble([HBOR]) + GetDouble([HBOR]) will not work. A calculated field should therefore 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 expression.

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

     Same will apply to GetDouble, GetUnits, GetDimension and FormatDouble function.

     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.

7. To close the window and save the expression, click OK.

8. Drag the calculated field from the Field List onto a control or a table cell.

   

   The calculated field is now set up.

9. Switch to the Preview tab to view the result.