I learned something new about Crystal Reports today. It has to do with Group Selection, which you may not be familiar with. Group Selection allows you to filter a report based on subtotals. For example: “only show me customer groups with a subtotal of more than $5000”.  There is a separate Select Expert and a separate Selection Formula just for Group Selection, which mirrors Record Selection.

The tricky part about Group Selection is that while it shortens the report, it normally doesn’t reduce your grand totals. This can confuse users. So if you have a grand total that says you have 50 customers, and you use a group selection filter to eliminate half of the customer groups, your grand total will not go down. It will still say you have 50 customers even if only 25 remain in the report.  The normal solution is to replace the summary field grand total with a running grand total.  Running totals reflect the group selection criteria while regular summary fields do not.

What I learned today is that when your group selection eliminates ALL the groups in the report, then your grand totals will all be blank or zero. I was expecting the Group Selection to eliminate the groups, but for the grand totals to remain the same.   

You can read more about Group Selection and Running Totals in my Expert’s Guide to Totals, which is now a free download.

A customer gave me a challenge related to a [size] field. It was a string that stored values in fraction format like “2 1/8”. He wanted to do calculations with the sizes so he needed them converted to numeric values with the fraction converted to a decimal value. It is the opposite of another formula that I wrote to convert decimals into fractions.

The formula works.  It assumes that the there is a space between the (optional) integer and the fraction.  It also assumes that there are only numbers, spaces and a slash in the field, up to the end of the fraction. What comes after the fraction doesn’t matter.  The only change you need to make is to put replace the field name on the first line with your database or formula field:

Local stringVar z := trim({ItemMaster.Width});
Local NumberVar x := Instr(z, '/');
Local NumberVar y := Instr(z, ' ');
Local NumberVar NumStart := x - 2;
Local NumberVar NumEnd := x - 1;
Local NumberVar DenStart := x +1;
Local NumberVar DenEnd := x +2;
If DenEnd > Length(z) then DenEnd := Length(Z) ;
If NumStart < 1 then NumStart := 1;
Local Numbervar Num := if '/' in z then Val(z [NumStart to NumEnd]);
Local Numbervar Den := if '/' in z then Val(z [DenStart to DenEnd]);
Local Numbervar FractionVal := if Den = 0 then 0 else Num/Den;
Local NumberVar IntVal :=
if not ('/' in z) then val(z) else
if ' ' in z then val(z [1 to y]) else val(z);
IntVal + FractionVal

Let me know if you find any issues.

This post is an update to an older post where I suggested a method for getting cross-tabs to reflect group selection criteria.  I just discovered a flaw in that approach, and so here is a better way.  You can download a sample report that illustrates the issue and a screen shot of the report output below:

This report has a group selection formula that limits the results to customers with a subtotal of over 50K. There are three customers that meet this criteria out of the 77 customers in the report data. To get an accurate grand total I added a running total (in blue) to the report footer.

A normal cross-tab added to this report would show all 77 of the customers that met the RECORD selection criteria. But we want just the three customers that meet the GROUP selection criteria. So I added two modified cross-tabs to the report, one using my original solution and one using the new solution. You will notice that the grand total of the Original solution (in Red) doesn’t match the the running total in blue. It is off by the value of a 4th customer who is being included in the cross-tab incorrectly.

This customer doesn’t meet the criteria and is not shown in the final report output. But if you were looking at the report in preview mode you would see that this customer is the first customer listed in the group tree. If we then changed the group to Descending Order, then a different customer would be first in the tree, and that customer would show up (incorrectly) in the cross-tab.   I have no idea why the first group is treated differently.

But if you look at the New Approach, this extra customer is shown with a zero amount and the total of the cross-tab (Green) matches the running total in blue. We could eliminate this extra row completely by using the “Suppress Blank Rows” option in the cross-tab properties.

So what is the new approach?  We still create a formula for the Amount field, but we incorporate the Group Selection criteria into the formula, something like this:

if Sum ({Orders.Order Amount}, {Customer.Customer Name}) > 50000
then {Orders.Order Amount}
else 0

One note on using these types of formulas in the cross-tab.  You can add these formulas while in the cross-tab expert, but if you go to change any cross-tab properties, the formula will be replaced.  To get around this you can temporarily comment out the formula and just use the raw database field.  Then you can make any cross-tab changes.  When you are done you can put the formula back the way it was.