Results 1 to 14 of 14

121107, 16:56 #1Registered User
 Join Date
 Jun 2005
 Posts
 79
Unanswered: Format Decimal field, hide decimal when 0
Hi,
I have a decimal field with 4 decimal places. How do I format the field so that it suppresses the decimal places that are 0 and not display the dot if all 4 decimal places are 0. Basically trim the 0's from the right.
Examples:
123.1200 = 123.12
422.1000 = 422.1
531.0000 = 531
665.9876 = 665.9875
Thanks

121107, 17:35 #2www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,444
Provided Answers: 12

121107, 18:31 #3Registered User
 Join Date
 Jun 2005
 Posts
 79
I only want to remove the decimal when it is zero. What you suggested just changes the Decimal Places value under Number Format. I'm pretty sure that the only way to achieve this is using a formula, but I don't know how to do it.

121207, 09:34 #4www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,444
Provided Answers: 12Try this
Code:If Right(ToText({Customers.Total Sales}), 3) = ".00" Then ToText({Customers.Total Sales}, 0) Else ToText({Customers.Total Sales}, 2)
Convert to string  test to see if the 3 rightmost characters ar ".00"  if they are, convert result to text using 0 decimal places, otherwise use 2.

121207, 12:21 #5Registered User
 Join Date
 Jun 2005
 Posts
 79
ok but that still doesn't do what I'm looking for. Please look at the examples I gave:
123.1200 = 123.12
422.1000 = 422.1
531.0000 = 531
665.9876 = 665.9875

121207, 13:38 #6Registered User
 Join Date
 Jun 2005
 Posts
 79
I managed to figure out the formula.
NumberVar decimals;
decimals := ToNumber(StrReverse(Split(ToText(CurrentFieldValue , 4), ".")[2]));
if decimals = 0 then
ToText(CurrentFieldValue, 0)
else
ToText(CurrentFieldValue, Length(ToText(decimals, 0, "")))
 I convert the value to text with 4 decimal places, ToText() seems to automatically use 2 decimal places if you don't specify.
 I split the text at the decimal and get the decimal value which is stored at index 2
 The decimal string is reversed and converted to a number which removes any 0's that were at the front because of the reverse
 The if checks if the number is 0 which means that the decimal string was "0000", else I get the length of the decimal string which I use when formatting the value to text

090511, 08:15 #7Registered User
 Join Date
 Sep 2011
 Posts
 1
The Best and simple answer(Farzad)
just try the code below at format filed>Number>Customize> Decimals Code Part:
Code:if CurrentFieldValue=Int(CurrentFieldValue) then 0 else 2
Maxitem Software Corp.

020212, 10:44 #8Registered User
 Join Date
 Feb 2012
 Posts
 1
thanks holythirteen
holythirteen's solution works awesome !!!!! Thanks man

042512, 10:17 #9Registered User
 Join Date
 Apr 2012
 Posts
 1
Clumsy but works
In order to set the decimal places to show the significant places only
The logic:
Example: Suppose Price is 0.1023000
strreverse(totext({Price},7)) ==> 0003201.0
tonumber( 0003201.0 ) ==> 3201.0
Round(tonumber( 3201.0 )) ==> 3201
len(totext( 3201 ,0)) ==> 4 , so set decimal places to 4
The code:
If isnull({Price}) then 2
//else len(totext(Round(tonumber(strreverse(totext({Price },7,'')))),0))

042512, 12:28 #10Registered User
 Join Date
 Apr 2012
 Posts
 1
Chain of ifs to deal with decimal places
I cannot say this is a smart solution to the problem from my point of view, seems sillyk that Crystal forces you to implement such a nasty formula for something a format function should handle easily
This is the solution I am giving for a scenario where, at least, we always want to show the figure with a minimum of 2 decimal places and a max of 7. Put this in the Crystal report field  format  decimals formula
If {Price}=truncate({price},2) then 2 else
If {Price}=truncate({price},3) then 3 else
If {Price}=truncate({price},4) then 4 else
If {Price}=truncate({price},5) then 5 else
If {Price}=truncate({price},6) then 6 else 7
Some examples
3 will be shown as 3.00
3.1 will be shown as 3.10
3.11 will be shown as 3.11
3.111 will be shonw as 3.111
and so on.
if removing completely the decimal part of the figure (for example, price = 6) is required, then add the extra if
If {Price}=truncate({price},0) then 0 else
On which case 3 would be shown as 3
and if you want to show one decimal place too add the following
If {Price}=truncate({price},1) then 1 else
On which case 3.1 would be shown as 3.1
I would be very glad to hear there is a more simple solution for this problem!

040313, 03:17 #11Registered User
 Join Date
 Apr 2013
 Posts
 1
A Generic Solution
I managed to get a generic rounding formula to truncate trailing zero's.
One thing you have to do is set the rounding and decimals to 1 in the GUI (e.g. not 1.0, 1.00, and so on) and then implement the formula below against the rounding. I think that's because the final rounding number is the greater of what's configured in the GUI and what the formula result is.
Code:// If you don't specify the number of decimals, the number will be // converted to your default number of decimal places which was 2 // in my case, so not ideal when the number to convert may have // 3 or more dp stringVar number := ToText({the_table.the_numeric_field}, 8); // Strip trailing zero's while (right(number, 1) = "0") do number := left(number, len(number)  1); // Now work out how many decimal places are left and that is the // rounding amount len(number)  InStr(number, ".");
Last edited by JonnyRobot; 040413 at 02:13.

042813, 05:56 #12Registered User
 Join Date
 Apr 2013
 Posts
 1
Another Suggestion
if (CurrentFieldValue > 1 OR CurrentFieldValue < 1 )then
0
else
2

061913, 11:08 #13Registered User
 Join Date
 Jun 2013
 Posts
 1
I know this is late, but I tried the above solutions, and none fit the request, nor what I needed. In the end, I came up with this to be entered under a custom decimal style. (Format Object>Customize>Decimals Formula)
LEN(Replace(Right(ToText(CurrentFieldValue), Len(ToText(CurrentFieldValue))  InStr(ToText(CurrentFieldValue), ".")), "0", ""))
Seperate the text after the decimal, replace any 0s with "", and use the remaining length for the final number of decimals. Should function with any number of decimals.Last edited by Amaethon; 061913 at 11:12.

111913, 12:33 #14Registered User
 Join Date
 Nov 2013
 Posts
 1
How to ignore zero decimal place values in Crystal Reports  Stack Overflow
This is a more comprehensive solution