Understanding Group Sorting and Crystal’s “Top N” Reporting
In a recent presentation, I went through various advanced features of Crystal Reports. The underlying theme was that too often we use Crystal to replace reports initially written in COBOL, RPG, or some other language. Customers don’t take full advantage of Crystal’s analytic capabilities.
For example, few Crystal customers make use of Crystal’s “Top N” reporting function. Recently, I wrote about the importance of identifying your top performers. In Crystal this process is at least partly done through Top N reporting – a subset of Crystal’s group sorting functionality.
To see Top N reporting in action, let’s start with a simple report summarizing revenue by customer.
So far, so good. And if you’re coming at it from an operational mindset, you’re done. You have a tidy list of customers by revenue.
But what if you’re providing this to an analyst, and they want to know which are the really important numbers? Who are your crucial customers? You could tell them to dump it into Excel and go to work. But that ignores Crystal’s functionality. It’s easy to sort things if you’re sorting transactions – invoice by invoice for example. But this report is a summary of transactions. So we need Crystal’s group sort function.
To do this, select “Group Sort Expert” off the “Report Menu.”
You’ll see a window that looks like this:
Note that it shows we’re grouping by customer.
Click on the group sorting options.
Start by selecting “All” and then click “OK.”
It will then ask us which summary field we want to use to sort our groups:
Here, we’re going to select the US dollar amount and click “OK.” We’ll choose “descending” because we want the biggest numbers to show up first.
Et voila! Our data is now sorted by amount:
Now, what if we only want to see the star players – the top five for example? Go back to “Report Menu” and select “Group Sort Expert” again. This time, choose “Top N” and specify that we want to see the top five. We can also tell Crystal to include the others under the title “others,” so we can tie out properly.
And there you have it! All done neatly in Crystal, with no Excel dumps required.
Are you getting the most out of Crystal’s analytic capabilities? If not, why?
