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.
NoteThese operate differently from the
Min()andMax()functions, which manipulate specific data fields as part of formulas.
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:
- Open the Filters dialog then, click the Group Min/Max tab. To open the dialog:
- (pre-v2021.1) From the Report Settings
menu, click Filters. - (v2021.1+) Click the Filters
icon on the toolbar
- (pre-v2021.1) From the Report Settings
- 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
- From the Minimum dropdown, choose either Minimum to see the smallest or earliest values, or Maximum to see the largest or latest values.
- 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
- 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
- clicking the Move Item Up
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:
In the Report Viewer, this report looks like this:
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.
TipTo 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:
- Add a sort on the field to do the min/max grouping on. Since sorts are implicit groups, adding a Group section is optional.
- At step 4 above, choose the sort field from the dropdown.
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.
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.
NoteIf 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.
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.
