KB37161: Custom Group filters are not applied to the first SQL pass which calculates metric qualification within a report in MicroStrategy Developer.

SYMPTOM:
 
Custom Group filters are not applied to the first SQL pass which calculates metric qualification within a report in Strategy Developer 10.x.
 
 
STEPS TO REPRODUCE:
 
     1.    Create a Custom Group called 'Geo Custom Group'
            --Create a static filter called 'Country' qualify on element ‘USA’ 
            --Create a static filter called 'Region' qualify on elements “Central, Mid-Atlantic” as shown below:     
            

 
 
 
     2.    Create a Custom Group called 'Product Custom Group'
            --Create a static filter called 'Category' qualify on element ‘Books’
            --Create a static filter called 'Subcategory' qualify on elements “Literature, Books-Malicious” as shown below:     
                                                                                                                                                                                                                                                                                                                    

 
     3.    Create a metric qualification filter called ‘Profit Qualification’
            --Set the definition to be “Profit Greater than or equal to 200”
            --Set the output level to be “Evaluate the set at the metric level” as shown below:    
 

 
     4.    Create a conditional metric called ‘Sum Profit’
            --Add ‘Profit Qualification’ created in step 3 as its condition as shown below:   
 
 

 
     5.    The metric ‘Profit’, remain default output dimensionality as shown below:     
 
 

 
     6.    Create report with 'Geo Custom Group', 'Product Custom Group', and 'month' as attribute and 'Sum Profit' as metric
            --Create an attribute filter qualify on three month in the report filter area as shown below:
 

 
     7.    Execute the report and verify the SQL, in the metric qualification pass for the conditional metric, the customer group’s filter is not applied, only report filter has been applied: 
 
Sql
insert into ZZMQ00
select a11. AS MONTH_ID
from a11
where a11. in (200701, 200702, 200703)
group by a11.
having sum((a11. - a11.)) >=  200.0 
 
Following actions do not solve the problem:

1. Adding Custom Group filters into report filter
2. Change evaluation order
3. Change custom group interaction with report filter option       

The custom group elements are unrelated but report filter will be merged and applied as a whole. When calculating the metric qualification, the SQL engine will generate a temp table which contains set of attributes(the output level), and the temp table cannot be applied to the Custom group as the SQL engine does not know which attributes should be included in the temp table.  
 
CAUSE:
 
The current behavior is the designed behavior of the Strategy SQL generation engine.
  
 
ACTION:
 
Strategy will continue to investigate and post updates as soon as they are available.
 
 
 
 
 
KB37161