KB221464: The report SQL does not generate outer join due to one metric been mistaken as a nested aggregation metric in MicroStrategy Analytics Developer 9.x and 10.x

STEPS TO REPRODUCE:

1. In Tutorial Project, create a report with Month and Revenue;
2. Enable outer join by modifying the VLDB settings as below:
   
• Joins -> Full Outer Join Support -> Support
• Joins -> Join Type -> Join 92
• Joins -> Preserve all final pass result elements -> Preserve all elements of final pass result table with respect to lookup table but not relationship table
• Joins -> Preserve all lookup table elements -> Preserve lookup table elements joined to final pass result table based on template attributes without filter
3. Run the report in SQL view, it is inner join in the second pass;

CAUSE:
It is a SQL engine defect. The SQL engine tries to all ‘max’ function to the metric which makes it like a nested aggregation metric (but in fact, it is not). And since by default, the VLDB setting “Nested Aggregation Outer Join” is using the value “Do not perform outer join on nested aggregation”, outer join is not performed.
Sometimes the ‘max’ in the SQL cannot be seen because it’s controlled by another VLDB called “Distinct/Group by option (when no aggregation and not table key)”. Choose “use group by” and ‘max’ function would be shown.
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
WORKAROUND:
The workaround is to change some VLDB settings as following:
+ Joins -> Nested Aggregation Outer Join -> Do perform outer join on nested aggregation when all formulas have the same level