• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1890
  • Last Modified:

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!!
0
HKBoyz
Asked:
HKBoyz
  • 9
  • 6
  • 5
10 Solutions
 
ast2550Commented:
Right Click - 'Format Field'
On the Number tab, click 'Customize'
What is the Rounding listed as?
0
 
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.
0
 
HKBoyzAuthor Commented:
Decimal: 1.00
Rounding: 0.01
Negative: -123
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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.
0
 
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.
0
 
HKBoyzAuthor Commented:
I am using an ODBC driver.
0
 
ast2550Commented:
Right Click on the field and select 'Browse Field Data'
Does it show any with decimals?
0
 
frodomanCommented:
Which ODBC driver are you using?
0
 
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.
0
 
HKBoyzAuthor Commented:
It doesn't show any decimal places when I Browse field data.
Microsoft ODBC for Oracle.
0
 
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.
0
 
HKBoyzAuthor Commented:
is there anyway I can convert it to a 2 decimal place number using a formula?
0
 
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?
0
 
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?
0
 
ast2550Commented:
Try another ODBC/OLE/Native connection...?  I don't think your data is getting to Crystal correctly.
0
 
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.  
0
 
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?
0
 
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.
0
 
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!
0
 
HKBoyzAuthor Commented:
Yup! That's all it counts!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now