Why Crystal report display digits after decimal places as "0"s?

A Crystal report gets data from an Oracle DB based on a  SQL query (in "Command"). There is a "number" field which stores values in negatives and 2 decimal places. When running the SQl alone it shows the number w/o any problem e.g.
-32.23.  However, in Crystal it displays as -32.00. Even changed the number format to -1230.00 it still shows the same thing.

HELP!!
HKBoyzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ast2550Commented:
Right Click - 'Format Field'
On the Number tab, click 'Customize'
What is the Rounding listed as?
frodomanCommented:
To expand on above comment, what you want to see on the Customize screen is:

  Rounding:  0.01

  Decimals:  1.00

This gives you 2 numbers after the decimal and rounds the number to 2 decimal places.  You can probably also simply select the "System Default Number Format" option instead of Customize.  Typically the settings I gave you above are the system defaults but that isn't always the case.
HKBoyzAuthor Commented:
Decimal: 1.00
Rounding: 0.01
Negative: -123
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

HKBoyzAuthor Commented:
I basically tried all the formatting combination and still get the same result. It alomst fee like the numbers have been set like that before they are being sent to display on the Preview screen.

However if I run that SQL alone in SQL Plus it shows the exact decimal digits every single time.
frodomanCommented:
By any chance are you using the Merant drivers?  If so, when you connect to an Oracle numeric field with no specified precision Crystal will treat it as an integer (thereby showing incorrect values).  Depending on your version of Crystal you may be able to obtain a patch but honestly I'd recommend switching to a different db driver - either a native connection or Oracle's ODBC or Microsoft's ODBC all seem to work fine for straight table pull reports.
HKBoyzAuthor Commented:
I am using an ODBC driver.
ast2550Commented:
Right Click on the field and select 'Browse Field Data'
Does it show any with decimals?
frodomanCommented:
Which ODBC driver are you using?
ast2550Commented:
Did you change the table definition after the report was created?  If so, you'd have to verify the data.

If it was originally an integer and now is a decimal, crystal wouldn't know it unless you tell it to examine the table definition, again...verify database.
HKBoyzAuthor Commented:
It doesn't show any decimal places when I Browse field data.
Microsoft ODBC for Oracle.
HKBoyzAuthor Commented:
I didn't change the table definition. It's a straight select from base table fields. I renamed the field in the select. However I tried not to rename it but still got the same results. The field is always a number field, with 2 decimal places stored in the DB.
HKBoyzAuthor Commented:
is there anyway I can convert it to a 2 decimal place number using a formula?
ast2550Commented:
Yeah sure.  Create a formula called TwoDecimals, Set it equal to {Table.Field} + .01

But, this isn't probably what you want.  If you cannot see the decimals throught the 'Browse Field Data' a formula can't help.

Create a new report from scratch, add one the table that has the decimal, does it show it correctly?
HKBoyzAuthor Commented:
ast2550
That was a good suggestion. I tried and the answer is no. It looks like by default Crystal trim off the decimal places. I am using Crystal 9. Is there anyway I can change that default settings?
ast2550Commented:
Try another ODBC/OLE/Native connection...?  I don't think your data is getting to Crystal correctly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frodomanCommented:
That's what I've been saying.  I don't usually have problems with the MS ODBC but you might want to check and make sure you have an updated version.  
ast2550Commented:
Maybe I was too hasty giving up...I have another thought.

Do you regularly create crystal reports using those Oracle Drivers?  Do you have any other report that has a decimal in it?  If this is your first report or your first one using a decimal, then I guess it could be global driver thing.  It just doesn't seem right.  These things just work.  Is there a pattern here or some context that'll help?
HKBoyzAuthor Commented:
Both of you are right. I regularly use the Oracle ODBC drivers but this is the first report I encountered such a problem. I change it to a CR driver and that got rid of the problem. Now it shows the decimal places. I wonder why though......

Anyway, thanks a lot.
frodomanCommented:
That's interesting - I use the CR driver for Oracle stored procedures because I've had ODBC problems but never with a SQL command...  But the important thing is that you have it working!
HKBoyzAuthor Commented:
Yup! That's all it counts!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.