Print Page | Close Window

data type is incorrect

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Talk with the Author
Forum Discription: Ask Brian questions about his books and give him your comments. Like the book? Hate the book? Have suggestions? Let me know!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19708
Printed Date: 01 May 2024 at 10:05am


Topic: data type is incorrect
Posted By: ricky969
Subject: data type is incorrect
Date Posted: 25 Jun 2013 at 11:17am
verifying the DB does not update the datatype from string to datetime.

i updated the current datasource using both xml connection and oledb connection but the datatype for two of my fields does not change from string[22] to datetime.

if i create a new report i can use either datasources (xml or oledb) and the correct datatype gets displayed.

The problem is that i cannot start from a new report. Any idea?

Thanks
rick


Replies:
Posted By: hilfy
Date Posted: 26 Jun 2013 at 8:36am
What are you connecting to?  Tables or a stored proc?  What version of Crystal are you using?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ricky969
Date Posted: 26 Jun 2013 at 11:52am
fields were mapped to xsd file. i switched to stored proc but that did not help.

it's a very old report, developed using CR6 and then converted thru the years to CR9, VS2008, VS2010, VS2012.

Posted By: hilfy
Date Posted: 27 Jun 2013 at 3:37am
Is there something in particular you're trying to do with the fields that aren't changing to the correct type?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ricky969
Date Posted: 27 Jun 2013 at 1:13pm
There's an old function that i want to remove. DTSToDate is no longer supported and since the field comes in as dateTime i'd like to take full advantage of that. I'd like to be able to control date and time (including 12/24hr based on customer's settings) without hardcoding "mm/dd/yy" etc.

ToText(DTSToDate({XX_XXXX_RPT;1.EnteredDateTime}), 'MM/dd/yyyy')

Posted By: hilfy
Date Posted: 28 Jun 2013 at 3:19am
What format is the date in the string using?  Which version of Crystal are you using?
Have you looked at using either DateTime() instead of DTSToDate()?
 
-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ricky969
Date Posted: 28 Jun 2013 at 6:20am
string comes as '06/28/2013 23:00:01.999'. if I just use DateTime() in the formula below I get the error: "A string is required here)

if I wrap DateTime() into ToText(), I get error: Bad date-time format string

if {@stringName} = '' then
''
else
    ToText(DateTime({xx_xxxxx_RPT;1.EnteredDateTime}))

However, my main problem is not the function itself. i'd like to understand why the data type in the dataset is not reflecting datetime. i believe i mentioned that if i create a brand new report and use the same xsd file (or stored proc) as datasource, the data type comes in correctly.

Posted By: hilfy
Date Posted: 28 Jun 2013 at 8:43am
Have you tried Set Location?  For no reason I've ever been able to determine, sometimes it works for stored procedures and sometimes it doesn't.  Steps:
 
1.  Go to Database menu and select "Set Location".
2.  In the top pane, select the connection AND the stored procedure for the report.
3.  In the bottom pane, create a new connection (even though it will point to the same place as the old connection...) and select the stored procedure.
4.  Click on the Update button.
 
Also, DateTime() is not working because of the ".999" at the end of the string.  It expects whole numbers for Hours, Minutes, and Seconds.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ricky969
Date Posted: 28 Jun 2013 at 2:34pm
I tried the steps from 1 to 4 multiple times...it never picked the change in the two datetime fields. It looked like the value was cached somewhere. i also checked/unchecked "save data" but nothing worked.

as far as the DateTime() function, i'm aware that the problem was caused by the milliseconds but i cannot change that in the stored procedure. also, i prefer not to convert to string because i have users in different countries and i want them to be able to see the date and time based on their local settings rather than hardcoded to "US" or "Europe".

i really appreciate all your help. It's probably worth mentioning that this report has been revised 520+ times (based on the revision number). I'm pretty sure something corrupted the file. I was just hoping to figure out a way to reset the datatypes just in case it happens again on a report that would require hours of works if I have to re-write it from scratch.

Posted By: hilfy
Date Posted: 01 Jul 2013 at 3:15am
One of the "tricks" I've used on the rare occasions when I've had to rewrite a report is to have both the old report and the new one open in Crystal at the same time - usually each in their own Crystal window instead of having them both in tabs.  Aligning the windows makes it easy to make sure that the sections are the same height.  You can then copy and paste the objects from a section in the old report to the corresponding section in the new report, keeping the object spacing and format.  The only thing that I've had problems copying are lines - so I redraw those.  If you have any formulas that are only used inside other formulas, you can drag the formula onto the old report then cut it and paste into the new report, delete the object on the new report and the formula will still be in the formula list in the new report.  Then the only thing that you have to check are any section formulas that are used for things like suppression, new pages, etc.  (Formulas on objects will come over with the copied objects.)
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window