Maximum and minimum highlighting-FineReport Help Document

Last update： 2022-01-06

I. Overview

1. Expected effect

In the process of making reports, when comparing in the same dimension, we hope to highlight the maximum or minimum value. The effect is shown in the following figure:

2. Implementation ideas

This is achieved by setting Conditional Formatting> Background. There are two ways to judge the "maximum" and "minimum":

1) By setting Data Setting>Summary>Max/Min.

2) Use the formula MAX() or MIN().

II. Example

1. Prepare data

Create a new general report, create a new database query ds1: SELECT * FROM SALES_VOLUME.

2. Method 1

1) The initial style of the design report is shown in the figure below:

Among them, column D and column E are only used as conditions and need to be hidden after setting. Select columns D and E, click the right mouse button at the column number, and select hide, as shown in the figure below:

The specific cell settings are shown in the following table:

Cell	Dataset	Data Column	Data Setting1	Data Setting2	Expansion Direction	Left Parent Cell	Up Parent Cell
A4	ds1	Region	Group	Common	Vertical	Default	Default
B4	ds1	Salesperson	Group	Common	Vertical	Default	Default
C2	ds1	Product_types	Group	Common	Horizontal	Default	Default
C3	ds1	Product	Group	Common	Horizontal	Default	Default
C4	ds1	Sales_Volume	Summary	Sum	No Expansion	Default	Default
D4	ds1	Sales_Volume	Summary	Max	No Expansion	Default	Default
E4	ds1	Sales_volume	Summary	Min	No Expansion	Default	Default

2) Select cell C4, click Conditional Formatting on the right, add two condition formattings, and rename them to Maximum and Minimum.

When the currentValue is equal to the maximum value D4 cell, the background is displayed in green. As shown below:

When the currentValue is equal to the minimum E4 cell, the background is displayed in yellow. As shown below:

3. Method 2

When designing a report, instead of using Data Setting>Summary to find the maximum and minimum values, the same effect can be achieved by directly using formulas to determine the maximum and minimum values in the Conditional Formatting.

Just change the condition type of the above maximum and minimum from Common to Formula.

The maximum formula is: C4 = MAX(C4[!0]{B4 = $B4}). As shown below:

The minimum formula is: C4 = MIN(C4[!0]{B4 = $B4}). As shown below:

Note: C4[!0] in the formula refers to all values expanded from cell C4, {B4 = $B4} means to filter by the value at the corresponding position of cell B4 after expansion.