Top/Bottom Filters

Top/Bottom filters, also known as Top N filters can be added to a report. Top N filters allow you to filter data to only the rows with the top or bottom values, for either data fields or data summaries, per group iteration.

Top/Bottom filters work after all other filters have been applied. If a Standard or Group Min/Max filter has narrowed down a data field, a Top/Bottom filter will work on the remaining values.

To show only the top or bottom values, for either data fields or data summaries:

1. 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
2. Check the Limit the report to the top/bottom values checkbox.
3. From the Top/Bottom dropdown select either Top to show the largest or latest values or Bottom to show the smallest or earliest values.
4. Choose which values will be displayed, either:
   • check the All checkbox to sort all of the section values by the Top N criteria
   • enter an integer into the # textbox to specify the desired number of values (e.g. enter 2 to see the top 2 or bottom 2 values). This field is only available when All is unchecked.
5. Select the data field or group field to filter from the Value dropdown.
6. Choose the Direction that the results should be sorted:
   • Ascending — items will be sorted from least to greatest
   • Descending — items will be sorted from greatest to least
   • None — items will be sorted in the original report sort order

To optionally filter the top/bottom values of each sorted group on a report, called a For Each Group:

7. Click the Add Group button.
8. Select one of the sorted groups on the report to group by from the dropdown list. You may also type into the dropdown list to search for the name of a field.
9. Click Okay.

To remove a For Each Group, click the Delete Dropdown  icon.

To remove a Top N Filter, uncheck the Limit the report to the top/bottom values checkbox.

Examples

Top 3 Performing Salespeople

A report showing the name and sales revenue for the top three performing salespeople in an organization may look like this:

In the Report Designer, the report contains the Employee Name and an aggregate of all of their order revenues summed together. The detail rows are collapsed so they are not initially visible in the Report Viewer until they are expanded.

To filter all of the employees to show only those with the highest three revenues, a Top N Filter is applied with the following properties:

• Top/Bottom — set to Top to choose the largest values
• All — unchecked, enabling the # textbox
• # — set to 3 which will filter out all employees except for the top 3
• Value — set to the formula =aggsum({OrderDetails.Revenue}) to set the criteria to the aggregate sum of each employee’s orders
• Direction — set to Descending so that the largest value appears first and then in descending order from the Top.

Largest Donations per Donor

A report showing a fundraising goal may want to show the name of each donor, and the top two largest donations that each donor made to the cause may look like this:

The report is sorted by the donor’s last name. Each donor may have made many contributions to the cause. In the Report Designer, the report looks like this:

If a Top N Filter is added, only the top donations will be shown. To show the top two donations for each donor, a Top N Filter with a For Each group is added with the following properties:

• Top/Bottom — set to Top to choose the largest donation values
• All — unchecked, enabling the # textbox
• # — set to 2 which will filter out all donations except for the top 2
• Value — set to donations.Amount to set the filtering criteria to the amount of each donation
• Direction — set to Descending so that the largest value appears first and then in descending order from the Top.
• For Each — appears because the Add Group button was clicked to add a For Each group. Set to donors.Surname to enable the Top N filter to be applied to each donor’s name.