KB13918: How to create a subtotal that will calculate as a smart subtotal at lower levels of aggregation, but as a standard subtotal at the grand total level in MicroStrategy Developer 9.4.1 and 10.x.

SYMPTOM:
Situations may arise in which smart totaling may be desired at lower levels of aggregation, while the grand total should be calculated using standard totaling. This can be achieved using the custom subtotal feature set in Strategy Developer 9.4.1 and 10.x.
 
ACTION:
Consider a report with the following definition:
 

 
Revenue and Last Month's Revenue are the standard metrics from the Strategy Tutorial project. The two Min(Rev, Last Month Rev) metrics are defined differently to produce the same results (the lesser of the two preceding metrics) at row level, but different subtotals:
 

• Min(Rev, Last Month Rev) - smart = Case((Revenue > ), , Revenue) with the smart metric option enabled
• Min(Rev, Last Month Rev) - ApplySimple = ApplySimple("case when #0 > #1 then #1 else #0 end", , Revenue)

The smart metric applies the Case comparison to the results of the component metrics' subtotals, returning the lesser of the subtotal values (highlighted in red). The ApplySimple metric cannot use smart totaling because by definition it renders into a single expression in SQL. Its subtotal, in blue, is a simple sum of the row level metric values.
 
It may be desired to have the Month-level subtotal behave as a smart total, but substitute the standard subtotal at the grand total level. That is, in the third column, the grand total would be $1,754,076 instead of $2,113,828 as currently shown.
 
To produce this result, perform the following steps:
 

• Make a copy of the smart metric. Edit the copy and uncheck the smart metric option.
• Create a custom subtotal, defined as the sum at default level of the non-smart metric copy, as shown below:
  

• Still in the subtotal editor, go to the Tools menu and choose 'Apply to Metrics.' Select the original smart metric and enable it for use with this subtotal, as shown below:
  

•  
  NOTE: The subtotal may be enabled for the metric also by editing the metric and selecting the subtotal on the 'subtotal/aggregation' tab of the metric editor.
   
  
• In the report editor, open the subtotal dialog. Click the 'Advanced...' button.
• Enable the standard 'Total' subtotal, but only across the attribute levels desired (here, Month) as shown below:
  

• For the grand total, click the 'New...' button. Here, choose the normal 'Total' for any metrics that should subtotal normally, and choose the new custom subtotal for the smart metric, as shown below:
  

• Apply the new custom grand total at Grand Total level. If the custom total needs to be mixed in at other levels, the 'Across level' option may be used here as well, as shown below:
  

The report results should now show the smart subtotal at Month level, but the standard total for the grand total, as shown below: