Print Page | Close Window

Tonumber function XI

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7260
Printed Date: 01 May 2024 at 7:51pm


Topic: Tonumber function XI
Posted By: Thunter
Subject: Tonumber function XI
Date Posted: 05 Aug 2009 at 12:14pm
I am using Crystal XI professional and trying to compare a date that is kept in the database as a number (not the typical so many days from 1899).  It is actually the date formatted but stored as a number (e.g., 20090731).  I need to reference the nubmer to limit the amount of records I bring back and optomize the report.
 
I convert my date in the formuas as follows:
 
// Convert Date - convert date to text to compare against date stored as number in DB
stringvar D:= IIF(length(totext(day(currentdate-1)))=1,"0"& totext(day(currentdate-1)),totext(day(currentdate-1)));
stringvar M:= IIF(length(totext(month(currentdate-1)))=1,"0"& totext(month(currentdate-1)),totext (month(currentdate-1)));
totext(year(currentdate-1))+M+D
 
and then in the record section I compare this to the date by changing it to a number for the match.
 
tonumber( mailto:%7b@DateNumConversion%7d%29=%7bHistory.XDate - {@DateNumConversion})={History.XDate }
 
It works perfectly as designed when I run the report on my workstaiton with my Crystal report writer application.
 
The problem is when I schedule it in our Crystal Enterpris XI (Business Objects 11.5) it errors out with the follow error and appears that the standard "tonumber" function does not work or does not work the same in the enterprise version.
 
The error I get is.
 
Error in File D:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\Data\procSched\hhcrystal2.reportjobserver\~tmp210c5c882de05345.rpt: Error in formula <Record Selection>. 'tonumber({@DateNumConversion})={History.XDate} ' The string is non-numeric. Details: errorKind
 
I don't understand why both Crystal report version do not work the same.  I have tried approaching it by creating another forumua in the report to convert the text date to a number and then referencing the variable but it give the same error when it executes the variable that it gives in the record selection.
 
Any suggestions?
 

 


-------------
Terry W Hunter


Replies:
Posted By: DBlank
Date Posted: 05 Aug 2009 at 12:44pm
Not sure...
You want anything = 1 year ago today correct?
If so try this instead:
tonumber(totext(dateadd("yyyy",-1,currentdate),"yyyymmdd"))

Posted By: Thunter
Date Posted: 05 Aug 2009 at 12:51pm
Thanks, I will try the Dateadd function. But I don't understand why that would be treated different then the tonumber on the text date version I create.
 
What I am trying to do is get currentdate-1 to begin the report selection.  Because the date format in the database is an actual number (e.g., 20090731) I need to convert my currentdate-1 to that format.  I create the text variable first in the formula then the just use the "tonumber" on it in the selction criteria for comparisons.
 
What is really strange is that the function/approach works fine on my Crystal Report application on the workstation when I run it.  It only fails when I schedule it in Enterprise and it is run from the enterprise server.
 
Thanks again.


-------------
Terry W Hunter

Posted By: DBlank
Date Posted: 05 Aug 2009 at 12:58pm
Don't know why you were running into the issue on ES.
I was trying to limit the process and return an actual INT to try and correct the error.
You can also add that as part of your select statement without referencing a formula field...
You want anything that was on of after yesterdays date ...
{History.xdate} >= tonumber(totext(dateadd("d",-1,currentdate),"yyyymmdd"))

Posted By: Thunter
Date Posted: 05 Aug 2009 at 1:24pm

I used the formula you have but it doesn't return the format I need.

 

I used -  tonumber(totext(dateadd("d",-1,currentdate),"yyyymmdd"))

 

but what I get back from that is a number "good thing" but it is 20090004 "not good" since I am comparing against a date number in the database of 20090804.  Some how it is dropping the month.  I didn't go as far to load it onto my enteprise server to see if it works that same in both.

 

This is driving me crazy.  I still can't get past the problem that something works (a crystal function) on my workstation using the client Crystal XI applicaton loaded and then doesn't work when I schedule it on the Business Objects 11.5 enterprise application (same report).  It keeps pointing me to a problem on the Enterprise side and the application.

 

Thanks again.



-------------
Terry W Hunter

Posted By: DBlank
Date Posted: 05 Aug 2009 at 1:51pm
My bad....it should be using cap MM not mm ...
tonumber(totext(dateadd("d",-1,currentdate),"yyyyMMdd"))

Posted By: Thunter
Date Posted: 05 Aug 2009 at 2:02pm
IT WORKED!!!!
 
It was fine on my workstaiton and worked when I sheduled it.  I stil don't understand the difference between enterprise and the client when executing a "tonumber" function.
 
At least I can move on and get this one schedule.  My curiosity though will keep me looking for an explanation on their differences.
 
I am EXTREMELY greatful.


-------------
Terry W Hunter


Print Page | Close Window