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.
You may want to sort groups by the summary, or aggregate, calculation of each group of an Advanced Report. You can do this using Top/Bottom filters.
Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. You can supply any arbitrary cell in the report in the Top/Bottom filter. So if you set the number of displayed values to "infinity", then no values are excluded, but the ordering still takes effect.
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. You cannot use this to 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.
