Displaying Web Intelligence Date Prompts using Built-in Functions

Using Web Intelligence functions to display date prompt values.

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!

Author: Dallas Marks

I am a business intelligence architect, author, and trainer. I help organizations harness the power of analytics, primarily with SAP BusinessObjects products. An active blogger, SAP Mentor and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting about business intelligence.

21 thoughts on “Displaying Web Intelligence Date Prompts using Built-in Functions”

  1. 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

  2. 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.

  3. 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.

  4. 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.

  5. 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 🙂

    1. 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

  6. 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 !!!!

  7. 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.!

Leave a Reply