Calculated fields

A calculated field is a combination of functions, fields, and constants displayed in a report column. For example, you can calculate the sum of two fields, divide a field by a constant, generate random numbers, convert to ASCII, and so on.

If a certain indicator is missing from your database, it is possible to create different types of calculated fields and display them in reports and dashboards.

Note   Calculated fields can be defined only for reports based on data dictionaries of type standard or multi-dictionary.

Define a calculated field

To define a calculated field:

  1. Open the report for which you want to define a calculated field.
  2. Select the Edit button and select the Field Selection tab.
  3. Once on the Field Selection tab, click the Calculated Field button calculated field icon with yellow plus sign.
  4. The Calculated field dialog box displays.
  5. Provide values for the following fields:
  6. Field Description
    Field name A unique identifier for the field.
    Description A user-friendly name for the column. This is the value used to identify the field in the UI. For example, this is the value you see when you add a field to your report on the Design tab.
    Field group

    The group with which the calculated field is associated.

    Groups are used to find the fields easily when you design a report. By default, four groups are available: Attribute, Metric, Time axis and Calculated fields.

    You can define other filed groups in Data management > Field group.

  7. Select the Add button icon green plus sign to create an expression for the field.
  8. A dialog box displays allowing you to choose the type of expression and its attributes.
  9. Select a value from the Operand type drop down.
    • Function: Make complex calculations based on several functions. For example, you could choose a function to truncate and aggregate values.
    • Constant: Define a constant value with a certain data type.
    • Field: Refer to a value from an existing column.
  10. Select a data type from the Type drop down.
  11. Data Type Description
    String Alphanumeric characters.
    Numeric Value A numeric value that could be numeric, integer, float and double. See below for more detailed information about each type.
    Numeric Functionally equivalent to decimal. A literal representation of a number’s value. It uses decimal precision for rounding. Range is -1038+1 to 1038-1.
    Integer Range is -2147483648 to 2147483647.
    Float A small (single-precision) floating-point number. An approximate numeric type, meaning that it represents an exponential format for a given value. It uses binary precision when rounding. Range is 1.17549x10-38 to 3.40282x1038.
    Double

    A large (double-precision) floating-point number. An approximate numeric type that uses binary precision. 2.22507x10-308 to 1.79769x10308.

    Temporal Value A date, a time, a timestamp, or an interval of time.
    Date Describes a date using the fields YEAR, MONTH and DAY in the format YYYY-MM-DD.
    Time Describes a time in an unspecified day using the fields HOUR, MINUTE and SECOND in the format HH:MM:SS.
    TimeStamp Describes both a date and time using the fields YEAR, MONTH, DAY, HOUR, MINUTE and SECOND in the format YYYY-MM-DD HH:MM:SS.
  12. Specify a value for the Value field. Depending on the data type you choose in the Type field, you either select a value from the Value drop down or enter a value in the Value field.
  13. Select OK.
  14. Your new calculated field is displayed at the bottom of the Calculated field dialog box.
  15. Optionally, select the Add button to add another expression to this calculated field.

Examples of calculated fields

This section contains several examples of implementing calculated fields with all three operand types: Function, Constant and Field.

Use functions

As functions provide the greatest number of options amongst the operand types, this section provides several examples of their use.

Calculate the sum of two fields

You can create a column that will sum the values from two or more fields.

  1. Create a calculated field.
    1. In the Operand Type field, select Function.
    2. In the Type field, select Numeric.
    3. In the Value field, select Addition.
  2. Two branches are displayed by default in order to specify which two columns to sum. If you click on the plus button next to the Addition function, you can add as many columns as you like.
  3. To sum two fields, for each branch specify the Operand type as Field, the Type as Numeric and the Value as the column that you want to include in the sum.

Case Any Then

The Case Any Then function is similar to an SQL CASE expression with the following pattern:

CASE value_to_evaluate

