Number Field Displays Incorrectly

[mwake]

I'm using CR10, reporting against a SQL Server database. When I bring in the account ID field(number data type with 20 digits) into a report, I get 7.31e+018 when it should be a 20 digit number. I'm guessing the number is too big for crystal. How can I get it to appear correctly?

 

[rosemaryl]

Dear Mwake,

I believe Crystal is limited to 15 digits ... have you tried converting the number field to text?

In a formula do this:

ToText({Table.LongNumberField},'#')

I used the '#' argument to state no decimals, no formatting ... like thousands separators etc.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[mwake]

I tried your suggestion, but it didn't work. I'm wondering if the problem is caused by the bigint data type in SQL Server

 

[rosemaryl]

Dear Mwake,

Are you using OLEDB connection? If so, please see the following:

http://support.businessobjects.com/library/kbase/articles/c2017284.asp

It is indicated that a hot-fix corrects this issue. You could also change to using the SQL Server Driver via odbc connection for your report ... I have never had a problem with big init fields....

By the way, the 15 digit limitation includes decimal places .. The number 123456789012345.1234567890 will display as 123456789012345 and if I go to 10 digits I can then have 5 decimal places! 1234567890.12345

Hope the above helps,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[mwake]

I'm using an ODBC connection with the sql server driver, so I'm not sure what the problem is. A few numbers appear correctly but most of them appear 7e+18...

 

[mwake]

Apparently the problem is a datatype mismatch in the ODBC layer because of the bigint datatype from SQL Server. It was a known bug in v9, so I'm hoping there is a patch for v10