Top 5 Report

I see this asked a lot on forums. Its really simple but can be a quite challenging issue for beginners. They usually know how to compute for the top 5 (or 10 or 20), but how to display only the top 5?

Here is a report that will display the Quantity sold for each month and show the top 5 months with most quantity sold in descending order. Also shown is how Rank is calculated.

If we run the report we get the following result:

Now the question is, how do we display the top 5 only? The obvious answer is to filter [Rank] < 6 (or [Rank] <= 5). But if you add this filter in your query, it does not seem to work (for relational reports).

The solution is rather simple; add the filter condition in the detail filter section of your query. Then set the application property to ‘After Auto Aggregation’.

Below is our final output.