MicroStrategy and Datastrong Host "STEM for Her" Race at Upcoming MicroStrategy World™ User Conference

Symptom

Dynamic sorting is useful when you sort your report based on a metric like monthly profit. For instance, let's say you have a report with a Subcategory attribute in your rows, as well as a Month attribute and Profit metric in your columns. 

Ideally you'd sort this report  by the latest month's profits so that, in this case, March 2014 would automatically be your sort criteria.  
However because the data volume would grow when April 2014 was added, you'd have to manually change your sort criteria to Apr 2014. Without this step your data would still be sorted by Profit for Mar 2014.

This issue occurs because the dynamical criteria could not be specified in Strategy. When any detailed element is chosen and saved, the criteria is fixed.

Solution

Strategy allows users to select Subtotal as sort criteria. With this, you can create a metric whose Subtotal is equal to Profit for the latest month and use that metric to sort the grid dynamically. Follow along with our Tutorial Project to learn how to select Subtotal as sort criteria (this sample Tutorial DWH is hosted on an Oracle Database). 
Note

1. If a filter/prompt has been used to choose the time range, be sure to add this filter/prompt to both reports created in step 2 and the final step.
2. Avoid testing with Access data. Setting a Metric to outer join will not work this way. 

• Create a metric named MaxMonth with the forumla Max(Month@ID).
  

• Create a report named "Max Month Report". Include a Month attribute and the MaxMonth metric from step 1. This report will always fetch the latest month. 
  

• Create a filter called Filter to Fetch the Latest Month and drag Max Month Report to the filter definition field.
  

• Create a metric and name it Profit for Max Month, which is the Sum(Profit_Fact) with Filter to Fetch the Latest Month as a filter condition. 
  

• Create a report for dynamic sort with Subcategory and Month as your attributes, as well as Profit and Profit for Max Month as your metrics.
• Set Profit to Outer join in Report Data Options.
  

• Enable Subtotal as Total at the Month level.
  

• Your report will display Total under Profit for max month which is equal to profits for the latest month:
  
  

• Sort the grid with the Profit for max month metric and set the Criteria to Total.
  

• Hide the Profit for max month metric by setting its width to 0 in the Grid Options menu. 
  

Now when new data is added, it will automatically be sorted by the latest Month.