KB13002: Report filter is ignored when a conditional metric with a condition in the hierarchy is on the report template in MicroStrategy Developer 9.4.x-10.x

SYMPTOM:
When a conditional metric with a condition in the hierarchy is on the report template, the report filter is ignored in Strategy Developer 9.4.x-10.x.
 
STEPS TO REPRODUCE:
Follow the steps below to reproduce this issue in the Strategy Tutorial project:

• Create a filter on Year In List (2003) and name it 'Year 2003'.
• Create a metric called 2003 Revenue that has the following definition shown in the image below:
  
• Formula: Sum(Revenue)
• Level/Dimensionality: Report Level (Standard, Standard); Year (Absolute, Standard)
• Conditionality: Year 2003 (filter)

• Create a report with the attributes Customer Region and Month, the metric 2003 Revenue, and Month In List {January 2003} in the report filter, as shown below:
  

• Execute the report and notice the result set shows too many rows of data returned in the image below. This occurs no matter what settings are selected in the Advanced button in the Conditionality section of the metric.
  

CAUSE:
This issue occurs because the result set from the steps above only applies the conditionality of the metric in the SQL and yields too many results in the report because the report filter is ignored, as shown below:
 
Pass 1:
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZTPP0400PRMD000
from STATE_REGION_MNTH_SLS a11
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
where a12.YEAR_ID in (2003) <-- Metric Conditionality
group by a12.YEAR_ID,
a11.REGION_ID
 
Pass 2:
select pa11.REGION_ID REGION_ID,
a14.REGION_NAME REGION_NAME,
a13.MONTH_ID MONTH_ID,
a13.MONTH_DESC MONTH_DESC,
pa11.WJXBFS1 WJXBFS1
from #ZZTPP0400PRMD000 pa11
join LU_MONTH a13
on (pa11.YEAR_ID = a13.YEAR_ID)
join LU_REGION a14
on (pa11.REGION_ID = a14.REGION_ID)
Notice that the report filter is not in any of the SQL Passes.
 
WORKAROUND:
To workaround this issue, open the VLDB Settings Editor from Data -> VLDB Properties and select the 'Query Optimizations' folder. From there, select the Apply Filter section, and uncheck 'Use Default Inherited Value and select the second option to 'Apply filter to passes touching warehouse tables and last join pass, if it does a downward join from the temp table level to the template level,' as shown below:

 
The result set changes when this setting is applied, as shown below:

 
The SQL also changes to include both the conditionality of the metric in the first pass, and the report filter in the second pass, as shown below:
 
Pass 1:
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZTPP0300PUMD000
from STATE_REGION_MNTH_SLS a11
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
where a12.YEAR_ID in (2003) <-- Metric Conditionality
group by a12.YEAR_ID,
a11.REGION_ID
 
Pass 2:
select pa11.REGION_ID REGION_ID,
a14.REGION_NAME REGION_NAME,
a13.MONTH_ID MONTH_ID,
a13.MONTH_DESC MONTH_DESC,
pa11.WJXBFS1 WJXBFS1
from #ZZTPP0300PUMD000 pa11
join LU_MONTH a13
on (pa11.YEAR_ID = a13.YEAR_ID)
join LU_REGION a14
on (pa11.REGION_ID = a14.REGION_ID)
where a13.MONTH_ID in (200301) <-- Report Filter