How to convert a string prompt into a date in WebIntelligence

 There are occasions where we need to prompt the user for a date. This can be done using the prompt functionality in the query panel.

We can display the value entered by the user at the prompt using the UserResponse function.

The UserResponse function is made up of two parts:

string UserResponse(string data_provider;string prompt)

The first part is the Data Provider that contains the prompt that you want to use. This is an optional field and is only required if your report contains multiple data providers. If you are only using one data provider, ignore this first part.

The second part is the EXACT text of your prompt string.

This needs to be a perfect match to your prompt. A way to ensure this is to copy and past the prompt string before you refresh the report.

When refreshed, the function will return the prompt value.

Note that the value returned from the UserResponse function is a string. Despite the value being shown as a date, the value is stored as a string. This means we are not able to perform date calculations on this field as it stands. The error message that will be shown is:

 

To overcome this, we expand the formula slightly and nest the UserResponse function inside a ToDate function.

The ToDate function is made up of two parts:

date ToDate(string input_string;string date_format)

The first part is the input string (in our case the UserResponse formula), and the second part is the format of the input. Note that the format has to match the input exactly.

We end up with the following formula:

 =ToDate(UserResponse(“Enter Date:”);”dd/mm/yyyy”)

At this stage, the user prompted date is converted to a date format and can be used in date calculations.

Bonus:

If you subsequently want to display the user prompted date in a specific date format, we would nest the formula further and include a FormatDate function:

string FormatDate(date date_to_format;string date_format)

This function takes two values. The first is the date you want to modify and the second part is the format string you want the date to appear in.

For example, if the user has entered the value 14/01/2012 at the prompt and we want to display this as Saturday 14 January 2012, we would create the following formula:

=FormatDate(ToDate(“Enter Date:”);”dd/mm/yyyy”);”Dddd dd Mmmm yyyy”)

In the above example, note that the output of the FormatDate function is a string, not a date. This is important to remember. If you need to perform calculations on a date, make sure it is in a date format (using ToDate).

Once you have finished your date calculations, you can then format the output in any way you like using FormatDate.

For a list of available date formats, take a look at this post. https://bobjblog.wordpress.com/2012/03/05/web-intelligence-date-formats

Note: If you receive #ERROR messages in your calculations, ensure that you are not using a date and time string. The examples above are based on dates in the format mm/dd/yyyy and not mm/dd/yyyy hh:mm:ss

Advertisements

6 Responses to “How to convert a string prompt into a date in WebIntelligence”

  1. Maruthi Says:

    Hello,
    In my case, if my user response function is writing “mm/dd/yyyy hh:mm:ss”…I would like to display only “DD/MM/YYYY” . Is it possible?

  2. Maruthi Says:

    I got the solution for this . This works fine.. Thank you.

    =FormatDate(ToDate(UserResponse([DataProvider Name];”Prompt Text”);”dd/MM/yyyy hh:mm:ss a”);”dd/MM/yyyy”)

  3. RG Says:

    UserResponse formats the date based on the user’s product locale, so for one user it might be dd/MM/yyyy hh:mm:ss a, and for another it would be MM/dd/yyyy hh:mm:ss a, how would you solve this issue ?

    • weldblog Says:

      Interesting question. I will have a think about that and open it up to our community.

    • Andrew Says:

      I would use something like this: If iserror(FormatDate(ToDate(UserResponse([DataProvider Name];”Prompt Text”);”dd/MM/yyyy hh:mm:ss a”);”dd/MM/yyyy”) Then FormatDate(ToDate(UserResponse([DataProvider Name];”Prompt Text”);”MM/dd/yyyy hh:mm:ss a”);”MM/dd/yyyy”) Else FormatDate(ToDate(UserResponse([DataProvider Name];”Prompt Text”);”dd/MM/yyyy hh:mm:ss a”);”dd/MM/yyyy”)

  4. "Bomber" Harris Says:

    Your first example isn’t quite correct. Entering the date 05/03/2014, and using your formula, I got 03/01/2014, i.e. January not March. if you change “=ToDate(UserResponse(“Enter Date:”);”dd/mm/yyyy”)” to “=ToDate(UserResponse(“Enter Date:”);”dd/MM/yyyy”)” (with capital letter M’s), it works and you get the correct month number.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: