When assigning filters to an Advanced Report from the Report Designer, users have the option of assigning Group Min/Max filters to the report. Group Min/Max filters will cause the report output to display details containing either the highest or lowest values in a field for either one group, multiple groups, or an entire data set.
Note: These operate differently from the Min() and Max() functions, which are used to manipulate specific data fields as part of formulas.
This tool is especially useful if you are only interested in viewing the highest or lowest values, such as the most recent hire date or the 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 you may define.
To access Group Min/Max filters, navigate to the Filters menu from the Report Designer Settings menu and click the Group Min/Max tab in the upper right-hand corner of the menu.
To filter a Data Field’s minimum or maximum value, either drag and drop the field to the Filter By panel, use the Add button, or double-click the field.
Specify Minimum or Maximum from the operator dropdown.
Use the Move Item Up
and Move Item Down
icons to change the filter priority.
To remove a filter, click the Delete
icon.
Applying Group Min/Max Filters
We will explore several ways of applying this type of filter using the sample report below.
With no Group Min/Max filtering, this report executes the following:
Ignoring Other Groupings
To apply the filter to one group only, select a group from the dropdown menu and check the Ignore other groupings on the report checkbox.
To best utilize this option, it is important to understand the difference between an inner group and an outer group. In the report designer, the topmost group (in this case, Orders.EmployeeID) is the outermost group and has the highest priority. The second group (in this case, Orders.CustomerID) is within the first and has the second highest priority. Each subsequent group is nested into the previous one and grouped after the others.
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.
In our example, both Buchanan and King have sold to customer BONAP. If we apply a maximum filter on order quantity for each Customer ID and leave the Ignore other groupings checkbox unchecked, the output shows the detail containing each customer's maximum quantity sold per employee.
Checking the Ignore other groupings checkbox, however, returns the customer's max quantity sale for the whole report, ignoring the grouping on Employee.
Since BONAP's order of Spegesild from King is greater than its order of Pavolova from 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.
Filtering the entire data set
Selecting the Entire Data Set from the group dropdown menu will disable the Ignore other groupings option and return the records containing the single maximum value for the selected field in the entire report.
In our example, selecting this option displays only BLONP's order from Buchanan because its quantity is the highest in the set.
Next Article: Top or Bottom Filters