Suppress trailing zeros in decimal values

Thursday 6 August 2015 @ 9:48 pm

Formula #15 on my formulas page has always allowed you to convert a number into text and have it not display zero decimals on the right end.  So if you had the following three numbers:


They would display like this:


But the original formula could only handle 6 decimals. This week I need it to go out further and so I decided to improve the formula.  Now it can handle any number of digits and you don’t have to convert the field to text if you don’t want to.  There are now two formulas involved.  The first just determines how many non-zero places need to be displayed for the field in each row:

//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 , ''))

You can then use this to display your original field with the appropriate number of digits. There are two different methods to use this formula:

1) Format the original field by selecting: [ Format > Field > Number tab > Customize button ]. Then next to the decimals property click the [X+2] condition button and enter the formula field created above {@Digits} and nothing else. 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 in any calculations.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server