SYMPTOM:
A filter is constructed using OR to connect a pair of qualifications, as in the following report definition:
The template metric's dimensionality is configured to ignore filtering on Category and related attributes as shown below:
When SQL is generated for this report, the Category qualification correctly does not appear. However, the Year qualification is also missing, as shown below:
select a11.YEAR_ID YEAR_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from YR_CATEGORY_SLS a11
group by a11.YEAR_ID
CAUSE:
This behavior is by design, and is the result of Strategy SQL Generation Engine's method of handling ignored filter levels.
When two qualifications are conjoined by OR, the result is the union of the results of the individual qualifications, as shown below. All data in colored regions will be preserved in the end result; areas in white are excluded.
Ignoring the Category filter means that the result should include data for all categories, OR those data in Year 2004. When all category data are included, the result will be all of the data in the fact table. In the illustration below, now the entire data range is shaded.
A qualification whose conjunction is OR cannot remove data from the result set; it can only add data. Since the results of the ignored Category filter include all of the data in the table, the Year filter will, logically, have no effect on the end result.
The Strategy SQL Generation Engine handles ignored filters by marking the ignored branch of the filter expression as being always TRUE, corresponding to the requirement to include all data related to that branch. In that case, the following identity from Boolean logic applies:
P OR True = True
Considering the expression P OR Q, if Q is always true, then P OR Q is also always true. Therefore both P and Q may be omitted from the filter in the generated SQL.
If the generated SQL included the Year qualification, the meaning of the filter would change from OR to AND. Since this is not an accurate reflection of the report filter design, Strategy SQL Generation Engine 9.x does not perform that operation.
ACTION:
The filter design in the report must be changed to take into account these facts of logic.
One option is to move certain qualifications into metric conditionalities so that the filter designs for the various metrics are different according to the filtering settings in the metric's dimensionality (Standard or Ignore).
This behavior also means that, as of this writing, OR is not a viable solution for security filters across multiple hierarchies.
