Crystal allows you to put your groups in order based on the value of each group’s subtotal. This is found in the Group Sort Expert (or the TopN feature in older versions). But the group sort expert in CR can’t rank groups based on a formula – you have to use a subtotal as the ranking field. Sometimes you can convert your formula into a subtotal, but the formulas I get asked about most are ratios. People divide one subtotal by another to produce an average or a ratio and they want to use that as a group ranking field. Unfortunately, you can’t rank groups in CR based on ratios.

One solution is to use a cross-tab, because CR can rank rows of a cross-tab using a ratio. But not everyone can meet their layout requirements using a cross-tab.  This week a customer needed to rank groups based on a ratio and I couldn’t get the layout they wanted with a cross-tab, so we had to go to the last resort.  We calculate the two subtotals in SQL using a “Group By” and then crystal can calculate the ratio as a detail level formula. The groups in the original report would be rolled up in SQL by the “Group By” and given to Crystal as one record.

There were two challenges to doing the calculations in SQL:
1) The fields we were totaling had null values.
2) We needed to calculate the ratio using a distinct count.
I have written before about how much easier it is to do calculations in a Crystal formulas as opposed to SQL. One of the reasons is how much easier it is to deal with Null values in CR. But I did find a relatively simple method in SQL using the Coalesce function. If you want to choose between two values and choose the one that isn’t null this simple expression will work:

Coalesce (table.field1, table.field2)

 If you want null values to default to zero (or any other value) you can use:

Coalesce (table.field1, 0)

 And if you want to add two values together when either one might be null you can use:


Coalesce (table.field1 + table.field2, table.field1, table.field2)

This will check the values from left to right and return the first value that isn’t null.

Also as part of this project I found the syntax for a ‘distinct count’ in SQL, which I had never had to use before. All you do is put the word Distinct inside the parentheses, just before the field name like this:

Select count ( Distinct table.field ) as DesiredColumnName

If you are using a “Group By” it calculates a value for each group, and if you are not using a “Group By” it calculates one value using all records.