SYMPTOM
A user designs a report such as the following. The intent is to return the top three ranking Customer Regions, along with the Northeast region regardless of its revenue.
However, when the report is executed, only the Northeast Customer Region's revenue is displayed. It is as if the ranking set qualification were removed from the report filter.
CAUSE
Metric qualifications have an advanced property labeled "When resolving this qualification in a report, other qualifications will be taken into consideration and merged."
This option is enabled by default, meaning that the metric used for filtering will calculate over data that have been pre-restricted according to other qualifications in the same filter. In this example, the calculation of revenue rankings for Customer Regions takes place over the Northeast region only. Consequently, the filtering set includes only Northeast and the final result cannot go outside of this region.
select a13.CUST_REGION_ID CUST_REGION_ID,
rank () over ( order by sum(a11.TOT_DOLLAR_SALES) desc) WJXBFS1
into #ZZAM00
from CITY_SUBCATEG_SLS a11
join LU_CUST_CITY a12
on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)
join LU_CUST_STATE a13
on (a12.CUST_STATE_ID = a13.CUST_STATE_ID)
where a13.CUST_REGION_ID in (1)
group by a13.CUST_REGION_ID
select pa11.CUST_REGION_ID CUST_REGION_ID
into #ZZMQ01
from #ZZAM00 pa11
where (pa11.WJXBFS1 <= 3.0)
select a13.CUST_REGION_ID CUST_REGION_ID,
max(a14.CUST_REGION_NAME) CUST_REGION_NAME,
sum(a11.TOT_DOLLAR_SALES) Revenue
from CITY_SUBCATEG_SLS a11
join LU_CUST_CITY a12
on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)
join LU_CUST_STATE a13
on (a12.CUST_STATE_ID = a13.CUST_STATE_ID)
join LU_CUST_REGION a14
on (a13.CUST_REGION_ID = a14.CUST_REGION_ID)
where (a13.CUST_REGION_ID in (1)
or ((a13.CUST_REGION_ID)
in (select ps21.CUST_REGION_ID
from #ZZMQ01 ps21)))
group by a13.CUST_REGION_ID
Note that the final pass does correctly render the OR conjunction. Since ZZMQ01 includes only Northeast, however, the WHERE clause is effectively Customer Region in (Northeast) OR Customer Region in (Northeast). The problem, then, is not the conjunction; it is rather that the metric qualification is pre-filtered according to Customer Region.
Why, does the filtering set (ZZMQ00) not return all four desired Customer Regions per the OR conjunction? The reason has to do with the distinction between filters and limits. Filters restrict a data set prior to aggregation (WHERE clause), while limits restrict the data after aggregation (HAVING clause). Attribute qualifications are filters, while a metric qualification must always use a limit condition since there is no way to use an aggregated metric result to reduce a data set without first aggregating the metric.
A pair of filtering conditions can be combined with OR, as can a pair of limit conditions. But in a pass of SQL where there is both a filter and a limit, the result can only intersect (AND) the filter and limit. In a union (OR), the dataset for one condition must be evaluated without respect to the other and vice versa. When both a filter and limit are used, the filter applies first and the limit is then applied to the filtered dataset -- that is, behaving like AND.
ACTION
The advanced option to consider other qualifications in the metric qualification should be disabled. Then the metric qualification can evaluate over all customer regions, and the report produces the expected result.
select a13.CUST_REGION_ID CUST_REGION_ID,
rank () over ( order by sum(a11.TOT_DOLLAR_SALES) desc) WJXBFS1
into #ZZAM00
from CITY_SUBCATEG_SLS a11
join LU_CUST_CITY a12
on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)
join LU_CUST_STATE a13
on (a12.CUST_STATE_ID = a13.CUST_STATE_ID)
group by a13.CUST_REGION_ID
select pa11.CUST_REGION_ID CUST_REGION_ID
into #ZZMQ01
from #ZZAM00 pa11
where (pa11.WJXBFS1 <= 3.0)
select a13.CUST_REGION_ID CUST_REGION_ID,
max(a14.CUST_REGION_NAME) CUST_REGION_NAME,
sum(a11.TOT_DOLLAR_SALES) Revenue
from CITY_SUBCATEG_SLS a11
join LU_CUST_CITY a12
on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)
join LU_CUST_STATE a13
on (a12.CUST_STATE_ID = a13.CUST_STATE_ID)
join LU_CUST_REGION a14
on (a13.CUST_REGION_ID = a14.CUST_REGION_ID)
where (a13.CUST_REGION_ID in (1)
or ((a13.CUST_REGION_ID)
in (select ps21.CUST_REGION_ID
from #ZZMQ01 ps21)))
group by a13.CUST_REGION_ID
Note: The "other qualifications" option applies to all other conditions in the same filter object where the metric qualification resides. If it were necessary, for instance, to filter the ranking qualification on dates but ignore the customer region qualification, a separate filter object could be created for the Revenue rank AND Date conditions. Consult the following Strategy Knowledgebase document for further information on this technique.
KB20409 (KB5200-8X-2464): How to create a metric qualification that includes some of the associated qualifications but ignores others in Strategy SQL Generation Engine 8.x
Related Strategy Knowledgebase documents
KB18271 (KB5200-8X-2414): What is the metric qualification advanced option 'When resolving this qualification in a report, other qualifications will be taken into consideration and merged' in Strategy SQL Generation Engine 8.x?
KB18272 (KB5200-8X-2415): How does the metric qualification advanced option to consider other qualifications interact with metric conditionality in Strategy SQL Generation Engine 8.x?
