BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Problem in regard with the 'ToDate' Function


 
Search this topic... | Search WebIntelligence XI... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence XI  Previous TopicPrint TopicNext Topic
Author Message
Zeenat
Senior Member
Senior Member



Joined: 11 Dec 2007

Posts: 58
Location: India


flag
PostPosted: Tue Dec 11, 2007 6:31 am 
Post subject: Problem in regard with the 'ToDate' Function

My problem is regarding Date Function in WebI XI R2,

1. The Report is Migrated from BO 6.5.4 to DeskI XI R2 and than converted the .rep report to .wqy report.
2. Custom SQL is used from the DeskI to WebI

UserResponse function is working fine, but as soon as I am using ToDate Function it gives #Error

=UserResponse([Query 1 with ABCD]; "From Trip Date:"


=ToDate(UserResponse([Query 1 with ABCD]; "From Trip Date:"); "MM/dd/yyyy")

The Complete format I required is, this same format is working for DeskI but for WebI it is not working :

=FormatDate(ToDate(UserResponse ([Query 1 with ABCD] ; "From Trip Date:") ; "MM/dd/yyyy h:mm:ss a") ; "Ddd, MM/dd/yyyy hh:mm a")

The same thing is working for few other reports but in 3 reports I am getting this kind of Problem, and all these 3 reports have Custom SQL required in that.

Does anybody have the idea about it.... icon_rolleyes.gif
Back to top
bdiss
Forum Member
Forum Member



Joined: 05 Dec 2007

Posts: 11
Location: Columbia, SC


flag
PostPosted: Tue Dec 11, 2007 10:47 am 
Post subject: Re: Problem in regard with the 'ToDate' Function

banghead.gif I understand your frustration and I ran into the same issue with Webi dates from user response. My team & I tried a number of different resolutions and the only one we could get to work was to use the Left function and the substring function to break apart the string. Webi adds a time stamp to dates when using the "user response", which seems to be the root cause of the issue.

We have a date object in our universe with values returning "mm/dd/yy", but when we use the user reponse we get a time stamp after the date, which isn't there in the db. We used the "IsDate" function in the report to figure out that Webi doesn't see the user response value as a date even though it is a date in the univese and it is a date when we pull the date back in the select clause. We also tried the "ToDate" and it failed for us as well. You may also want to try bringing the column back in your report and creating variables that return the max / min of the specific column. You will be bypassing the user reponse, but should still be able to get the data you are looking for.
Back to top
Zeenat
Senior Member
Senior Member



Joined: 11 Dec 2007

Posts: 58
Location: India


flag
PostPosted: Wed Dec 12, 2007 5:06 am 
Post subject: Re: Problem in regard with the 'ToDate' Function

i get u but not completed, it would be great if u can send me the code too, so that i can be able to understand it completely...
Back to top
bdiss
Forum Member
Forum Member



Joined: 05 Dec 2007

Posts: 11
Location: Columbia, SC


flag
PostPosted: Fri Feb 15, 2008 10:37 am 
Post subject: Re: Problem in regard with the 'ToDate' Function

Hi Zeenat,

Sorry about the delay. I haven't checked the site for awhile.

Here is the code that we use:
=Left(UserResponse([Data Provider Name];"Prompt Text");
Length(UserResponse([Data Provider Name];"Prompt Text"))-12)
yesnod.gif
It works great for us because it is dynamic. The time stamp (including spaces) is always 12 characters, whereas the length of the entire string can vary depending on whether the date has 2 digit month / day or 1 digit month / day.

Thanks,
Back to top
tech_sk8rboy
Principal Member
Principal Member



Joined: 10 Dec 2003

Posts: 145



PostPosted: Fri Feb 15, 2008 11:49 am 
Post subject: Re: Problem in regard with the 'ToDate' Function

or try this code:

ToDate(Trim (Substr( UserResponse("Enter Start Date:");1;Pos(Concatenation(Trim(UserResponse("Enter Start Date:"));" ");" ")));"M/d/yyyy")

thanks,
Back to top
ap.achary
Senior Member
Senior Member



Joined: 29 Nov 2006

Posts: 39



PostPosted: Fri Apr 09, 2010 11:22 am 
Post subject: Re: Problem in regard with the 'ToDate' Function

Thank you very much for the solution provided above as I was also having a similar problem with ToDate Conversions in WebI. This is best work-around.

Achary.
Back to top
JohnJustus
Forum Enthusiast
Forum Enthusiast



Joined: 25 Jun 2007

Posts: 1334
Location: Dallas, TX


flag
PostPosted: Fri Apr 09, 2010 1:18 pm 
Post subject: Re: Problem in regard with the 'ToDate' Function

What was the formula did you used? Can you please let me know.
Thanks.

_________________
"But they that wait upon the LORD shall renew their strength; they shall mount up with wings as eagles; they shall run and not be weary; and they shall walk, and not faint" (Isaiah 40:31)
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence XI  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0465 seconds using 17 queries. (SQL 0.0034 Parse 0.0347 Other 0.0083)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo