Table of Contents

Calculated Fields

Calculated fields in a report are used to perform operations on existing columns so that the “calculation” does not need to be done in the SQL selection code. “Calculations” can be done on numbers, dates, and text.

The following chart shows the functions that are currently available. Users familiar with SQL will recognize the format of most of the operators and functions.

Area Type Action Notes
OperatorsArithmeticNegate (Subtract)
Add
Multiply
Divide
String Concatenate Add multiple text values together in a single string (see Calculated Field Notes)
FunctionsArithmeticExponentiate Calculate a new value using an exponent against a given number
Modulus Determine remainder from division operation
Date/Time Current DateTime Calculate the current date & time
Current Date Calculate the current date
Current Time Calculate the current time
Math Round Round a given number
Absolute Value Use the absolute value of a given number (always positive)
Square Root Calculate the square root of a given number
Pi Use the value of Pi (to six decimals)
String Right Return n characters of a text string beginning with the last character and counting to the left
Trim Left Remove any blank spaces from the left end of text string
Trim Right Remove any blank spaces from the right end of text string
Substring Return n characters from a text string beginning at a specified position
Length Return then number of characters in a given text string
Left Return n characters of a text string beginning with the first character and counting to the right
Uppercase Return a text string in all UPPERCASE characters

Note 1: Enclose literal values in single quotes. Database values are enclosed in double quotes.

To Begin

  1. Step to the “Display” tab of the report wizard.

Add a Calculated Field

  1. Click the [Add Calculated] button in the lower right corner of the page.
    1. The “Calculated Field” tool is displayed.

Update (Modify) a Calculated Field

  1. Click on the row an existing calculated value.
    1. The row is highlighted to indicate it is active.
  2. Click the [Update Calculated] button in the lower right corner of the page.
    1. The “Calculated Field” tool is displayed.

Remove a Calculated Field

  1. Click on the red X at the right end of the row for an existing calculated value.
    1. The row is removed from the field display list.

The Calculated Field Tool

The Calculated Field tool has five areas provides a list of available values that can be used in calculations.

  1. Name and data type information
  2. Values (fields) available for use in calculations
  3. Operators and functions available for use in calculations
    1. Use the drop-down list to access Functions
  4. Formula entry area
  5. Action buttons

Create a Formula (Calculation)

  1. Enter a Calculated Field Name.
    1. This is a required value and should be unique in the current report
  2. Data Type will be determined by the application in a later step. Do not enter a value manually.
  3. Use the Fields and Operators/Functions to add elements to the Formula area.
  4. Select the desired element and click [Insert Field] or [Insert Function] as needed. The element is displayed in the “Formula” area.
  5. Add all needed fields and operators/functions to build the formula.
  6. Click [Validate] to confirm that the formula is valid.
  7. A message is displayed to confirm if the formula is valid or not.
    1. If the formula is valid, the “Data Type” value is automatically populated with the appropriate value.
  8. Click [Save] to save the formula and close the “Calculated Field” tool.
  9. Click [Close] to close with “Calculated Field” tool without saving any changes.

Calculated Field Notes

  1. Formulas can be entered manually. The selection process described above is not mandatory.
  2. Formulas can only reference values provided from the data selection. A formula cannot reference another calculated field.
  3. Parentheses can be manually added in a formula to control the calculation order if necessary.
    1. Example: “BALANCE” * (10 + 3) will give a different result than “BALANCE” * 10 + 3.
  4. Concatenation can only be used on text strings. Text and numbers cannot be mixed.
  5. Non-field text values must be enclosed in single quotes.
    1. Example: Assume we want to concatenate “CARDNAME” and “CARDCODE” so that the resulting value is CARDNAME (CARDCODE) or Third Wave (C10000). The formula would look like: CARDNAME + '(' + CARDCODE + ')' where the parentheses are static values and so are enclosed in single quotes.
  6. A calculated field can be used to create a column used for an Action link. The “formula” can simply be a text string (enclosed in single quotes).
    1. Example: the “formula” is 'Click for PDF'. “Click for PDF” will appear in each row and serve as the link point for an Action link.