| Author |
Message |
Eric
Newbie
Joined: 31 Jul 2008
Location: Canada
Online Status: Offline
Posts: 7
|
  
Topic: Crystal is dropping the decimals on data read!!!
Posted: 31 Jul 2008 at 9:03am |
|
This is the first time I've ever seen this and this is a MAJOR problem. Im creating a report to schedule maintenance on aircraft and for some weird reason I cannot get the full data from my SQL table into Crystal.
We're currently using Firebird (SQL based) and I've been using CR for about 2 yrs now. This one table, I cannot get any number inside CR that has decimals. The field in the table is set to NUMERIC(9,2) and I've used two softwares to pull SQL commands and the data appears properly. I basically have all data that contains hours with one decimal. CR is not rounding, its totally dropping the decimal.
Dont need no BS about formating the field, it doesnt matter how I format. One decimal all the way to four, changed every possible way to set it up. Somehow, CR is not pulling the data the way it should. Some internal SQL command, anyway to change this?
|
|
E. Tremblay
|
 IP Logged |
|
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 31 Jul 2008 at 12:10pm |
What about tricking CR to read the data by using a SQL formula which converts it to a string. That way, your database server (which can read the number properly) will create a basic string that CR can read and it will already have the decimals in it.
Oh yeah - have you tried doing 'Verify Datasource'? Sometimes this can resolved data type issues.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
Eric
Newbie
Joined: 31 Jul 2008
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 31 Jul 2008 at 1:01pm |
|
Thanks for some ideas Brian. I've even talked to a business friends whos been using CR for 5 years for several companies using the same Firebird databases. He's never had troubles...
He said to try tricking it by inserting the field into a numbervar. Didnt work.
When I do verify database, it finds no errors.
I've also used a formula field while using "Cdbl" and still the same.
|
|
E. Tremblay
|
| IP Logged |
|
Eric
Newbie
Joined: 31 Jul 2008
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 31 Jul 2008 at 1:05pm |
|
I've also looked for other operators & functions...
tried "Truncate(XXXX,1)" no success
and tried "ToNumber(field)" no success
|
|
E. Tremblay
|
| IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 31 Jul 2008 at 5:09pm |
Are you doing these functions in SQL or CR? You need to do them in SQL so that it modifies the datatype prior to CR getting access to it. What about a ToString() funciton in SQL? That should help.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
Eric
Newbie
Joined: 31 Jul 2008
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 01 Aug 2008 at 9:02am |
|
No I as using basic CR formulas. I've actually never had to use SQL formulas in any of the hundreds of reports Ive made! Maybe I just got lucky, but mainly use sub-reports to get the data the way I want it.
I did look thru the help files and your "CR Pro XI" book for an example of SQL formulas, no luck. I know SQL very well but always get errors in syntax.
|
|
E. Tremblay
|
| IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 01 Aug 2008 at 12:01pm |
Yeah, I don't cover SQL formulas in the book (I leave that to the SQL experts). But I think that the problem won't be resolved with CR formulas because CR is reading the data wrong. You need to intercept the data PRIOR to CR seeing it and change its data type. The only way to do that is with a SQL formula.
If you look in my book, I have a few references to it. Pg 222 talks about creating new formulas, p447-449 shows the SQL Expressions that are the equivalant of Crystal Functions. From this chart, you'll see that the Convert() function converts data types. You can use this to convert the number to a string (but you'll have to Google it to get the exact syntax for you specific conversion task).
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
| IP Logged |
|
Eric
Newbie
Joined: 31 Jul 2008
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 03 Sep 2008 at 1:47pm |
|
I had to put this aside for a little while here.. but the SQL commands does work properly to bring the decimals back into the report... But a SQL command needs to be specific to a single row. How am I supose to have a SQL command pull the appropriate number for each row in CR?
I basically have this...
(
SELECT "HOURS_AC_WHEN_DUE"
FROM "AC_POLICIES"
)
And it will give that famous error multiple entries on singleton. How can I place a SQL command with...
WHERE "THIS FIELD" = to that specific row in CR, because each row is different.
|
|
E. Tremblay
|
| IP Logged |
|
|
|
