Indexes in the database normally improve the performance of a report. But I had a situation with a customer this week where the index caused a report to misbehave. The user ran a simple report listing account numbers. The report had no selection criteria. One of the account numbers showed up in two different records. But when he added a selection criteria to select just that one account number, only one of the two records was returned. The account numbers were identical and there were no other report features (like select distinct or group on server) that would affect the number or records returned. He puzzled over it for hours before sending me a copy of the report with saved data.When I added the same criteria at my end (and used the saved data) I got both records. But when I sent it back to him and he clicked “Refresh” he got only one.

That told me the problem wasn’t in the report. Somehow the database wasn’t doing the right query and I knew where to look first. When some databases get a criteria from Crystal they try to optimize the filtering by using an index. But I have found that on rare occasions the index that is selected by the database won’t include all records. In these cases I have found that I have to use a Crystal function in the selection formula. Adding a function to the criteria prevents it from being passed to the database, which means that all records are returned to Crystal and filtered locally. In most cases this makes the selection process less efficient, but in some cases this is the only way to get the correct data.

As a test I had him change the selection formula to use a Crystal function with that field. The report now returned both records. When the user saw that the problem was the index he said he would change the index and see if that affected the original report. Sure enough, when he changed the index from descending to ascending, the original report worked correctly.

This user was connecting to Visual Dbase through ODBC but I have seen this behavior several times in PeachTree Accounting (Btrieve based) reports. So, if you are ever baffled by this type of behavior, you know where to look.

Added 3/12/08:

Found another example of this behavior in reading old Paradox files. In one instance a valid criteria caused zero records to be returned. In a second instance a criteria caused Crystal v9 to crash. Both situations were resolved by writing the criteria so that they had to be processed locally in Crystal.