Field size and significant digits

[xp8103]

I have a field defined in my database as a numeric with 15 places to the right of the decimal. I can only define 10 in the field when it shows in my report and I am using the field to sort on. Without being able to see all of the places, I cannot determine if my sort is correct.

Ideas?

 

[MalcolmW]

Crystal has a maximum precision of 15 digits, so it should be OK, as long as you don't have any digits to the left of the decimal. However, to check your result, which sounds very reasonable considering Crystal only will display 10 decimal places, you can multiply your field by 100,000. You could display this result as a string using a formula, such as
'0.' + ToText(100000*{YourField}, 10, '', '')
If you wish, you could group on this formula instead of using the field.
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[xp8103]

Malcom,
That is the unfortunate part. There are 3 digits to the left. The ultra precision is to allow records to be inserted in between.
Do you fee that that will effect the sorting and grouping (given that I will have 3 digits to the left)?

 

[MalcolmW]

The problem is that Crystal treats converts numbers to Binary Coded Decimal - basically a form of scientific notation. So the number 123.987654321098765 gets converted to internally to .123987654321099 x 10^3.
This obviously won't work for you - not only do you lose precision, but the rounding of the last digit is not desirable.
You will have to convert this number to a string at the database level (using a view, stored procedure, etc) - just be sure that for sorting purposes, you have leading zero(s) for any fields that have less than 3 significant digits to the left of the decimal.
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[xp8103]

Oh no! Creating an sp in SQL server for this result is exactly what I am trying to avoid!

 

[MalcolmW]

Ah, it's easy. The Books On Line have some great examples, or you can just create your SELECT statement and email it to your DBA and ask him/her to put it in a stored procedure or view. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[xp8103]

HAHAHHAHAHAHA Not laughing at you Malcolm, laughing at myself.
It's not the lack of desire or ability to create stored procedures that I have, it is the desire to not use one to create the data for this report.
The problem is, I will be needing to use this data in various forms and creating an sp that contains everything for a report and then having to create another when new specs for another report come up is what I am trying to avoid. Sorry about the confusion, I am not trying to avoid sp's, just trying to find a way to use this data without creating them.

Nik Rende
Maine State Legislature

 

[xp8103]

Excellent! Was able to create a view including the convert statement!

Thanks for the help Malcolm!

Nik Rende
Maine State Legislature