Rounding decimal down

[Former Member]

I have a field that displays in sql as

2.9999999999999

when i place the field in the report it displays as 3.00

i would like to display the actual number. i have tried the following ;

formatting the field every which way

round (field,0)

(if (remainder( 1) > 0 and remainder( 1) < 1) then

truncate( ) + 1 else )

any suggestions would be appreciated

[Former Member · Accepted solution]

Hi..

Go in Format Field and select Number tab and select the required decimal places and apply.

Thanks,

Sastry

[Former Member · Accepted solution]

i have down that every which way is possible,

it is auto rounding up the decimal, i need to see the actual value.

[Former Member · Accepted solution]

What do you get if you use this:

Truncate (field , 4)

You can set the decimals to any number.

[Former Member · Accepted solution]

And make sure you display more digits than what you put into the Truncate function.

[Former Member · Accepted solution]

that gave me 3.00 as well.

i wonder if because the field is displaying 2.9999999999999

that crystal will auto round up and i cant make it exact????

[Former Member · Accepted solution]

Did you use "Increase Decimals" in the tool bar to have it display 4 decimal places?

[Former Member · Accepted solution]

Since the Crystal reports depends on system regional settings go to control panel>regional settings>change the no.of decimals for number or currency depending on the data type of the field and give decimal places as 14 and in the report use system default format by right clicking on the field.

I think it works!

Raghavendra

[Former Member · Accepted solution]

thank you all, i have tried every suggestion and it still displays as 3.0000

i believe it may be the version(8.5) that may automatically display the value when the last digit is a 9... who knows but thanks again.

[Former Member · Accepted solution]

I am having the same problem in 8.5 and 11, I have three groups, Supplier, Grade, Grand Total, I have maual totals to work out the average of the grade, the same formula apllies to all three groups but it only rounds automatically in the Group Grade. Is there anything I am missing ?

Regards

[Former Member · Accepted solution]

i sounds like a version issue. I have another field that is 1.0837

and it displays correctly, the 2.9999 doesnt and i believe it has to auto round because of the 9 at the end.

what number is giving you grief?

[Former Member · Accepted solution]

That is what is strange

I have a formula in my detail section which is A\B (A is my nett weight and B my total cases, this formula shows the average wieght per case) which will give a decimal figure and this displays correct. I can't enter a summary of this field so I create a manual formula which is sum(A)\sum(B) the value then in the summary rounds to the closest 1, this for a sepcific group. I have a second group with the exact same formula sum(A)\sum(B) in a another group footer and it does not do it and it is displayed correctly. It does not matter what I do do in the one group it rounds it and in antoher it does not, it happens in Crystal 11 as well, if it is group specific I have no idea why, anyhting else you think I can try ?.

[Former Member · Accepted solution]

Got the solution to my problem. It was hell and not Crystals fault.

[Former Member · Accepted solution]

please share the secret!!!

[Former Member]

Hi Sharon,

I saw a problem like this a while back with my previous company. I don't remember what the values involved were, and I believe that it was actually a decimal number like 2.0199999 showing up as 2.02, or something to that effect, but it sounds similar.

I agonized over this thing, and as it turned out, the actual stored value of the field was in fact what Crystal was displaying to me. The value that I was getting out of the SQL query analyzer was actually subject to a rounding error.

Long shot, I know.

It could be a function of the drivers that you're using to access the database. They may nor recognize a value to that level of precision. Remember, that at the machine level all values are stored as binary, and fractional decimals don't exactly convert.

[Former Member]

The maximum decimal places allowed is 10 in format field. So I would say that convert the value to text before getting it on to the report and now use truncate function or left() to get the desired result and then convert in to decimal if you ant to use it in calculations.

You can use add command like this

select convert(varchar(14),field) as fieldname from table

Hope this Helps!

Raghavendra

[ken_hamady2]

Truncate instead of round?

[Former Member]

Try this

Right click on the field and go to format field and select the number format you want like 123.00 and click on customize and choose decimals from the drop down and click on OK

Regards,

Raghavendra