MicroStrategy ONE
Apply Filter Options
The Apply Filter property has three settings. The common element of all three settings is that report filters must be applied whenever a warehouse table is accessed. The settings are
- Apply filter only to passes touching warehouse tables (default): This is the default option. It applies the filter to only SQL passes that touch warehouse tables, but not to other passes. This option works in most situations.
- Apply filter to passes touching warehouse tables and last join pass, if it does a downward join from the temporary table level to the template level: The filter is applied in the final pass if it is a downward join. For example, you have Store, Region Sales, and Region Cost on the report, with the filter "store=1." The intermediate passes calculate the total sales and cost for Region 1 (to which Store 1 belongs). In the final pass, a downward join is done from the Region level to the Store level, using the relationship table LOOKUP_STORE. If the "store = 1" filter in this pass is not applied, stores that belong to Region 1 are included on the report. However, you usually expect to see only Store 1 when you use the filter "store=1." So, in this situation, you should choose this option to make sure the filter is applied in the final pass.
- Apply filter to passes touching warehouse tables and last join pass: The filter in the final pass is always applied, even though it is not a downward join. This option should be used for special types of data modeling. For example, you have Region, Store Sales, and Store Cost on the report, with the filter "Year=2002." This looks like a normal report and the final pass joins from Store to Region level. But the schema is abnormal: certain stores do not always belong to the same region, perhaps due to rezoning. For example, Store 1 belongs to Region 1 in 2002, and belongs to Region 2 in 2003. To solve this problem, put an additional column Year in LOOKUP_STORE so that you have the following data.
| Store | Region | Year |
|
1 |
1 |
2002 |
|
1 |
2 |
2003 |
|
... |
|
|
Apply the filter Year=2002 to your report. This filter must be applied in the final pass to find the correct store-region relationship, even though the final pass is a normal join instead of a downward join.
Interaction with Other VLDB Properties
Two other VLDB properties, Downward Outer Join Option and Preserve All Lookup Table Elements, have an option to apply the filter. If you choose those options, then the filter is applied accordingly, regardless of what the value of Apply Filter Option is.
Levels at Which You Can Set This
Database instance, report, and template