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“.