date(time) prompt and user response

building reports webintelligence
1

Hi all,

In the DWH and universe I have a date field, not datetime, but just a date field.
I am using this date field a lot in prompts.
The user can fill in the date and the report runs. So far so good.

Because the users wants to see the selected dates on the report, I made 2 variables with user response function in it.
To be more specific: '=ToDate(UserResponse(“Activity between (Start):”);“M/d/yyyy”) is 1 of them.

If I run the report, fill in the prompt, it works fine. But if I save the report and open it again, it gives an error in the variable field.
We discovered that if we add “hh:mm:ss A” to the variable and select a date in the prompt and add ‘12:00:00 AM’ manually behind the date, it will work perfectly.
But I (and ofcourse the users) do not want to add a time, because there is no time for this. Especially when the time is not selected automatically when you select a date from the calendar.
It looks like BO really want a time.
Does this sound familiar?

Best regards,
P. Weemer

pweemer :netherlands: (BOB member since 2009-07-08)

2

pweemer,
try creating object of univesre level which will remove the time stamp from the date field.
Then use the same object for prompt and try.

swap_l (BOB member since 2009-04-09)

3

Hav you tried the FormatDate function?

Mak 1 :uk: (BOB member since 2005-01-06)

4

Hi!

BO is making fun of me :hb:

Thank you both for thinking with me!

I already had predefined prompts in the universe, but I think that is cousing the problems.

Also FormatDate did not work completely (but I enclosed it in the formula just in case).

It is very strange. If I only have date in the ToDate it works fine till the moment I save the report. Next time I open the prompt is showing Datetime. After running I change my formula in datetime, everything works fine again after a few runs. Then I save the report. Next time I open the prompt is showing only date. After running I change the formula back in only date, etc, etc,

Work around: I do not use the predefined prompt from the universe and let the report run each time the customer opens the report.

But, if someone has another option to try, please let me know!

Regards!

pweemer :netherlands: (BOB member since 2009-07-08)

5

These two posts may give you a couple of ideas:-

https://bobj-board.org/t/164336
https://bobj-board.org/t/157831

I think you need to change the user response (string) to a date with the time,ToDate, then FormatDate to change into your required format?

Mak 1 :uk: (BOB member since 2005-01-06)

6

HI,

intead of using the UserResponse, Use the ReportFilter.

FormateDate(ReportFilter([Date]);“m/d/yyy”)

This would help you. It is report level. UserResponse takes the date value as the string. So to populate the date value in the report this would be the better way

sandeepmunagala :us: (BOB member since 2010-09-07)

7

He has a query level filter / prompt, i.e. a UserResponse, how is this going to help… :? ?

Mak 1 :uk: (BOB member since 2005-01-06)

8

Hi MaK,

ReportFilter will help.

He is looking to get the user selected date and populate it in the report. so here instead of userresponse if you use the reportfilter on a dimn then it will consider the user selected value and formating the date as i mentioned above.

sandeepmunagala :us: (BOB member since 2010-09-07)