Welcome to (andygiddings.com) (email)


Crystal Programming Tips

... how to control rounding and trailing zeros.
"Out of the box", Crystal Reports rounds and formats number fields using the number of decimal places specified in your Windows Control Panel’s Regional Settings area (typically two decimal places for standard US Windows installations). While you can select a different overall default from the Fields tab of the File -> Options dialog box, it will still be a set number of decimal places that will be used as a default when formatting number and currency fields.

This caused a problem for one of our clients who needed to display numeric values with the exact number of decimal places that the number actually was stored with. For example, if the actual field was 1, they wanted the value displayed as 1 instead of 1.00. And, if the field was .125, they wanted the value displayed that way instead of .13. The problem was, these various decimal precisions were contained in the same field and changed from record to record. Thusly, they couldn’t manually set the number of decimal places that would properly display the value’s exact precision.

To solve this problem, enter the following formula in the Conditional Format Formula Editor for both the Decimals and Rounding properties on the Number tab of the field’s Custom Style dialog box from the Format Editor:

If CurrentFieldValue = Int(CurrentFieldValue)
Then 0
Else
If
CurrentFieldValue * 10 = Int(CurrentFieldValue * 10)
Then 1
Else
If
CurrentFieldValue * 100 = Int(CurrentFieldValue * 100)
Then 2
Else
If
CurrentFieldValue * 1000 = Int(CurrentFieldValue * 1000)
Then 3
Else
If
CurrentFieldValue * 10000 = Int(CurrentFieldValue * 10000)
Then 4
Else
If
CurrentFieldValue * 100000 = Int(CurrentFieldValue * 100000)
Then 5
Else
If
CurrentFieldValue * 1000000 = Int(CurrentFieldValue * 1000000)
Then 6
Else
DefaultAttribute

This conditional formula will automatically round and display the precise number of decimal places, based on the actual decimal precision of the value displayed in the field. This formula should work properly for up to six decimal places, using the manual setting in the Format Editor for any finer precision.