KB37538: How to include items of Consolidation in report result when no fact data is available in MicroStrategy Developer 9.x

A consolidation element does not show up in a report which consists of a consolidation and a metric if the element does not have corresponding data in fact tables. How to force the consolidation to show all the elements irrespective of whether there is fact data available for that element or not?
 
This issue is best explained through an example such as below.
 
A lookup table for attribute Region contains six attribute elements as shown below:
 
LU_Region:
 
Region_ID, Region_DESC
 
1,                  Region A
2,                  Region B
3,                  Region C
4,                  Region D
5,                  Region E
6,                  Region F
 
A fact table only contains the revenue values for three Regions as shown below. Meaning Region A, Region B and Region C have valid revenues while Region D, Region E and Region F only exist in the lookup table and do not exist in the fact table.
 
Region_Revenue:
 
Region_ID, Revenue
 
1,                  Value1
2,                  Value2
3,                  Value3
 
If users want to display all the attribute elements in the report, they have to first set the VLDB setting for "Preserve all lookup tables elements" to "Preserve lookup table elements join in to final pass result table based on template attributes with filter or without filter" under join category.
 
After enabling outer join for lookup table, if users create the consolidation elements as shown below:
 
C1 = Region A + Region B + Region D;
C2 = Region E + Region F;
C3 = Region C;
 
When the VLDB setting "AE null checking" under Analytical Engine category is set to False, the report will generate NULL value for C1 and C2 because if there is at least one attribute element doesn't exist in fact table, the sum of it is also NULL. The result is as shown below:
 
Consolidation   Revenue
C1                       NULL
C2                       NULL
C3                       Value3
 
When "AE null checking" is set to True, the report will generate zero values for C2 if all attribute elements don't exist in fact table. The report result is as shown below:
 
Consolidation  Revenue
C1                      Value1+Value2
C2                      0
C3                      Value3
 
 
As a conclusion, if the users want to force the consolidation to show all the elements irrespective of whether there is fact data available for that element or not, there are two workarounds as shown below:
  

1. Set "AE null checking" to False. Modify consolidation element to fall into only two categories:
   a) All attribute elements exist in fact table like C3; It is the sum of attribute elements which contains valid values in fact table.
   b) All attribute elements does not exist in fact table like C2; It is the sum of attribute elements which do not exist in fact table.
   In this case, the report will generate the correct real data for the first kind and get null for the second kind.
2. Set "AE null checking" to True. Add a threshold on Metric qualification with "Metric value=0", format it as replaced text (empty string or space etc.)
   Note: This only works if there is no valid zeros in the report for this metric which means the real value for C3 is not zero.