I recently had to figure out how to create a Top 5 and Bottom 5 ranked report for a customer. It’s been years since I’ve had to do this, so it was a nice little problem to solve.

Management Reporting (MR) has a number of carryover functions from FR. So how does MR translate this functionality? I have to say, I’m pretty impressed. It works well, is seemingly fast, and does the job without much effort in figuring it out. Also note that you can use this feature in the new Reports tool added to EPM Cloud pods this month.

First of all, here’s the report that I’m creating in this blog post. I’m showing you the final result so the steps will make more sense. And note that I could hide the “Act Rank” column (which I do in the last step) but I’m showing it here so that the construct is transparent.

Also note that the max number of items in this data set to be ranked is 6, which is why you see repeats between the Top and Bottom sections.

You’ll need knowledge of 3 important MR features for this solution:

1. Rank function
2. Sorting
3. Conditional suppression

Rank Function

The first important feature is the rank function. This can be used in a formula column or row and has 3 components to it: what you’re ranking, the order for ranking, and an optional “unique” parameter that provides 2 methods for handling duplicate values.

There is also an optional property called ifNN  (= “if nonnumeric”) that you can attach to the cell/col/row that you’re ranking. (Use the link above to read more about it.)

Sort Feature

Sorting is pretty straight forward in MR. You don’t have many options – you can choose rows or columns, then which specific cross row/column to sort, then if ascending or descending.

Conditional Suppression

Conditional suppression is another inherent feature in MR. There are quite a few options and potential combinations with Conditional Properties in general, so I’ll leave you to explore them yourself. 🙂 In essence, for conditional suppression you decide on the acting agent and its condition, and then MR suppresses it.

The way we’re going to use this is to suppress anything greater than the number 5, as this report is top 5 and bottom 5 and we don’t want anything higher or lower.

Basic Construct

The basic construct for a simple report is easy. In my sample report, segments (products) are in the rows, the Actual scenario is in the first column, and a formula column is in the second column. The POV for account (not shown) is for Net Revenue. My report is showing the top 5 segments for Net Revenue, as well as the bottom 5 segments for Net Revenue, both on the same report.

You need 2 columns and at least 1 row. To rank a single set of data, all of your data needs to be together in the same row or column. I happen to have 2 rows on my report because I am doing both top and bottom sections. Row 3 is my “top” segments data. Row 6 is my “bottom” segments data. I have to rank them separately to get different results.

The first column is a data column. The second column is a formula column, which is used for the Rank function. You can see this construct below:

My ranking formula for the cell B3 is RANK([A,3].ifNN(-1),desc,false).

My ranking formula for the cell B6 is RANK([A,6].ifNN(-1),asc,false).

^^ Notice the differences here. Both formulas rank a different cell. In addition, my “top” does a descending rank and my “bottom” does an ascending rank.

Once I have the above set up, I now need to get the sorting and conditional formatting in place.

For sorting, I’m essentially sorting the ranking column, column B. I want my top section to show the highest net revenue at the top, then the next 4 in descending order. For my bottom section I want to show the lowest net revenue at the top, then the next 4 in ascending order. So I’m going to create 2 sorts on this report (with the detail of the bottom section sort in detail shown below):

Finally, let’s finish off by putting the conditional formatting in place. Since the rank function returns a numerical value of where each data point ranks in conjunction with all the other members its grouped with, I need to pull only the first 5 in each section – I don’t want anything beyond that. So I’m going to add conditional formatting for both rows to suppress anything higher than 5. Luckily, due to the way I set up the previous steps, I only need to create one condition and apply it to both rows:

Now that I have all 3 components in place, my report runs and returns the top and bottom 5 segments for Actuals Net Revenue.

Final Formatting

The final step is to implement any formatting that I deem necessary for the report. You can see that I’ve already included text rows to mark the Top and Bottom sections. I could also choose to hide the ranked column so that it doesn’t show in the final result. When I do that, my report looks like this:

There isn’t a ton of data in this sample app, but it runs fantastically fast. I’ve tried it on slightly larger data sets and those also perform well. Enjoy!