How to get Start Date and End Date from User Responce in Webi

[former_member194503]

Hi Experts,

I am geting an out put of user responce(.....) in below given format

07/10/2015 12:30:43 AM;07/15/2015 12:30:43 AM

I want to display the start date and end date  as below given format

Date Range : 07/10/2015 To 07/15/2015

Can anyone suggest ???

Note:

I have used the below mentioned code but it is giving me only the Start Date. How do I get the End Date aswell ?

=FormatDate(ToDate(Trim (Substr( UserResponse("Enter To Date");1;Pos(Concatenation(Trim(UserResponse("Enter To Date"));" ");" ")));"MM/dd/yyyy");"MM/dd/yyyy")

[amitrathi239]

Hi,

try with these steps.replace "Period From/To (in Calendar Days)" text with your prompt text in the formulas.

From Date=If(Length(UserResponse("Period From/To (in Calendar Days)"))=45) Then Substr(UserResponse("Period From/To (in Calendar Days)");1;10)

To Date=If(Length(UserResponse("Period From/To (in Calendar Days)"))=45) Then Substr(UserResponse("Period From/To (in Calendar Days)");24;11)

Final variable=[From Date]+" To  " +  [To Date]

Amit

[former_member194503]

Hi Amit, Thanks for the replay

I was able to fix the issue with the below mentioned formulae. as you suggested I have calculated the length and got the result. but its too long.

I have a qus here instade of calculating the length as my user responce out put value comes with a ; in between  two Date times is there any way to find the ; and separate both the strings, I mean pos or substring

formula I have used

= "Date Range :"+If Length(UserResponse("Date Range Selection (with exclusion)"))=41 Then Substr(UserResponse("Date Range Selection (with exclusion)");1;9)+" To "+ Substr(UserResponse("Date Range Selection (with exclusion)");22;9) ElseIf Length(UserResponse("Date Range Selection (with exclusion)"))=42 Then Substr(UserResponse("Date Range Selection (with exclusion)");1;9)+" To "+ Substr(UserResponse("Date Range Selection (with exclusion)");22;10) ElseIf Length(UserResponse("Date Range Selection (with exclusion)"))=43 Then Substr(UserResponse("Date Range Selection (with exclusion)");1;9)+" To "+Substr(UserResponse("Date Range Selection (with exclusion)");22;10) ElseIf Length(UserResponse("Date Range Selection (with exclusion)"))=44 Then Substr(UserResponse("Date Range Selection (with exclusion)");1;10)+" To "+Substr(UserResponse("Date Range Selection (with exclusion)");22;10) ElseIf Length(UserResponse("Date Range Selection (with exclusion)"))=45 Then Substr(UserResponse("Date Range Selection (with exclusion)");1;10)+" To "+ Substr(UserResponse("Date Range Selection (with exclusion)");22;10)

[mhmohammed]

Hi Babisri,

Did you try using the formula I wrote? That should be an easier one. Give it a shot and let me know if it works/doesn't work.

When you are using a Prompt with Data Range, how come you get the Userresponse as String with a ";" in between?

Ok, assuming that you're getting the Userresponse as "07/10/2015 12:30:43 AM;07/15/2015 12:30:43 AM", lets call it [UR] and so, the formulas to separate them are:

Value 1 can be calculated as

=Substr([UR];1;Pos([UR];";")-1)

Value 2 can be calculated as

=Substr([UR];Pos([UR];";")+1;Length([UR])

Thanks,

Mahboob Mohammed

[former_member201488]

When you are using a Prompt with Data Range, how come you get the Userresponse as String with a ";" in between?

AFAIK, Userresponse() always returns a string value.

[former_member194503]

Hi Mohommad,

Thanks for the formula you have given.Even I got the purpose of the formula. its very simple and it should work. but i found a syntax error below attached the screenshot

[former_member194503]

Hi Neil,

I have attached both the user responce and its output. Kindly check the screnshot it has a ; in between two strings

[mhmohammed]

Hi Babisri,

I still have a problem with your formula, you say that the prompt is to get a Range (Start Date and End Date), but you use the same prompt text in both the places, how come?

First things first, please send us a screenshot of how your prompt screen looks like, as I did below.

I created a sample report and this is what the filter, prompt screen looks like.

And the formula I used is (you're missing the part I highlighted with yellow in snap and made bold in the text)

="Date Range: "+FormatDate(ToDate(UserResponse("Enter From Date");"");"MM/dd/yyyy")+" To "+FormatDate(ToDate(UserResponse("Enter To Date");"");"MM/dd/yyyy")

Hope that helps.

Thanks,

Mahboob Mohammed

[amitrathi239]

Hi,

try with this.

=Replace(Replace(userresponse("Date Range Selection (with exclusion)");";";"To ");"12:00:00 AM";" ")

Amit

[Former Member]

Hi,

I think that you can achieve the rdesired result breaking the formula in tweo components :

[user start date] = SubStr(UserResponse();1;Pos(UserResponse();";")-1)

[user end date] = SubStr(UserResponse();Pos(UserResponse();";")+1;Length(UserReponse())),

this will get start and end dates as strings with the timestamp. To remove the timestamp get the first 10 charactes of each one

[final user start date] = Left([user start date];10)

[final user end date] = Left([user end date];10)

Regards,

Rogerio

[former_member194503]

Thanks Amit, It works. Thanks a lot.

[former_member194503]

Thanks Rogerio, it works. Thanks a lot

[mhmohammed]

Hi Babisri,

The UserResponse() function looks for the Prompt text (e.g. "Enter Start Date" or "Enter End Date" etc) from the prompt screen. Depending on what text you use in UserResponse, it gives you the value that was selected in that prompt. So, in the formula you have entered the same text "Enter To Date:" in both the places, change the first text to "Enter From Date:" or whatever prompt text you're using, that'll do it.

Updated formula:

=FormatDate(ToDate(Trim (Substr( UserResponse("Enter From Date");1;Pos(Concatenation(Trim(UserResponse("Enter To Date"));" ");" ")));"MM/dd/yyyy");"MM/dd/yyyy")

Also, you don't have to use Trim, Substr etc, if the Date object's data type is Date time, use the below formula and you should get the desired result.

I just tested this and it works perfect for you.

="Date Range: "+FormatDate(ToDate(UserResponse("Enter From Date");"");"MM/dd/yyyy")+" To "+FormatDate(ToDate(UserResponse("Enter To Date");"");"MM/dd/yyyy")

Hope that helps,

Mahboob Mohammed

[antonette_oberholster]

Hallo

Maybe this post could be of use:

Regards

Antonette