I found an apparently longstanding bug in Crystal Reports recently. It can be demonstrated by creating a simple report with three formulas (A, B and X). Formula A can reference any field. Formula B contains formula A. Formula X contains formula B and also uses either the Next() or Previous() function. For a very simple example, use these three formulas:
Formula A: {table.field}
Formula B: {@A}
Formula X: Next ( {@B} )
Place these 3 formulas on the details band of the report, side by side. They should all return the same values, with X values one row ahead.
To see the bug, open Formula A and add or append a literal value to the existing field. If the field is a string you can use:
{table.field} & ‘AnyText’
If your field is a numeric or a date you can add a number to it:
{table.field} +10
When you save Formula A with this change you will immediately see Formula A values change in preview. You would expect to see the same change in Formula B, but because of this bug, Formula B will not change. You can make any change to Formula A that doesn’t cause a refresh/recalculate, and Formula B won’t change – at least not right away.
Now go into Formula B and make a simple change. For example, hit [Enter] to add a blank first line and save Formula B. Now Formula B displays the change and matches Formula A. The formula updates finally, but that update didn’t happen right away.
To see it again you can go back to Formula A and take out the added value. Again Formula B won’t change until you do something else to make the report recalculate or refresh.
Somehow, using the Previous()/Next() functions in this three-formula series confuses Crystal’s internal formula dependency. The fields always get back in sync after a refresh, or whenever the report recalculates all the formulas. But many minor formula changes don’t force the report to recalculate so things can be off for a while. I discovered this bug while trying to troubleshoot a very complex report. It took hours to isolate the behavior, confirm that the formulas were correct and identify Crystal as the source of the problem.
I also went back and tested CR v10 (2004) and CR v8.5 (2001) and found the exact same behavior, so this is not a new bug. I have never noticed it before, but it might explain some of the unexplained gremlin situations I have encountered over the years.
One solution to this is to add “WhilePrintingRecords” to either Formula A or Formula B, but that adds significant limitations to how these formulas can be used.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)