UserResponse Function - Options in Date Function

[Balaji]

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

[Former Member · Accepted solution]

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")

[Balaji · Accepted solution]

hi Mark,

just I had taken this date as a sample, if required we can use two digit date format, I think single or multiple (M- Month and d-date) is not a big deal here.

even I tried based on your inputs but still same message "#ERROR"

regards,

Balaji

[Former Member · Accepted solution]

Hang on, I've seen the problem.

Your UserResponse string spits out one long string right?

What you'll need to do is create two variables that hold the from and to dates:

Create an initial UserResponse variable, Approved Dates as:

=UserResponse("Approved Date")

Now create variables Approved Date From and Approved Date To as:

Approved Date From:

substr([Approved Dates];1;Pos([Approved Dates];";")-1)

Approved Date To;

substr([Approved Dates];Pos([Approved Dates];";")+1;Len([Approved Dates])-(Pos([Approved Dates];";")+1))

Display those two variables and see if they return what you want to convert. You may need to tweak the +1 values to be +0 instead. Once they look like you want, use the following:

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

[Balaji · Accepted solution]

Hi Mark,

For Approved Date From: -

substr([Approved Dates];1;Pos([Approved Dates];";")-1)

  Error:  Invalid character ';' at position 50. (IES 10080) (WIS 10080)

For Approved Date To

substr([Approved Dates];Pos([Approved Dates];";")+1;Len([Approved Dates])-(Pos([Approved Dates];";")+1))

Error as :

Invalid character ';' at position 50. (IES 10080) (WIS 10080)

I can able to validate the formula in the variable editor without any issue, but when I clicked the ok button for submission, I am receiving this error, and the screen is closed,

not able to find the exact reason for this erro, Need your inputs

just noticed similar kind of requirement discussed in this thread, but I am not able to apply the given answer, just spend few mins on this thread and if you understood and related to this requirement/ issue then kindly guide me http://scn.sap.com/thread/3227081

Regards,

Balaji

[former_member182342 · Accepted solution]

Hi Balaji ,

Please use following formula

A=UserResponse("Approved Date")

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

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

Let me know it will resolve your issue or not.

Regards,

Anish Mahadik

[Balaji · Accepted solution]

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.

[tanveer1]

Hi,

Use the below formula

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

Thanks,

Tanveer

[Balaji]

hi Tanveer,

still same error

as per your inputs I tried the formula as

= "From Date: " + Substr(FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A"); "dd/MM/yyyy");1;10) + " and To Date : " + Substr(FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy");23;30)

Regards,

Balaji

[tanveer1]

Hi Balaji,

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

Here [Formula Used] = UserResponse("Approved Date")

Thanks,

Tanveer.

[Balaji]

hi Tanveer,

Based on your input I can able to get the From Date as expected, I think substring 1-10 digit exactly matching,  but for Todate it is coming along with time, tried the substring range even 24-26

Formula used: = "From Date: " + Substr(UserResponse("Approved Date");1;10) + " and To Date : " + Substr(UserResponse("Approved Date");24;26)

Output:

From Date:12/22/2014 and To Date:12/26/2014 11:10:19 AM

In this substr option I noticed that if i give single digit date as a input option, then partial time also included here,

for the same formula, for single digit date output as

From Date:6/1/2015 6 and To Date:/2015 6:36:03 PM

we need to check alternate option here.

Regards,

Balaji

[tanveer1]

You could use

= "From Date: " + Substr(UserResponse("Approved Date");1;10) + " and To Date : "  + Substr(Right("12/29/2014 3:11:00 PM;6/1/2015 3:10:19 PM";19);1;8)

[Former Member]

You can't use substrings Tanveer because the length of the dates can vary - when the 6/1/2015 becomes 6/10/2015, your formula would fail.

[tanveer1]

Hi Mark, Balaji,

Yes mark you are right here .

Alternatively you can use here =Pos([String];";") and Length( ) and use shifts in substring to get the desire result irrespective of date format

Ideally

Create a variable Date = UserResponse("Approved Date")

Pos( [Date];";")  should be 22

and Length( [Date]) should be 43

So create 2 variables POS and LEN as formula above

POS = Pos( [Date];";")

LEN = Length( [Date])

you can shift the substring starting and ending positions parameter based on length and position of ";"

=  "From Date: " + Substr([Date];1;10 - (22-[POS]) ) + " and To Date : "  + Substr(Right([Date];21- (43-[LEN]) + (22-[POS]) );1;10 - (43-[LEN])+(22-[POS]) )

This should work perfectly.

Thanks,

Tanveer.

[Balaji]

hi Tanveer,

I tried this given option, not showing the correct output, tried the single and double dated values,

it showing different results,

Formula:

= "From Date: " + Substr(UserResponse("Approved Date");1;10) + " and To Date : "  + Substr(Right(UserResponse("Approved Date");22);1;11)

correct output showing for double digit date format,

but if I change to single digit date for the same formula, some unwanted formats are included here, it is slowing as

From Date:6/1/2015 3 and To Date:PM;6/1/2015

so I think as per Mark, this substr is not suitable method here.

[Former Member]

Tanveer, the position and length simply cannot be hardcode as 22 and 43.

Try difference combinations of dates  - 1/12, 1/1, 12/12 all will produce different values.

[Balaji]

hi Tanveer,

I tried to create, but i am receiving the Invalid char error for ; , while creating the variable,

if possible kindly share the exact formula options which you referred,

Regards,

Balaji

[tanveer1]

Hi Balaji,

All you need to create is three variables Date of type Dimension and LEN, POS of type measures

Where is that you are getting the error ??

1. Date = UserResponse("Approved Date")

2. POS  =  pos([Date];";") this should be of  type measure

3. LEN  = length([Date]) this should also be of type measure

and then use the formula as suggested above

=  "From Date: " + Substr([Date];1;10 - (22-[POS]) ) + " and To Date : "  + Substr(Right([Date];21- (43-[LEN]) + (22-[POS]) );1;10 - (43-[LEN])+(22-[POS]) )

Let me know how this works

Mark,

Here we are using 22 and 43 as offsets not just hard coding , i have tried all possible cases and it is working perfectly.

Thanks,

Tanveer.

[Balaji]

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