Skip to Content

Date format in webi userresponse()

Hi guys

i have @prompt created in universe to capture the date entered by the user.. but the problem i am getting is that while displaying the date selected by the user is showing very weird thing.. when the date selected is 03/04/2014 then in webi it is showing in mm/dd/yyyy format and when date is selected 25/04/2014 then it shows in dd/mm/yyyy format which is correct..

i have tried using formatdate(userresponse(); "dd/MM/yyyy") ---- no success

tried making changes in preferences as well ---- no success

tried changing the format on universe level by right clicking the object-> object format->date/time and added new format dd/MM/yyyy --- no success

can you help me what needs to be done to achieve same..

regards

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Posted on Apr 30, 2014 at 04:51 PM

    formatdate(userresponse(); "dd/MM/yyyy") will throw you error because formatdate function takes a date as input parameter where userresponse returns a string.

    Also, userresponse syntax here is wrong. Correct syntax: =UserResponse("Prompt Text")

    What does the formula =UserResponse("Prompt Text") return ?

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 01, 2014 at 08:09 AM

    Hi leo dec,

    Check below formula

    As UserResponse return string value.

    A= UserResponse("Prompt Text")

    B=formatdate(todate(A;"MM/dd/yyyy");"dd/MM/yyyy")

    Regards,

    Anish

    Add a comment
    10|10000 characters needed characters exceeded


    • Hi

      Thanks for the reply... little clarification...

      created the object using @prompt in universe as date data type @Prompt('Enter the Week End Date ','D',,,)

      In webi report selected the universe objects in query and showing in report but in report when the day is between 01 and 12 then it is showing in MM/dd/yyyy format but when day selected after 13th then it shows in dd/MM/yyyy format which is correct...

      tried formatdate(object_nm, "dd/MM/yyyy") --- no success

      formatdate(todate(object_nm, "MM/dd/yyyy");"dd/MM/yyyy") -- it corrects the format when day selected between 01 and 12 but gives an error when day selected after 13

      regards

  • author's profile photo Former Member
    Former Member
    Posted on May 01, 2014 at 09:28 AM

    Leo,

    [Date] = UserResponse("Prompt Text")

    Try this variable,

    = formatdate( todate([Date];"dd/MM/yyyy");"MM/dd/yyyy")

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      SInce 2 years, I think you found a solution to your issue, I have the same.

      I resolved with this code : If a user change his "preference" it's work too using GetContentLocale function

      =If(GetContentLocale()= "en_US";ToDate(Left(UserResponse("@Prompt");Pos(UserResponse("@Prompt");" ")-1);"M/d/yyyy");ToDate(Left(UserResponse("@Prompt");Pos(UserResponse("@Prompt");" ")-1);"dd/MM/yyyy"))

      with @Prompt your Prompt.

      I hope this code can help other people...

  • author's profile photo Former Member
    Former Member
    Posted on May 01, 2014 at 11:22 AM

    Leo,

    Break it down into two steps.

    See what your UserResponse string looks like first, based on a few runs of the report.

    Once you have established which format it is (date first or month first) then build your ToDate formula accordingly - please note that you will need to manage your string format:

    dd = 03

    d = 3

    dd = 14

    d = 14

    MM = 12

    M = 12

    MM = 04

    M = 4

    Note that you may need to chop off the time component of your string if there is one. To do this, susbtring the user response object (let's call it Input Date) first as:

    =Substring([Input Date];1;Pos([Input Date];" "))

    So your final object of data type Date would be:

    =ToDate([Input Date];"whatever your date format is here")

    Or

    =ToDate(Substring([Input Date];1;Pos([Input Date];" "));"whatever your date format is here")

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 07, 2019 at 01:33 PM

    HI,

    Can you try this,

    instead of breaking date into a hug formula?

    try to understand what is the user-response give and output (user-response always return string )

    step 1 enter date in the prompt and check what is the user response variable has a value.

    if the value = 25/12/2018

    and we have to use below formula

    formatdate( todate(userresponse("date");"dd/MM/yyyy");"MM/dd/yyyy");

    if you get a value as = 12/25/2018

    then use below formula

    formatdate( todate(userresponse("date");"MM/dd/yyyy");"MM/dd/yyyy");

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.