Displaying a prompt value on a BusinessObjects Web Intelligence report is pretty straightforward. Web Intelligence provides the UserResponse function to do this.
string UserResponse(object data_provider; string prompt_text; Index)
But notice that the data type returned by UserResponse() is always a string. What if you are trying to display a date? For example:
UserResponse(“My Date Prompt”)
The answer will be a string like this one:
3/7/2009 12:00:00 AM
So what to do if you desire a different format? Perhaps one like mm/dd/yyyy? Web Intelligence also provides a ToDate() function that converts a string to a date using a date format string.
=ToDate(UserResponse(“My Date Prompt”); “M/d/yyyy hh:mm:ss A”)
This variable will now display the following:
3/7/09
If you hover over the variable definition in the data tab of the report manager, you’ll see that this variable is indeed a date. Perfect. But what if I want a different format? Since the result is currently a date, the FormatDate function can be used to display it in the desired format.
FormatDate(ToDate(UserResponse(“My Date Prompt”); “M/d/yyyy hh:mm:ss A”); “MM/dd/yyyy”)
will be displayed as
03/07/2009
The last step may be to add the date prompt to a report variable that is displayed in the report’s title cell. The following formula will use the Web Intelligence document name concatenated with the report tab name (a new function in Web Intelligence XI 3.x – pretty cool) concatenated with some date prompts.
=DocumentName() + ” (” + ReportName() + “) between ” + FormatDate(ToDate(UserResponse(“My Begin Date Prompt”); “M/d/yyyy hh:mm:ss A”); “MM/dd/yyyy”)
+ ” and ” + FormatDate(ToDate(UserResponse(“My End Date Prompt”); “M/d/yyyy hh:mm:ss A”); “MM/dd/yyyy”)
which will be displayed as
Sales Report (Summary) between 01/01/2009 and 03/07/2009
The typing gets a bit lengthy and tedious. It may be preferable to create separate variables for the date prompts then reference them in another report title variable.
HINT: The date format string is where things get a bit tricky. Especially when the on-line help doesn’t include a fast link to a reference of valid date formats. Download the Building reports using the Java Report Panel document from the SAP Help Portal. A decent format string reference can be found on pages 259-262 of the XI 3.1 edition. Perhaps the reference will be just one click away in the next release of Webi?
Have fun building Web Intelligence reports!
good summary dallas :)
- josh
wow. very good. will go and test this now. had quite few problems before found this post
Very good summary!
I have a tricky, really related challenge for you:) I have the following situation in a report:
UserResponse("Start date") returns 7/31/2009, which correctly is the date from the prompt.
ToDate(UserResponse("Start date");"mm/dd/yyyy")returns 1/31/09. Why 1???? I get the same error if I try the format "m/dd/yyyy"…
FormatDate(ToDate(UserResponse("Start date");"mm/dd/yyyy");"mm/dd/yyyy") returns 07/31/2009.
I need the user prompt response as a date in the report to compare it to other dates in variables. Any idea why toDate gives me such a strange result???
-Mark
being a newbie to BO,found the post extremely helpful. Thank You
This does working for regional setting set to mm/dd/yyyy – not for others like germans dd.mm.yyyy.
UserResponse will return string in regional date format and can create interference between users.
BTW – it is diffferent in Java Editor and DHTML.
—To Mark
You should use ‘M’ not ‘m’…
It is case sensitive…
Caner, ‘M’ is for Month and “m” or “mm” is for minutes. So it depends on which value you want to display.