WHEN value_to_evaluate=value_case_1 THEN result1

...

ELSE default_result

The following illustration shows how you can specify the different values and result.:

illistration of the case Any Then results

The SQL statements above relate to the illustration as follows:

  • value_to_evaluate as indicated by A.
  • value_case_1 as indicated by D.
  • result1 as indicated by B.
  • default_result as indicated by C.

In addition, you can use the Add button indicated by E to create additional WHEN branches in your CASE expression.

Example

Consider a situation where you need a new column named State_Code containing the values 0, 1 and 2 depending on the values of the existing column State. For the State Succeed you want to display 1, for Failed you want to display 0 and for any other state you want to display 2.

To achieve this, choose the Operand type as Function, the Type as String and the Value as Conditional Function > Case Any Then.

By default three branches are displayed. In this example we need one more branch. To add a new branch click on the plus button next to the Case Any Then function:

illustration of case Any Then function with the plus button available

The values in the following table refer to the illustration above. Use these values to create the several branches in this calculated field:

Callout Operand Type Type Value
A Field String State
B Constant String Succeed
C Constant Numeric 1
D Constant String Failed
E Constant Numeric 0
F Constant Numeric 2

When Condition Then

You can combine conditions with Boolean operators.

Consider a situation where you need to find out which products from your catalog have expired. You need to compare the expiration date with the current date. If the current date is greater than the expiration date, you display the value, Expired. Otherwise, you display the value, Valid.

In this situation, the Operand type is Function, the Type is String and the Value is When Condition Then. When you select this function two branches are displayed by default.

The values in the table below refer to the illustration above. Use these values to create the scenario illustrated.

Callout Operand Type Type Value
A Constant Date 01/01/14 (or you can use {s:today})
B Field Date ExpDate
C Constant String Expired
D Constant String Valid
E Function Boolean Greater strict

Divide a field by a constant

You can divide the values from a numeric column by a constant.

The Operand type is Function, the Type is numeric and the Value is Division.

The following illustration is an example of a calculated field that divides the values in the column, Total_Amount by 2.

illistration of calculated field with total amount

The values in the following table refer to the illustration above. Use these values to create the scenario illustrated:

Callout Operand Type Type Value
A Field Numeric Total_Amount
B Constant Numeric 2

Concatenate two string columns

You can concatenate the values of two or more columns. For example, if you need to display the Name and Surname in the same columns, you can use the Function Concatenate String. Two branches will be displayed and for each one, you specify the Fields to be concatenated : Name and Surname.

Conditional count

You can use the Division function to divide the values obtain by the usage of two functions. In this example the Conditional Count and Count functions are used.

This calculated field may be useful to determine for example the event error rate.

For this scenario you have to choose the Operand type - Function, the Type - Numeric and the Value - Division. When selecting this function two branches are displayed by default:

illistration of calculated field division between conditional state and count [EventId]

The values in the following table refer to the illustration above. Use these values to create the scenario illustrated:

Callout Operand Type Type Value
A Function Numeric Conditional Count
B Field String State
C Constant String FAILED
D Function Numeric Count
E Field Numeric EventId

Obtain event timestamp

The function eventTimeToMillis takes the EventTime field of the respective row and returns a numeric value representing that time in milliseconds.

In the image below you can see an application of this function in a calculated field, where the combination with the addMillis function leads to obtaining a timestamp for each event. This calculated field can be further used, for instance, for comparing timestamps.

The function eventTimeToMillis was introduced in 4.2.0 Service Pack 7.

Use a constant

When you define a calculated field with the Operand type of Constant, you should decide on a data type (for example, string, integer, date, and time) and the value that will be displayed when executing the report. If you do not add any condition, the same value will be displayed in all rows.

This type of calculated field is especially useful in combination with conditional functions described above.

Use a field

You can define a calculated filed that contains an existing column. This is useful if you want to add an aggregation on a certain column without modifying the existing column.

Related Links