In the report that uses the metric qualification, the Category Revenue metric actually returns the revenue for the top-ranking Subcategory within the Category, same as the report-level Revenue metric. Consequently, the contribution is 100% for all rows, which is not the desired analysis.
Explanation of the discrepancy
In both reports, the metric condition identifies the highest-ranking Subcategory in each Category. The difference is that the ranking qualification in the report filter evaluates first and applies subsequently to all template metrics, while the report limit evaluates last, after the Category Revenue and Contribution metrics have already been calculated.
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
In the metric qualification report, the Category-level metric is subject to the set of attribute elements identified in the report filter. Since the ranking qualification allows only one Subcategory per Category, the filtered Category-level metric can be no higher than the value for the Subcategory. By contrast, the report limit merely discards the final result rows for lower-ranking Subcategories. The Category-level revenue has already been calculated including the entire Category and is not affected by the metric limit.
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Metric qualification procedureReport limit procedureCalculate each Subcategory's rank within its Category Create an intermediate table with the top ranking Subcategory for each CategoryCalculate the Subcategory-level revenue, including only the Subcategories passing through the filterCalculate the Subcategory-level revenue over all SubcategoriesCalculate the Category-level revenue, including only the Subcategories passing through the filterCalculate the Category-level revenue over all categoriesJoin the metrics and calculate the contributionJoin the metrics and calculate the contribution Select the final result rows where the rank of contribution is exactly 1Metric qualification SQLReport limit SQL
|
Ranking qualification; #ZZMQ03 is the set of Subcategories
select a11.SUBCAT_ID SUBCAT_ID,
sum(a11.TOT_DOLLAR_SALES) Revenue
into #ZZMD00
from CITY_SUBCATEG_SLS a11
group by a11.SUBCAT_ID
select a11.CATEGORY_ID CATEGORY_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD01
from YR_CATEGORY_SLS a11
group by a11.CATEGORY_ID
select pa13.CATEGORY_ID CATEGORY_ID,
pa11.SUBCAT_ID SUBCAT_ID,
rank () over (partition by pa13.CATEGORY_ID order by ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) desc) WJXBFS1
into #ZZAM02
from #ZZMD00 pa11
join LU_SUBCATEG a12
on (pa11.SUBCAT_ID = a12.SUBCAT_ID)
join #ZZMD01 pa13
on (a12.CATEGORY_ID = pa13.CATEGORY_ID)
select pa11.CATEGORY_ID CATEGORY_ID,
pa11.SUBCAT_ID SUBCAT_ID
into #ZZMQ03
from #ZZAM02 pa11
where (pa11.WJXBFS1 <= 1.0)
| Ranking qualification; #ZZMQ03 is the set of Subcategories
select a11.SUBCAT_ID SUBCAT_ID,
sum(a11.TOT_DOLLAR_SALES) Revenue
into #ZZMD00
from CITY_SUBCATEG_SLS a11
group by a11.SUBCAT_ID
select a11.CATEGORY_ID CATEGORY_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD01
from YR_CATEGORY_SLS a11
group by a11.CATEGORY_ID
select pa13.CATEGORY_ID CATEGORY_ID,
pa11.SUBCAT_ID SUBCAT_ID,
rank () over (partition by pa13.CATEGORY_ID order by ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) desc) WJXBFS1
into #ZZAM02
from #ZZMD00 pa11
join LU_SUBCATEG a12
on (pa11.SUBCAT_ID = a12.SUBCAT_ID)
join #ZZMD01 pa13
on (a12.CATEGORY_ID = pa13.CATEGORY_ID)
select pa11.CATEGORY_ID CATEGORY_ID,
pa11.SUBCAT_ID SUBCAT_ID
into #ZZMQ03
from #ZZAM02 pa11
where (pa11.WJXBFS1 <= 1.0)
|
Template metrics; set qualification applies at Subcategory level
select a11.SUBCAT_ID SUBCAT_ID,
sum(a11.TOT_DOLLAR_SALES) Revenue
into #ZZMD04
from CITY_SUBCATEG_SLS a11
join #ZZMQ03 pa12
on (a11.SUBCAT_ID = pa12.SUBCAT_ID)
group by a11.SUBCAT_ID
select pa12.CATEGORY_ID CATEGORY_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD05
from CITY_SUBCATEG_SLS a11
join #ZZMQ03 pa12
on (a11.SUBCAT_ID = pa12.SUBCAT_ID)
group by pa12.CATEGORY_ID
| Calculate Revenue at Subcategory and Category levels; no filter
select a11.SUBCAT_ID SUBCAT_ID,
sum(a11.TOT_DOLLAR_SALES) Revenue
into #ZZMD00
from CITY_SUBCATEG_SLS a11
group by a11.SUBCAT_ID
select a11.CATEGORY_ID CATEGORY_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD01
from YR_CATEGORY_SLS a11
group by a11.CATEGORY_ID
|
Join results
select pa13.CATEGORY_ID CATEGORY_ID,
a14.CATEGORY_DESC CATEGORY_DESC,
pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.Revenue Revenue,
pa13.WJXBFS1 WJXBFS1,
ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) WJXBFS2
from #ZZMD04 pa11
join LU_SUBCATEG a12
on (pa11.SUBCAT_ID = a12.SUBCAT_ID)
join #ZZMD05 pa13
on (a12.CATEGORY_ID = pa13.CATEGORY_ID)
join LU_CATEGORY a14
on (pa13.CATEGORY_ID = a14.CATEGORY_ID)
drop table #ZZMD00
drop table #ZZMD01
drop table #ZZAM02
drop table #ZZMQ03
drop table #ZZMD04
drop table #ZZMD05
| Join results; calculate Contribution and rank
select pa13.CATEGORY_ID CATEGORY_ID,
a14.CATEGORY_DESC CATEGORY_DESC,
pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.Revenue Revenue,
pa13.WJXBFS1 WJXBFS1,
ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) WJXBFS2,
rank () over (partition by pa13.CATEGORY_ID order by ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) desc) WJXBFS3
into #ZZAM02
from #ZZMD00 pa11
join LU_SUBCATEG a12
on (pa11.SUBCAT_ID = a12.SUBCAT_ID)
join #ZZMD01 pa13
on (a12.CATEGORY_ID = pa13.CATEGORY_ID)
join LU_CATEGORY a14
on (pa13.CATEGORY_ID = a14.CATEGORY_ID)
Keep only top-ranking rows without affecting metric results
select pa11.CATEGORY_ID CATEGORY_ID,
pa11.CATEGORY_DESC CATEGORY_DESC,
pa11.SUBCAT_ID SUBCAT_ID,
pa11.SUBCAT_DESC SUBCAT_DESC,
pa11.Revenue WJXBFS1,
pa11.WJXBFS1 WJXBFS2,
pa11.WJXBFS2 WJXBFS3
from #ZZAM02 pa11
where (pa11.WJXBFS3 <= 1.0)
drop table #ZZMD00
drop table #ZZMD01
drop table #ZZAM02
|
Absolute filtering as an alternative to the report limit in this scenario
The metric qualification obtains this result because the Category-level and Contribution metrics specify standard filtering at the level of Category. The metric qualification at report level is evaluated at the level of Subcategory, and this is the level that applies to higher-level metrics as well.
If, instead, the Category and Contribution metrics use Absolute filtering at Category level, then the metric qualification returns the same result as the report limit. The metric condition still applies before aggregating template metrics, but the Category-level metric "raises" the Subcategory filter up to Category. Where the Subcategory is "Science & Technology," an absolute-filtered metric calculates over the entire Books category and the final result is as desired.
SQL using Absolute filtering
Ranking qualification; #ZZMQ03 is the set of Subcategories
select a11.SUBCAT_ID SUBCAT_ID,
sum(a11.TOT_DOLLAR_SALES) Revenue
into #ZZMD00
from CITY_SUBCATEG_SLS a11
group by a11.SUBCAT_ID
select a11.CATEGORY_ID CATEGORY_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD01
from YR_CATEGORY_SLS a11
group by a11.CATEGORY_ID
select pa13.CATEGORY_ID CATEGORY_ID,
pa11.SUBCAT_ID SUBCAT_ID,
rank () over (partition by pa13.CATEGORY_ID order by ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) desc) WJXBFS1
into #ZZAM02
from #ZZMD00 pa11
join LU_SUBCATEG a12
on (pa11.SUBCAT_ID = a12.SUBCAT_ID)
join #ZZMD01 pa13
on (a12.CATEGORY_ID = pa13.CATEGORY_ID)
select pa11.CATEGORY_ID CATEGORY_ID,
pa11.SUBCAT_ID SUBCAT_ID
into #ZZMQ03
from #ZZAM02 pa11
where (pa11.WJXBFS1 <= 1.0)
Subcategory-level Revenue; set qualification applies at Subcategory level
select a11.SUBCAT_ID SUBCAT_ID,
sum(a11.TOT_DOLLAR_SALES) Revenue
into #ZZMD04
from CITY_SUBCATEG_SLS a11
join #ZZMQ03 pa12
on (a11.SUBCAT_ID = pa12.SUBCAT_ID)
group by a11.SUBCAT_ID
Category-level Revenue; set of Subcategories is converted to whole Categories
select a11.CATEGORY_ID CATEGORY_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD05
from YR_CATEGORY_SLS a11
where ((a11.CATEGORY_ID)
in (select ps21.CATEGORY_ID
from #ZZMQ03 ps21))
group by a11.CATEGORY_ID
Join results
select pa13.CATEGORY_ID CATEGORY_ID,
a14.CATEGORY_DESC CATEGORY_DESC,
pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.Revenue Revenue,
pa13.WJXBFS1 WJXBFS1,
ISNULL((pa11.Revenue / NULLIF(pa13.WJXBFS1, 0)), 0) WJXBFS2
from #ZZMD04 pa11
join LU_SUBCATEG a12
on (pa11.SUBCAT_ID = a12.SUBCAT_ID)
join #ZZMD05 pa13
on (a12.CATEGORY_ID = pa13.CATEGORY_ID)
join LU_CATEGORY a14
on (pa13.CATEGORY_ID = a14.CATEGORY_ID)
drop table #ZZMD00
drop table #ZZMD01
drop table #ZZAM02
drop table #ZZMQ03
drop table #ZZMD04
drop table #ZZMD05
The results of this SQL (using absolute filtering) exactly match the results shown above for the report limit. It is a matter of thinking about the level at which the ranking qualification should apply. For this reporting requirement, applying the ranking set at report level is incorrect; instead, that set should be raised to Category level.
