Ranking based on a ratio

Tuesday 30 December 2008 @ 11:32 pm

I recently reread a newsletter article I wrote a few years back and was reminded of a trick that I had forgotten. The article was about ranking groups using a formula that contains summary functions. It is simple to summarize most formulas in a report (sum, count, maximum, etc). And once you have a summary field it is usually a simple matter to use that summary field to rank the groups. Crystal calls this a Group Sort (or a TopN in older versions).

The challenge comes if the formula itself contains a summary operation. Then Crystal will not let you do a summary of that formula, and then there is no way to rank the groups based on that formula. However the article I wrote points out two very important exceptions to this rule:  You can do a summary of these formulas either inside a cross-tab or inside a chart.

So take a common formula that calculates a profit margin percentage:

( Sum ({Orders.Amount}, {Customer.Customer Name}) -
Sum ({Orders.Cost}, {Customer.Customer Name}) )
% Sum ({Orders.Amount}, {Customer.Customer Name})

If I wanted to find the groups with the highest margins or to rank all my groups by there margin, I would have a problem. Crystal won’t let me summarize this formula which means no TopN or ranking feature for the groups. But, I can do the ranking or TopN in a cross-tab.  The steps would be:

1) Create the formula

2) Add a cross-tab with the row by customer and the formula as the summarized field.

3) Change the summary to “maximum”.

4) Apply the TopN to the cross-tab (not to the groups).

These formulas are also available for summarizing in charts, even back to v8.5. And, since a chart can also do it’s own TopN, we can find the top 5 profit margin percentages using a chart in v8.5, but not with a cross-tab.

If you like articles like this you might want to check out my three volumes of “Expert Techniques“.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server