KB20040: Custom subtotals defined using an auxiliary metric are not displayed when the auxiliary metric is not present in the report in MicroStrategy Developer 9.x-10.x

SYMPTOM
When a custom subtotal is defined in terms of a metric that is not present in the grid, the subtotal field will appear blank in Strategy Developer 9.x-10.x as shown in the image below
 

 
STEPS TO REPRODUCE
Follow the next steps in Strategy Tutorial to reproduce this issue
 

• Create a Metric 'M4' defined as 'Cost / Profit' as shown in the image below
  

• Create a custom subtotal 'Sum(M4)'defined as the Sum(). The definition of the subtotal is shown in the following image:
  

• Create a new metric 'M3' defined as the Sum(Revenue) as shown in the image below
  

• From the 'Subtotals/Aggregation' tab, add the subtotal Sum(M4) as one of the available subtotals for the Metric as well as the Total subtotal function and the Default Dynamic Aggregation Function as shown below
  

• Create a report with the attribute 'Quarter' and Metrics 'M3' and 'M4'. After the report runs, add grand totals to the report. Notice that the subtotals work correctly when the two metrics are on the template. However; when ' M4' is removed from the report, the total in 'M3' disappears.

 
CAUSE
The Analytical Engine is in charge of calculating the totals in the report after the data is retrieved from the warehouse. When M4 is removed from the template, the Analytical Engine does not have the required information to calculate the subtotal and therefore they are not displayed on the report. The subtotals are calculated at the view level. The auxiliary metrics need to be in the actual template of the report; it is not enough to have them in the Report Objects Window. This behavior is by design.
 
ACTION
Whenever using subtotals based on auxiliary metrics, these auxiliary metrics need to be on the report .
 
WORKAROUND
There are two possible workarounds for this issue as described below:
 

• The auxiliary metrics used in the subtotal calculation can be hidden from view using the method described in:
   
  
• KB5700-752-1316- How to hide a metric on a report without Strategy OLAP Services privileges
   
  
• Based on the example above, the metric using custom subtotals can be modified following the next steps
   
  
• Modify the definition of metric 'M3' to contain M4 without changing the values as shown below:
  Sum(Revenue) + M4 - M4
  

• Make sure that the metric has the "Allow Smart Metric" option checked as shown below
   
  

• Remove the Metric M4 from the Template and place it in the Report Objects Window. The custom subtotal will be preserved even when the component metric is not in the actual template