KB16264: What are the differences in applying user versus original report filtering conditions in the Drill Map Editor in MicroStrategy Desktop

SYMPTOM:
When users create a custom drill path in the Drill Map Editor in Strategy Desktop, they have the following options to select from:
 

• Apply user filtering conditions
• Apply original report filtering conditions
  

This technical note explains the differences of using various combinations of these checkboxes and the results of enabling each option.
 
NOTE: In the following examples, the conditions below persist:
 

• The warehouse only contains data for years 2005, 2006 and 2007.
• The different drill maps are associated with only one time attribute respectively.
• The time hierarchy is as follows: Year > Quarter > Month > Date < MonthOfYear

Scenario 1
Uncheck 'Apply user filtering conditions'
Uncheck 'Apply original report filtering conditions'
 
Additional settings:
 

• Drill Map Associations: Date attribute
• Action Type: Drill to template
• Destination: a new template that has Year attribute and Revenue metric
• Report filter: None

ACTION:

• Run a report with the Date attribute on it.
• Right-click on the 01-01-05 attribute and drill to template. Note that on the resulting report, both the 2005 and 2006 years appear. Thus, there is no filter applied and the report is generated regardless of whether to choose to drill on a specific attribute. The report generates a list of all possible years on the subsequent report.
  

Here is the SQL generated by both reports in which there are no WHERE statements due to the lack of filters:
 

 
Scenario 2:
Check 'Apply user filtering conditions'
Uncheck 'Apply original report filtering conditions'
 
Additional settings:
 

• Drill Map Associations: MonthOfYear attribute
• Action Type: Drill to template
• Destination: a new template that has Year attribute and Revenue metric
• Report filter: None

ACTION:

• Run a report with the MonthOfYear attribute on it.
• Right-click on the January attribute and drill to template. Note that all years appear on the report but the report filter searches for the month January. Thus, by choosing to drill on a specific attribute, that attribute's conditions are applied to the subsequent report's filter condition. However, the subsequent report generates a list of all possible years.
  

Here is the SQL generated by both reports in which the second report has a WHERE statement reflecting the condition:
 

 
Scenario 3A:
Uncheck 'Apply user filtering conditions'
Check 'Apply original report filtering conditions'
 
Additional settings:
 

• Drill Map Associations: Quarter attribute
• Action Type: Drill to template
• Destination: a new template that has Year attribute and Revenue metric
• Report filter: Quarter = Q1 2005

ACTION:

• Run a report with the Quarter attribute on it and a filter of Q1 2005.
• Right-click on the Q1 2005 attribute and drill to template. Note that only the 2005 year appears on the report and the original filter from the initial report is carried over to the subsequent report. Thus, by choosing to drill on a specific attribute, that original attribute's filter is carried over to the subsequent report's filter condition
  

Here is the SQL generated by both reports in which the second report has a WHERE statement reflecting the condition:
 

 
Scenario 3B:
Uncheck 'Apply user filtering conditions'
Check 'Apply original report filtering conditions'
 
Additional Settings:
 

• Drill Map Associations: Quarter attribute
• Action Type: Drill to template
• Destination: a new template that has Year attribute and Revenue metric
• Report filter: None

ACTION:

• Run a report with the Quarter attribute on it and no filter in the original report.
• Right-click on the Q1 2005 attribute and drill to template. Note that both years appear on the report and since the original report did not have a filter, then there is no filter to apply to the subsequent report. Thus, by choosing to drill on a specific attribute, that attribute's conditions are not applied to the subsequent report's filter condition.
  

Here is the SQL generated by both reports in which there are no WHERE statements due to the lack of filters:
 

 
Scenario 4A:
Drill Map Editor settings:
Check 'Apply user filtering conditions'
Check 'Apply original report filtering conditions'
 
Additional settings:
 

• Drill Map Associations: Month attribute
• Action Type: Drill to template
• Destination: a new template that has Year attribute and Revenue metric
• Report filter: None

ACTION:

• Run a report with the Month attribute on it and no filter in the original report.
• Right click on the Jan 05 attribute and drill to template. Note only the year 2005 appears on the new report and since the original report did not have a filter, then there is no filter to carry over to the subsequent report. However, there is a newly created filter in the subsequent report because the 'Apply user filtering conditions' option is checked.
  

Here is the SQL generated by both reports in which the second report has a WHERE statement reflecting the condition:
 

 
Scenario 4B:
Check 'Apply user filtering conditions'
Check 'Apply original report filtering conditions'
 
Additional settings:
 

• Drill Map Associations: Month attribute
• Action Type: Drill to template
• Destination: a new template that has Year attribute and Revenue metric
• Report filter: Quarter = Q1 2005

ACTION:

• Run a report with the Month and Quarter attribute on it and add a filter for Quarter = Q1 2005 in the original report.
• Right-click on the Mar 05 month attribute and drill to template. Note that only the 2005 year appears on the report and the original report's filter is carried over to the subsequent report. Thus, by choosing to drill on a specific attribute, that attribute's conditions are applied to the subsequent report's filter condition and also the original report's filtering conditions are carried over to the subsequent report.
  

Here is the SQL generated by both reports in which the second report has a WHERE statement reflecting the condition: