Skip to Content

UserResponse Function - Options in Date Function

Hi experts,

i am trying to use the Bex query date prompts in Webi reports,

i have the prompt name as Approved date, it is showing the correct result if i used the below mentioned formula directly

Formula Used =UserResponse("Approved Date")

Received Output:12/29/2014 3:11:00 PM;6/1/2015 3:10:19 PM - (expected and also correct one)

i want to do a customization of dates here, , i am trying to get an output as
From Date: 29/12/2014 and ToDate : 1/6/2015

Tried formula

=FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A"); "dd/MM/yyyy") + " and " + FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy")

i am received the output as #ERROR

i am not sure how to use the two different dates here, date values are displayed as start value and end value, i here with attached the filed details

kindly share your inputs to solve this error,

i am using BO 4.0 SP10.

Regards,

Balaji

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 01, 2015 at 12:22 PM

    Use one d and one M in your ToDate conversions - MM expects 06, not 6 😊

    So you'd have

    =FormatDate(ToDate(UserResponse("Approved Date"); "M/d/yyyy hh:mm:ss A"); "d/M/yyyy") + " and " + FormatDate(ToDate(UserResponse("Approved Date"); "M/d/yyyy hh:mm:ss A");"d/M/yyyy")

    Add comment
    10|10000 characters needed characters exceeded

    • hi Anish,

      Thanks for your inputs, I have faced some parenthesis issue in Date2, then I have added one close braces at the end of the length, then I can able to get the expected output

      updated Date2 Formula

      Date2= formatdate(todate(substr([A];pos([A];";")+1;length([A]));"MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy")

      with the help of you and Tanveer my issue got resolved, just want to understand for performance wise issue will arise if we use more custom formula or variable, what kind of things we need to note and take care for performance perspective.

      Any personal experience/inputs then kindly share the same.

  • Jun 01, 2015 at 11:49 AM

    Hi,

    Use the below formula

    = "From Date: " + Substr([Formula Used];1;10) + " and To Date : " + Substr([Formula Used];23;30)

    Thanks,

    Tanveer

    Add comment
    10|10000 characters needed characters exceeded

    • hi Tanveer,

      Thanks for your continuous inputs for my request/issue, I can able to view the both single and double digit format.

      but in the single digit format currently it is showing as From Date/Todate: 1/6/2015 , how to show is as 01/06/2015 , I want to add 0 in the single digits date format. where I can do customization here.

      Regards,

      Balaji