User response prompt date not date data type in formula

[nalmond]

I have a report that takes a date from a user response prompt, on a BO report, and attempts to incorporate this date into a formula. The problem is that the date is not being seen as 'Date' data type. The associated Clarify field is of the Date data type.

I've tried ToDate, FormatDate, even trying SubStr and various combinations of all of them.

I have tried creating a variable 'Open at date' (all 3 var types tried) using =UserResponse ("Query 1 with CL_PR" , "Open date?"). The variable is then used in formulae. The formulae fail because the date isn't seen as date data type.

ToDate appears to be the function to use but it does not seem to be working successfully. Has anyone encountered and overcome this problem?

 

[skrandel]

You on the right track. You do need to convert the UserResponse to a date. All prompts are actually stored as a character datatype.

What is the syntax/problem with you to_date conversion.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[jayaramk]

I have tried the same with ToDate function and it works

Here we go :

=ToDate(UserResponse ("Query 1 with SITSSRSL" , "1. Enter Award From Date After (DD-MON-YYYY)") , "dd-mON-yyyy")

If it doesn't still work, make it as a detail dimension and try.

Hope this helps.
Jayaram

 

[nalmond]

Thanks for your help guys,

Unfortunately jayaramk's suggestion hasn't solved my problem.

I have tried creating a variable with:

=ToDate(UserResponse ("Query 1 with CL_PR" , "Open date?") , "dd-mmm-yyyy") I can make the variable a dimension it defaults to detail only.

when the variable is assigned to a column the cells report '#error'

 

[nalmond]

That should have been 'I can't make the variable a dimension it defaults to detail only.'

 

[jayaramk]

Hi Nalmond,

I just tried inserting the formula into the cell in my report and it works fine.

In the formula that I specified earlier, there was a small bug..viz, the date format should have been dd-mmm-yyyy, which you have already modified.

Could you send a copy of the report to me kannan_jayaraman@yahoo.com for me to have look.

Many Thanks,
Kannan J

 

[jayaramk]

Hi Nalmond,

What I believe is the problem is the Time part of the date value. Please truncate the time part when you get the date itself.

Please create a universe condtion as below

( Trunc(table_name.field_name>) >=@Prompt('1. Open Date (DD-MON-YYYY)','A',,MONO,FREE)

Use this condition in the report and try applying the todate conversion formula. It will surely work as it works for me.

Many Thanks
Kannan J

 

[Sridharan]

Time part can be there... But you need to accomodate that when you do a conversion... UserResponse will be a Detail but not a Dimension and you can actually take the Date Input and store in a Detail Variable... As said looking at the report might give some clues..

Sri

 

[nalmond]

Unfortunately I don't have any control over the Universe, small fish in big pond. Using substr, to only take the dd/mm/yyyy portion of the date, on the user response input as the variable is created isn't working.

 

[Sridharan]

No No No... YOu don't need access to Universe... You can accomplish at the report level itself... Need to have a look at the report...

Sri