Group Min/Max Filters

Group Min/Max Filters cause the report output to display detail containing either the highest/latest or lowest/earliest values in a field for either one group, multiple groups, or an entire data set.
This tool is especially useful if interested in viewing the highest or lowest values, such as the most recent hire date or highest revenue figure, in a given set. Group Min/Max filters are compatible with standard filters, and there is no limit to the number of group filters that can be defined.
To add a Group Min/Max Filter to a report:

1. Open the Filters dialog then, click the Group Min/Max tab. To open the dialog:
   • (v2021.1+) Click the Filters  icon on the toolbar
2. Add a data field to filter by, called the Filter Field by first selecting a Data Object from the dropdown, and then either:
   • clicking the arrow icon to the right of the data field’s name
   • clicking the  Add button at the bottom of the data field’s tree
   • double-clicking on the field’s name
   • drag-and-dropping the data object from the tree to the Filter By panel
3. From the Minimum dropdown, choose either Minimum to see the smallest or earliest values, or Maximum to see the largest or latest values.
4. From the For Each dropdown, choose the data set to group the minimums or maximums by. Choose Entire Data Set to show the minimum or maximum of the entire data set, or a data object or sort field to show the minimum or maximum of a group. See details in the sections below
5. If necessary, change the order of the filters by either:
   • clicking the Move Item Up  or Move Item Down  icons
   • click and drag the Grip  icon to move the filter to the desired location

Like standard filters, multiple Group Min/Max Filter Fields may be added. Subsequent filter fields will further filter the report in the event of a tie.
Consider a report that lists the orders placed by customers grouped by the employee that took the order. The report design may look like this:

Figure 1 — Report Designer layout of the example report used in this section

In the Report Viewer, this report looks like this:

Figure 2 — Report Viewer view of the above example report

This report will be used in each of the following examples

Entire Data Set

The most basic usage of a Group Min/Max Filter is to limit the whole report to a singe record, representing either the minimum or maximum value for entire data set. To do this, at step 4 above choose Entire Data Set.
Setting a Group Maximum Filter on the example report’s OrderDetails.Quantity field will reduce it to one record with the line item with the largest quantity of a single product out of all of the orders for all of the salespeople.

Figure 3 — The Report Filters dialog for the Entire Data Set Group Min/Max Filter

Figure 4 — The resulting output after applying the Entire Data Set Group Min/Max Filter on the report from Figure 1

Tip

To see more than one maximum or minimum for a data set, use a Top/Bottom filter instead.

Min/Max for each Group

Another common usage for Group Min/Max Filters is to see the min or max value for each instance of a group.

To do this:

1. Add a sort on the field to do the min/max grouping on. Since sorts are implicit groups, adding a Group section is optional.
2. At step 4 above, choose the sort field from the dropdown.

Figure 5 — Report Filters dialog when filtering with a group

Since the customers are grouped by employee, the resulting report output will show the product with the highest quantity ordered by each customer handled by each employee.

Figure 6 — Showing the products with the largest quantity on an order for each customer and each employee

To show the highest quantity ordered by each customer regardless of who placed the order, check the Ignore other groupings on report checkbox in the Report Filters dialog. In other words, show the largest product quantity ignoring the Employees group.

Note

If applying a Group Min/Max filter to an outer group, then checking the Ignore other groupings checkbox has no effect on the report output because the outermost group takes precedence anyway. If applying a Group Min/Max filter to an inner group, however, the button takes effect.

Figure 7 — Report Filters dialog when filtering with a group while ignoring other groups

Notice that Bon app’ (BONAP) appears only once in Figure 8 in contrast with Figure 6. Due to the fact that BONAP’s order of Spegesild from Robert King is greater than its order of Pavolova from Steven Buchanan, BONAP appears only once on the report. If BONAP had ordered the same quantity from both Buchanan and King, it would appear under both names, even with the Ignore other groupings checkbox checked.

Figure 8 — Showing the products with the largest quantity on an order for each customer regardless of the employee that placed it