Select the correct version number from the Application Version dropdown on the right-side of this article to show only the information pertaining to your environment.
To sort groups by the summary, or aggregate, calculation of each group of an Advanced Report, use Top/Bottom filters.
Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. Any arbitrary cell in the report can be the Top/Bottom filter.
To sort by a summary calculation:
- Ensure that your report has the appropriate aggregate formula in a group footer or header (v2019.2+) cell. The cell should return a numeric value in the report output, which you want to sort the groups by.
- Open the Filters dialog then, click the Top/Bottom tab. To open the dialog:
- (pre-v2021.1) From the Settings
menu, click Filters. - (v2021.1+) Click the Filters
icon on the toolbar
- (pre-v2021.1) From the Settings
- Check the Limit the report to the top/bottom values check box.
- From the Top/Bottom list:
- select Bottom to sort in ascending order, or
- select Top to sort in descending order.
- For versions v2018.2+: Check the All checkbox to sort all of the section values by the Top N criteria.
- For versions pre-v2019.1: In the # field, enter
2147483647. Why this number? We cannot enter ∞, so instead we want to enter an arbitrarily large number. This is the largest number that can fit without causing a report error. - From the Value list, select the group footer or header (v2019.2+) cell with the aggregate formula.
- If there is a For Each group, click the Delete
icon to remove it.
Keep in mind that this is not a Sort from the sort menu. It cannot make nested groups. This only affects the order in which a group of data is shown in the output. Because this is technically a Filter, this has precedence over the report sorts.
