Database Decimal field changes in Crystal 1

[mgallot]

I have an SQL server database that contains a table with a field called Connection Id. The field is defined as decimal with 20 places. When I pull this number into a Crystal 9.2 report and browse the data, the last 2 numbers in the field are always zeros. This is the unique identifier so I cant properly separate the records. I dont have decimal places or rounding in the format, is there anything else I can look at?

SQL Server Data
31808077920473813
31808077920473806
31808077920473810

Crystal Browse Field Data
31808077920473800

All instances are displaying as 800 for the last 3 numbers.

Thanks.

 

[lbass]

You could try using a SQL expression to convert it to a string:

{fn convert(`table`.`number`,SQL_VARCHAR)}

This at least will display all numbers.

-LB

 

[mgallot]

I tried converting to text but I get the same result because by the time I get the number its already been changed to zeros. The number is being converted between the database and Crystal. Is there something in an ODBC setting or Crystal Report Options that could cause this?

 

[synapsevampire]

By the time you get the number I assume means that you incorrectly believe that Crystal is performing the translation. Why do you believe this? I've never heard of a SQL Expression being processed within Crystal...

Check the Database->Show SQL Query and you should see the formula you created demonstrated in what is being passed to the database in the appropriate syntax.

Instead try:

convert(varchar(50),table.field)

or try:

CAST(table.field as varchar(50))

-k

 

[mgallot]

I found a post with a solultion from LBASS that had this solution:

Go to format field->number->customize->rounding and choose the maximum number of decimals, which appears to be: .0000000001, and then go to decimals ->x+2 and enter:

numbervar dec := 9;
numbervar i;
numbervar j := dec + 1;
numbervar x;

for i := 1 to j do (
if val(right(totext(currentfieldvalue,j,""),i)) = 0 then
x := j - i);
x

This would format up to 9 decimals, removing zeros at the end. If you have numbers with more than 9 decimals, though, rounding will occur and the above formula will also start rounding to fewer decimals.

This appears to be the correct solution, but I am recieving a message on the currentfieldvalue that says 'too many arguments'.

 

[lbass]

Where are you placing the formula? This belongs in the format number->decimal formula area.

-LB

 

[synapsevampire]

How will 9 decimals help you with a 20 decimal field?

-k

 

[mgallot]

Yes thats where it is. In Number> Decimal formula. Its highlighting this part:

j,""

And says 'too many arguments have been given to this function'.

 

[lbass]

I'm not sure why, as I found my original formula and it's working fine. However, it is irrelevant, since this formula won't work with 20 decimals as I noted in my original post!

If the SQL expression isn't working, that implies that your field in the database is already rounding, as SV suggests.

-LB

 

[synapsevampire]

I don't know nor did I mean to imply that the field is rounding in the database, I think it is being truncated using LBs formula, so I tried to assist, but apparently my suggestion wasn't tried.

LBs use of Crystal's method for doing the conversion isn't explicitly stating lengths, so there's probably something amiss there.

In general I suggest that one NOT use Crystal's formulas which are converted to the databases SQL, use the actual database SQL in a SQL Expression.

-k

 

[caoamo]

lbass

I was having a boar of a time trying to format my decimal fields to drop insignificant trailing zero's but keeping the digits if they were significant.

Your formula worked like a charm. Here's a star! Thanks!