For reports that contain dates as Filters that have been set to Prompt, it is helpful for the selected date range to appear within the report. While the Prompt Summary() function includes this information, along with other data that has been prompted, the format may not be appealing.
Instead, you can create Variables that will capture the dates entered by a User when refreshing the report. To do this, you'll need to use the same language as in the date prompts. If automated Filters (Active Filter, Enrolled Filter, Dismissed Filter) are being used, the prompt language is "Begin Date" and "End Date". We'll assume for this example that this is the case. Note that most dates retrieved via User prompt include a time stamp. The timestamp must be removed in order to use the resulting date in a calculated Formula. It does not need to be removed if the final date Variable will be used only for display.
Open the Variable Editor and call the Variable, "Begin Date". Insert the following Formula:
=ToDate(Left(UserResponse("Begin Date");Pos(UserResponse("Begin Date");" ")-1);"MM/dd/yyyy")
Now, make a duplicate of the Begin Date Variable, and rename it "End Date". Simply change the "Begin" in the Formula so that it now reads:
=ToDate(Left(UserResponse("End Date");Pos(UserResponse("End Date");" ")-1);"MM/dd/yyyy")
To understand this Formula, break it down from the innermost nesting:
Pos(UserResponse("End Date");" ") The purpose of this Formula segment is to find the position (POS) of the space in the date-timestamp that is returned with the prompt. The space occurs between the date and the time stamp.
Left(UserResponse("End Date"); ....-1) This nesting around the ..... which represents the first part above, takes the position that was found, moves it left by one space and then removes everything after that. In other words, it retains only the leftmost part of the resulting date from the space to the left.
=ToDate(.....;"MM/dd/yyyy") The outermost nesting of this Formula takes the final date without the timestamp and turns it into a date structure that Webi can understand.
Now you have a clean date that can be used in your report for display or for use in a Formula:
Display example: ="Date Range of Report " + [Begin Date] + " - " + [End Date]
Formula example: =DaysBetween([Date Taken];[End Date])