phone: (540)338-0194
email: ken@kenhamady.com

´╗┐

 Crystal Reports formula examples

Convert Decimal Numbers to Text showing only the non-zero decimals:

Whenever you display a column of numbers you have to decide how many decimal places to display, and CR will round the rest up or down as needed.  When you convert to text using the ToText function you also have to decide how many decimals to display.  But lets say you have the following values.  In both cases all of the numbers will have the same number of decimals, and will use zeros to fill out to the right like this:  
1.0500
1.4050
1.2345
But say you want the values above to appear this way when converted to text.
1.05
1.405
1.2345
In other words, you don't want any rounding, and you don't want trailing zeros after the decimal. The following two formulas will do that. The first determines how many non-zero places need to be displayed:

//Formula @digits
Local NumberVar X := {Your.Field};
Local stringVar Y := StrReverse (Totext (X - Truncate (X) , 9 , ''));
if Val (Y) = 0
then 0
else Length(Totext ( Val (Y) , 0 , ''))

When you are ready to display your original field with the appropriate number of digits, there are two different methods:

1) Format {Your.Field} by selecting: [ Format > Field > NumberTab > Customize button ]. Then next to the decimals property you can click the [X+2] condition button and enter the formula field {@Digits} (all by itself). This will adjust the number of decimals displayed for each record, based on the number of digits needed.

2) Alternately you can use the first formula to create a separate formula field that converts {Your.Field} into text with the appropriate decimals:

Totext ( {Your.Field} , {@Digits}

This creates a separate string field.  You can still use the original numeric field in any calculations.