Skip to main content

Using Nested Min Expressions in Finished Reports

Cory Fifield
Updated

The Min() function in Finished Reports compares two values and returns the smaller value.

When you need to compare more than two values, you can nest multiple Min() functions together.

For example:

Min(Min([calculatedField1],[calculatedField2]),[calculatedField3])

This evaluates the first two values, then compares that result with the third value.

Example Scenario

In this example, the report contains three calculated fields:

  • calculatedField1
  • calculatedField2
  • calculatedField3

Each calculated field returns a numeric value based on a date calculation.

The goal is to display the lowest value from all three calculated fields in a separate Lowest column.

Finished Reports Designer containing three calculated fields and a Lowest column.

Create the Calculated Fields

The example uses three calculated fields that will later be compared using the Min() function.

To create a calculated field:

  1. Open the report in Finished Reports Designer.
  2. Open the Field List.
  3. Right-click the applicable data source or area in the Field List.
  4. Select Add Calculated Field.
  5. Select the new calculated field.
  6. Enter a name for the calculated field.
  7. Set the appropriate Field Type for the value returned by the expression.
  8. Select the Expression property to open the Expression Editor.
  9. Enter the expression for the calculated field.
  10. Select OK to save the expression.

Repeat these steps for each calculated field needed in the report.

In this example, create:

  • calculatedField1
  • calculatedField2
  • calculatedField3

Calculated Field Collection Editor showing the calculated fields used in the report.

Configure calculatedField1

Select calculatedField1 and enter the following expression:

DateDiffDay([LAST_WORK_DATE], Today()) - 100

Make sure the calculated field uses an appropriate numeric field type.

Expression used for calculatedField1.

Configure calculatedField2

Select calculatedField2 and enter:

DateDiffDay([OCCURENCE_DATE], Today())

Expression used for calculatedField2.

Configure calculatedField3

Select calculatedField3 and enter:

DateDiffDay([WAIT_DATE], Today())

Expression used for calculatedField3.

Note: These calculated fields are examples used to demonstrate the nested Min() expression. You can use the same technique with other numeric fields or expressions.

Compare Two Values

To return the lowest value between two fields, use:

Min([calculatedField1],[calculatedField2])

The function compares the two values and returns the smaller value.

Compare Three Values

To compare three values, nest one Min() function inside another:

Min(Min([calculatedField1],[calculatedField2]),[calculatedField3])

The expression works in two steps:

  1. Min([calculatedField1],[calculatedField2]) determines the lowest value between the first two fields.
  2. The outer Min() compares that result with calculatedField3.

The final result is the lowest value from all three fields.

Add the Nested Min Expression to the Report

  1. Select the report control that will display the lowest value.
  2. Open the control's Expression setting.
  3. Enter:

Min(Min([calculatedField1],[calculatedField2]),[calculatedField3])

Label configured to use a nested Min expression.

  1. Open the Expression Editor to review or modify the expression if needed.

Nested Min expression comparing three calculated fields.

  1. Select OK or Apply to save the expression.

Compare More Than Three Values

Additional Min() functions can be nested to compare more values.

For four values:

Min(Min(Min([Field1],[Field2]),[Field3]),[Field4])

Each additional value is compared with the result of the previous Min() calculation.

Preview the Results

Select Preview in Finished Reports Designer to verify the results.

The Lowest column should display the smallest value from the calculated fields for each record.

Finished Report preview showing the lowest value from the three calculated fields.

Download the Example View

A current example View is attached to this article. It includes the calculated fields and nested Min() expression shown in the examples above.

Scroll to the Attachments section at the bottom of this article and download ExampleMin.vdm.

Open the View in VDM to review the calculated field expressions, nested Min() expression, and Finished Reports layout.

Related articles

Related articles

Comments

0 comments

Please sign in to leave a comment.