I wrote two posts last month about finding where a field is used. I got to test these methods recently while helping a customer clean up a report. There were a handful of formulas shown as “in use” that we needed to delete. So I thought this would make a good demo of the new features.

First, we checked all of the options in the fly-out menus and found most of the formulas so we could replace them with other fields and delete them. Then we used the “FIND” method in design mode to see if any were sitting on the report. But there was one field still in use that didn’t show up in either method.

Next I tried a very old method. I exported the report to a “Report Definition File” and opened the exported file in NotePad. This usually works, but not this time.  I was surprised to find that there were no references to that formula anywhere in the report definition.

So now I was curious.  Where could you use a field that wouldn’t show up anywhere? So I started chopping:

• I saved the report under a new name
• I deleted every object from the report layout.
• I deleted all the groups.
• I deleted any extra sections so I was down to the basic 5.
• For each of the 5 basic sections I inserted a new section below it and then deleted the original section. At this point there would be no conditional formatting formulas.
• Then I deleted all of the formula fields. But I still couldn’t delete the problem formula because it was still “in use”.

So I looked for other things to delete. After deleting the parameters I noticed that there was a lone running total field. The running total wasn’t being used, but as soon as I deleted it the problem formula was no longer marked as in use. So I did an “undo” to check the running total and found that my problem formula was the summary field of the running total. So I did a bit of testing and here is what I learned:

If you do a regular summary of a formula (Insert > Summary) that formula will show up in the Report Definition export.

Also, if you use a formula field in one of the the condition formulas of a running total it will show up in the Report Definition and also in the “Find in formulas” fly-out.

But if you use the formula as the summary field in a running total, that formula will NOT show up in the Report Definition or any of the fly-outs. As far as I know there is no way to find that using Crystal Reports without checking every running total (or going to a third party tool). But now I know one more place to check.

Sometimes when overhauling a report I will have a long list of formulas with many of them no longer needed. You can tell which formulas CR is actually using by the check mark next to each field in the Field Explorer. You can delete these unused formulas individually. If there are groups of them listed together you can select a range of formulas and hit “delete”. But here is a quick way to delete ALL of the unused formulas:

• Click on the the first formula in the Field Explorer (even if it is in use)
• Hold your shift key and click on the last formula in the Field Explorer (even if it is in use)
• Hit the delete key.
• You will get a warning that you are trying to delete fields that are in use. Click “No”
• Crystal will delete all of the fields in the list that are not being used, but leave the others alone.

Even if you click “Yes”, Crystal can only delete some of the formulas that are in use. If a formula is sitting on the report layout but is not being used in any other way, Crystal can delete that formula from the list. To delete the other formulas you have to find where they are being used and replace them.

One common problem, especially in selection formulas, is when you have both AND and OR but no parentheses to clarify which should come first.   Take the following example:

{Orders.Order Amount} > 5000 and
{Customer.Country} <> "USA" or
{Customer.Region} = "CA"

If there is a CA order for $2,500, will it meet the criteria?  Using the above formula, yes.  That is because without any parentheses, the AND takes priority over the OR.  So in English this formula means:

Return all records that are outside the US and above $5,000. Also return all records that are in CA regardless of the amount.

If I intended that ALL locations were above $5,000  then I need to put parentheses around the OR to tell the report to treat that as one rule.  It could look like this:

{Orders.Order Amount} > 5000 and
( {Customer.Country} <> "USA" or
{Customer.Region} = "CA" )

A similar thing happens when you include an IF THEN ELSE in your selection formula like this:

if {?StateParameter} = "ALL"
then True
else {?StateParameter} = {Table.State}
and {Table.Date} in {?DateRange}

In this case the AND will combine the DateRange rule with the ELSE.  The DateRange parameter will be ignored when you select ALL States.  To apply the DateRange in ALL cases you need to put the IF THEN ELSE statement inside a pair of parentheses like this:

( if {?StateParameter} = "ALL"
then True
else {?StateParameter} = {Table.State} )
and {Table.Date} in {?DateRange}