You can get the response string to a prompt using Web Intelligence function UserReponse(). However the function returns string value which is formatted according to Web Intelligence preferred locale. To make handling of date strings independent on the locale, dynamic formats can be used.
Dynamic Formats in ToDate Function
To convert the date prompt string into date, ToDate function can be used.
If locale is French (Canada), the format should be “yyyy-MM-dd HH:mm:ss”.
= ToDate(UserResponse("Date:");"yyyy-MM-dd HH:mm:ss")
To make a report independent on the format (and correspondingly the locale), the dynamic formats can be used. The dynamic input formats are “INPUT_DATE_TIME”, “INPUT_DATE”.
Handling Date and DateTime Format
Before SP2 of BOE XI 3.1, the user could enter date in both date and datetime format, and conversion using specific format would not work correctly in all situations. The formula that accounts the both possible formats:
=If(IsError(ToDate(UserResponse("Date:");"INPUT_DATE")); ToDate(UserResponse("Date:");"INPUT_DATE_TIME"); ToDate(UserResponse("Date:");"INPUT_DATE"))
Dynamic Formats with FormatDate
The dynamic formats can be used in FormatDate() function.
BusinessObjects documentation does not list the available dynamic formats. Some of them can be found in dialog “Number Format”.