KB43531: Incorrect SQL generated when there are multiple conditional metrics on template and metrics are set to Outer Join.

SYMPTOM:
 
In Strategy SQL Engine 9.x-10.x, incorrect SQL is generated when there are multiple conditional metrics on the template and metrics are set to Outer Join.
 
STEPS TO REPRODUCE:
Follow the steps below to reproduce the issue with Strategy Tutorial project:
 
select a13.QUARTER_ID  QUARTER_ID,
 a12.CATEGORY_ID  CATEGORY_ID,
 sum(a11.TOT_DOLLAR_SALES)  WJXBFS1
into ##ZZMD00
from SUBCATEG_MNTH_CTR_SLS a11
 join LU_SUBCATEG a12
   on  (a11.SUBCAT_ID = a12.SUBCAT_ID)
 join LU_MONTH a13
   on  (a11.MONTH_ID = a13.MONTH_ID)
 join LU_CALL_CTR a14
   on  (a11.CALL_CTR_ID = a14.CALL_CTR_ID)
where (a14.REGION_ID in (1)
 and a12.CATEGORY_ID in (1))
group by a13.QUARTER_ID,
 a12.CATEGORY_ID
select pa11.CATEGORY_ID  CATEGORY_ID,
 sum(ISNULL((pa11.WJXBFS1 * 0.1), 0))  WJXBFS1
into ##ZZMD01
from ##ZZMD00 pa11
group by pa11.CATEGORY_ID
select a13.QUARTER_ID  QUARTER_ID,
 a12.CATEGORY_ID  CATEGORY_ID,
 sum(a11.TOT_DOLLAR_SALES)  WJXBFS1
into ##ZZMD02
from SUBCATEG_MNTH_CTR_SLS a11
 join LU_SUBCATEG a12
   on  (a11.SUBCAT_ID = a12.SUBCAT_ID)
 join LU_MONTH a13
   on  (a11.MONTH_ID = a13.MONTH_ID)
 join LU_CALL_CTR a14
   on  (a11.CALL_CTR_ID = a14.CALL_CTR_ID)
where a14.REGION_ID in (7)
group by a13.QUARTER_ID,
 a12.CATEGORY_ID
select pa11.CATEGORY_ID  CATEGORY_ID,
 sum(ISNULL((pa12.WJXBFS1 * 0.2), 0))  WJXBFS1,
 sum((ISNULL(pa11.WJXBFS1, 0) + ISNULL(pa12.WJXBFS1, 0)))  WJXBFS2
into ##ZZMD03
from ##ZZMD00 pa11
 join ##ZZMD02 pa12
   on  (pa11.CATEGORY_ID = pa12.CATEGORY_ID and
 pa11.QUARTER_ID = pa12.QUARTER_ID)
group by pa11.CATEGORY_ID
select coalesce(pa11.CATEGORY_ID, pa12.CATEGORY_ID)  CATEGORY_ID,
 a13.CATEGORY_DESC  CATEGORY_DESC0,
 pa11.WJXBFS1  WJXBFS1,
 pa12.WJXBFS1  WJXBFS2,
 pa12.WJXBFS2  WJXBFS3
from ##ZZMD01 pa11
 full outer join ##ZZMD03 pa12
   on  (pa11.CATEGORY_ID = pa12.CATEGORY_ID)
 join LU_CATEGORY a13
   on  (coalesce(pa11.CATEGORY_ID, pa12.CATEGORY_ID) = a13.CATEGORY_ID)
Both M12 and M13 are calculated in SQL pass 4, where temp table ZZMD00(1 rows) is inner-joining ZZMD02(4 rows). 
 

• Create 2 filters as:  
  F01 = "Region in (NorthEast) AND Category in (Books)"
  F02 = "Region in (SouthWest)"
   
  
• Create 5 metrics as follow (Note: Set the Metric Join Type to be Outer Join):
  M01 = Sum(Revenue){~+, Quarter+}<F01> -- Here Revenue is a fact.
  M02 = Sum(Revenue){~+, Quarter+}<F02> -- Here Revenue is a fact.
  M11 = Sum(M01*0.1){~+}
  M12 = Sum(M02*0.2){~+}
  M13 = Sum(M01+M02){~+}
   
  
• Create report with {Category, M11, M12} on template, set Global Optimization to be level 0: no optimization. Execute R01 will get 4 rows correctly, as shown in the image below:
  

• Add M13 into the report at Step_1. Note that the report returns only 1 row regardless of whether the metrics are set to outer join, as shown in the image below:
  

•  
  This is because incorrect SQL is generated, as shown below: 

CAUSE:
 
This is a known issue with Strategy SQL Engine 9.x-10.x
 
ACTION:
 
Contact Strategy Technical Support for an update on the status of this issue
 
WORKAROUND:
 
The report returns 4 rows correctly by adding the Quarter attribute into the report at Step_2, as shown in the image below: