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.

This procedure assumes that you are familiar with creating a prompt. For steps, see the Basic Reporting Help or the MicroStrategy Developer help.

To Create a Dynamic Date Based on a Prompt Answer

Create the Date Prompt

This prompt, which will be used on the filter, asks the user for one date.

1. In MicroStrategy Developer, choose File > New > Prompt.
2. On the Welcome page, select Value prompt. Click Next.
3. Select Date and Time. Click Next.
4. Enter a Title, such as Target Date.
5. Enter text in the Instructions field, which is displayed when the prompt is run during report execution.
6. Select Prompt answer required.
7. Click Finish. The Save As dialog box opens.
8. Select the folder in which to save the new prompt, enter Target Date as the Object Name, and click Save.

Create the Filter

This filter, which will be used on the report, returns the dates between the date selected in the prompt and one month before the prompt answer.

1. Choose File > New > Filter.

   If the New Filter dialog box is displayed, click the Empty Filter icon. If you do not want this dialog box to be shown in the future, select Don't show this dialog in the future. Click OK. For a full description of object templates, including a list of the objects that can use object templates, see Re-Using Objects Via Shortcuts and Object Templates .

2. Drag the Day attribute from the Object Browser to the Filter definition pane.
3. Select ID from the Qualify On drop-down list.
4. Select Between from the Operator drop-down list.
5. 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.

6. Select Custom from the second drop-down list.
7. Enter the following in the second text box:

   AddMonths(

8. Drag the Target Date prompt from the Object Browser to the second text box.
9. Type the following at the end of the second text box:

   , -1)

   The interface should now look like the following:

   

10. Click OK.
11. Save and close the filter, naming it 1 Month Ago from Prompted Date.

Create the Report

1. Choose File > New > Report.

   If the New Grid dialog box is displayed, click the Empty Report icon. If you do not want this dialog box to be shown in the future, select Don't show this dialog in the future. Click OK. For a full description of object templates, including a list of the objects that can use object templates, see Re-Using Objects Via Shortcuts and Object Templates .

2. Add the Day attribute and the Revenue metric.
3. Drag the 1 Month Ago from Prompted Date filter from the Object Browser to the Report Filter pane.
4. Save the report, then execute it.