KB38349: How to create a filter prompt with start and dynamic end date in MicroStrategy 9.x.

In certain situations, it might be required to create a report that will prompt the users for a start date and expect the report to retrieve data for the next or previous X number of days. The example blow shows an option to achieve this without using ApplySimple functions

• Create a metric that is the max(Date) where Date is an attribute. Under Tools, go to Formatting - Values and choose the Date format to be used. Save and close this metric.
  
  

• Create a report (this report will be used as a filter) and place the Date attribute and the metric just created.
• Within that report, create a report filter - for the attribute "Date", qualify on ID, operator Greater than or equal to and click on the small button that has a question mark on it next to the text field. Once that opens, hit Finish and click OK for the entire filter.
  
  

• Within that report, go to Data - Report Data Options and click on Report Limit.  Click "Modify" and "Double-click here to add a qualification..." Choose the metric you created and under Parameters, Function - Rank, Operator - Top, Value - 60. Save and Close the report limit.
  
  

• Save and close the report.

Now this report can be used as a filter for other reports, metrics or prompts. The "Value" can be altered for the number of days needed between the start date and the end date.
  
 
 
38349