KB19864: How are filter objects resolved in the SQL generation of a report in MicroStrategy Developer?

When a filter is defined as part of the report's definition, either as part of a report filter, metric conditionality or security filters, the SQL Engine applies certain rules when deciding if the filter will be applied to the SQL generated for the report, and if so, how to apply the filter correctly.
 
Some general rules can be identified when analyzing how the filter is applied in the SQL:
 

• If a filter object is based on an attribute related to a fact (i.e. fact table), the filter will be applied directly when calculating a metric object based on that fact (i.e. the filter condition will be placed in the SQL pass where the metric calculation is being performed).
  In this example, a report with Call Center and Employee along with the Units Received metric, and a filter condition based on Employee, shows how this rule is applied:
   
  

•  
  Since the filter conditionality is related to the fact (the fact used in the Units Received metric definition), the filter will be placed in the SQL pass of the metric:
   
  

• If a filter object is based on an attribute not related to a fact (i.e. fact table), the filter will be ignored when calculating a metric object based on that fact (i.e. the filter condition will not be placed in the SQL pass where the metric calculation is being performed).
  In this example, if the filter condition is changed and based on an unrelated attribute, the condition is not applied to the SQL pass:
   
  

• If a filter object is based on an attribute not related to a fact, but a related attribute to it is present on the report template, the SQL Engine will apply the filter to the appropriate SQL pass.
   
  However, an extra step must be performed in order to apply the filter. Since the level of the filter (i.e. the dimensionality of the filter or attribute on which the filter is based) is not related to the fact of the SQL pass, the level of the filter must be raised to the level of the output (i.e. the attribute present in the report template).
   
  The Strategy SQL Engine raises the level of the filter using a subquery that contains the output attribute as the dimensionality and filters the elements of these attributes corresponding to the related attribute's elements matching the filtering criteria.
   
  In this example, Employee is removed from the report template. The SQL Engine raises the filter based on Employee to the level of Call Center, since both attributes are related: