Displaying a prompt value on an SAP 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 Web Intelligence?
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.
hey, thanks a million for this! totally helped with my report!
Glad that the article was helpful! Thanks for reading, Alisa!
Hi there When I convert ToNumber(UserResponse(“Enter Year”)) it gives me 2,009 and not 2009. Which is creating a problem when i use this in OpenDocument. Any suggestion as to how to avoid 2C009.
Leka, one possibility is wrap with one more function to convert from number to string. Or apply object format in universe. Good luck!
I am unsuccesfful with my attempt. =ToDate(UserResponse(“Actual Goods Issue Date (Mandatory) From”); “MM/dd/yyyy”). I keep getting an #Error. I have tried a number of options without success. I am thinking that since this date prompt is being passed from a BEX query they I won’t be able to change the format. Have you seen anything like this.
Hi Dalls,
In my report , i want to display the dynamic dates as per user responce,
Eg-1, User select- 2014, we wnat diaplay the header like 2009,2010,2011,2012,2013,2014
Eg-2 User select -2016 we wnat diaplay the header like 2011,2012,2013,2014,2015,2016
Same as for querters also. i need to display for user select – 8 Quarters
all are under same tab,
i am still stuguling to find out the answer can u hlpe me on the same
Thanks in advacne 🙂
Hi, Narasimha.
Thanks for writing, but I cannot provide technical support. I recommend posting your question on the SAP Community Network in the appropriate forum or on the BusinessObjects Board (BOB).
Regards,
Dallas
Beautiful !! Changed my hard coded query filter dates to prompts and followed your date formatting instuctons. Worked great. Saved me hours of frustration. Thank you for posting !!!!
Don,
I’m glad it helped somebody solve a problem.
Regards,
Dallas
Perfect explanation for me to understand and get it done. Thank you!
Thanks for reading. Glad I could help!
Wow! Nearly 6 years later and this post is still helpful! Thanks so much!!!
Hello Experts, i have an issue related to the prompts…that is date prompts for my report entering as 004.2014(month.year) and while diplaying value in prompt i need to display as April 2014 (instead of 004.2014)….Could you plaese give a solution to this issue
thanks in advance.!
Manjunatha,
Date syntax varies depending on the database vendor. I would encourage you to post your question on the BusinessObjects Board or the Web Intelligence community on SCN.