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:
At this stage, the user prompted date is converted to a date format and can be used in date calculations.
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