I first wrote about this issue a decade ago when it showed up in databases like Paradox, Btrieve and Visual Dbase. But I recently saw it twice in PostGreSQL environments so I am going to write about it again.

The problem occurs when you add a new rule to the selection formula. The main symptom is that the criteria works fine when you click “use saved data” but then when you refresh you lose some valid records. Some people think they have a bad join which can also cause you to lose records, but a bad join will lose records even if you click “use saved data”.

In my experience, the issue is usually connected with an index in the database.  When Crystal Reports sends your new rule to the database, the database tries to use an index to speed things up. But some indexes don’t work correctly for filtering and will cause the report to miss some or all valid records. The solution is to prevent that rule from being sent to the database by forcing the rule to be applied locally. There are three approaches I have used:

1) Starting with CR 2008 (v12) you can put this rule into the “Saved Data” selection formula. This selection formula is always applied locally so the database usually doesn’t see it. Crystal will apply this selection formula to the records that are sent back by the database.

2) The first option may not work in call cases, and of course it won’t work if you use an older version of Crystal that doesn’t have that feature.  In that case you have to use the method I wrote about in 2007. You write the selection formula in a way that forces Crystal to apply that rule locally.  If the report is SQL based you want to prevent Crystal’s SQL generator from converting that rule into the SQL WHERE clause.  The most reliable way I have found is by putting the database field inside a Crystal function. For instance if your problem rule uses  a numeric field in the selection formula like this:

{table.Amount} > 50

I would write it as

Round({table.Amount},2) > 50

Or if your selection formula is:

{Transaction.Code} = “ABC”

I would write it as

Left({Transaction.Code},3) = “ABC”

Applying a function to a database field in the selection formula will usually prevent that rule from being converted into the SQL.  In some cases I write the rule as a separate formula field called {@Criteria} and then reference {@Criteria} in the selection formula by adding a line to the selection formula like:

…. and {@Criteria}

Note that this is the exact opposite of what you normally want to do. Normally we want ALL of the record selection formula to be sent to the database or convert into the SQL WHERE clause.  It makes your query more efficient.  When I have a slow report one of the first things I check is for functions in the selection formula.  But it doesn’t help to be efficient if the database can’t handle the rule correctly.

3) I recently had one case where neither of the above options solved the problem. No matter how I wrote the criteria formula, as soon as it was part of the selection formula I would lose records.  So we gave up on selection and used suppression instead.  This is a last resort.  You don’t use the {@Criteria} formula in the selection formula at all.  Without the rule the report will include some records that you don’t want. You then suppress the unwanted records with a suppression formula like this:

not {@Criteria}

When you use this method you also have to make sure these records aren’t included in any totals. This means writing a formula that only includes the records that you want and then doing all totals on these formulas. For instance I could write a formula like this:

if  {@Criteria}
then {table.Amount}
else 0

If I total this formula it won’t matter that I have suppressed records in the report. I don’t see them and they aren’t in the totals.