Over the holiday break I had a customer contact me about a report that had just started taking a very long time to run. The first place I looked was in the record selection formula where I found this in the second line:

{Orders.OrderDate} -1 in LastFullWeek

I suspected that this was the problem. To confirm I had him send me the original report that ran in the normal time. Here is what the original said:

{Orders.OrderDate} in LastFullWeek

Apparently the requirement for the report had changed from the prior week starting on Sunday to the prior week starting on Monday. That minor change causes Crystal to completely drop the date rule from the automatically generated SQL. This means the database will send back ALL dates and Crystal will have to apply the date filter locally. I had him try this instead:

{Orders.OrderDate} in Minimum(LastFullWeek) +1 to Maximum(LastFullWeek) +1

Both version 1 and version 3 return the same results but version 1 adjusts the field while version 3 adjusts the comparison values. Version 3 will make it into the SQL WHERE clause while version 1 will not.

The same problem happens when you use a function on the field. Here are two common examples I see:

Date ({Orders.CreateTimeStamp} ) in ...

Round ( {Orders.Amount} ) = ...

If the report performance is fine than these examples can stay, but if you need to speed up the report then these should be written without the functions, so that they are incorporated into the automatically generated SQL.