MicroStrategy ONE
Filtering on a dynamic date based on a prompt answer
Steps to create a dynamic date based on a prompt answer
You can base the dynamic date on a prompt answer, rather than today's date. For example, you want to see daily revenue for the last month, starting at a particular date. If the specific date is 11/8/09, you want to see the daily revenue from 10/8/09 until 11/8/09, as shown in the report sample below:
To do this, create a prompt for a date. Use that date prompt in a filter that returns the dates between the date selected in the prompt and one month before the prompt answer. For instructions to re-create this example, see the Advanced Filters chapter of the Advanced Reporting Help.
Prerequisites
This procedure assumes that you are familiar with:
To create a dynamic date based on a prompt answer
-
-
Create the date prompt
-
-
On the File menu, point to New, and choose Prompt. The Prompt Generation Wizard opens.
-
On the Welcome page, select Value prompt, then click Next. The Value Prompt page opens.
-
Select Date and Time, then click Next. The General Information page opens.
-
Type a Title, such as Target Date.
-
Type text in the Instructions field, which is displayed when the prompt is run during report execution.
-
Select Prompt answer required.
-
Click Finish. The Save As dialog box opens.
-
Select the folder in which to save the new prompt, type Target Date as the Object Name, and click Save to return to MicroStrategy Developer.
-
Create the filter
-
-
On the File menu, point to New, and choose Filter. The Filter Editor opens.
-
If the New Filter dialog box opens, double-click Empty Filter to open the Filter Editor.
-
Drag the Day attribute from the Object Browser to the Filter definition pane. The Attribute Qualification pane opens.
-
Select ID from the Qualify On drop-down list.
-
Select Between from the Operator drop-down list.
-
Drag the Target Date prompt from the Object Browser to the text box for the first value, as shown below. The cursor will change to a + when it is in the correct position.
-
-
When you drop the prompt, the text box changes to Shortcut to: Target Date, and the drop-down list changes to Simple Prompt.
-
Select Custom from the second drop-down list.
-
Type the following in the second text box:
-
AddMonths(
-
-
Drag the Target Date prompt from the Object Browser to the second text box.
-
Type the following at the end of the second text box:
-
, -1)
-
-
The interface should now look like the following:
-
-
Click OK to return to the Filter Definition pane.
-
Save and close the filter, naming it 1 Month Ago from Prompted Date.
-
Create the report
-
-
Open a new report.
-
Add the Day attribute and the Revenue metric.
-
Drag the 1 Month Ago from Prompted Date filter from the Object Browser to the Report Filter pane.
-
Save the report, then execute it.
