Results 1 to 14 of 14
  1. #1
    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There is abutton called "Remove Decimal" on the formatting toolbar.
    Looks like "0.00" with an arrow.
    George
    Home | Blog

  3. #3
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try this
    Code:
    If Right(ToText({Customers.Total Sales}), 3) = ".00" Then
      ToText({Customers.Total Sales}, 0)
    Else
      ToText({Customers.Total Sales}, 2)
    It's not the best way of doing this at alll, but right now I can't think of anything better.

    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.
    George
    Home | Blog

  5. #5
    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

  6. #6
    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

  7. #7
    Join Date
    Sep 2011
    Posts
    1

    Lightbulb 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
    Note: The number '2' is decimal place of yours and you can replace it by your own.

    Maxitem Software Corp.

  8. #8
    Join Date
    Feb 2012
    Posts
    1

    thanks holythirteen

    holythirteen's solution works awesome !!!!! Thanks man

  9. #9
    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))

  10. #10
    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!

  11. #11
    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; 04-04-13 at 02:13.

  12. #12
    Join Date
    Apr 2013
    Posts
    1

    Another Suggestion

    if (CurrentFieldValue > 1 OR CurrentFieldValue < -1 )then
    0
    else
    2

  13. #13
    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; 06-19-13 at 11:12.

  14. #14
    Join Date
    Nov 2013
    Posts
    1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •