Reports: matrix report with percentage totals

...previous

On my previous note on reports I was asked for a query, that would return percentage ratios for vertical and horizontal subtotals (at least as I have understood ).

So in this case our report draft could be represented in the following way:

| Departments names block | Total
------------+----------+---------+----------+---------------
| | | |
Cities -+- Detailed data -+- Ratio (%) of totals
Names | for each | for every city
block -+- city & department -+- to the very total
| | | |
------------+----------+---------+----------+----------------
Total | Ratio (%) of totals for every | Overall total
| department to the very total | (100%)

As I have not very much spare time I decided to put here only both solutions (non-model and model) without any explanation, and if you have any doubts/questions - you can put it as a comment, then I will elaborate on this.

Input data you can still find here.

Non-Model solution:

SQL> select nvl(city, 'TOTAL') as city_,
2 nvl2(city,to_char(sum(decode(dep, 'DEP1', sal, 0))),round(sum(decode(dep, 'DEP1', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep1,
3 nvl2(city,to_char(sum(decode(dep, 'DEP2', sal, 0))),round(sum(decode(dep, 'DEP2', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep2,
4 nvl2(city,to_char(sum(decode(dep, 'DEP3', sal, 0))),round(sum(decode(dep, 'DEP3', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep3,
5 nvl2(city,to_char(sum(decode(dep, 'DEP4', sal, 0))),round(sum(decode(dep, 'DEP4', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep4,
6 round(2*100*ratio_to_report(sum(decode(flag, 1, sal, 3, sal, 0))) over (),1)||'%' total
7 from (
8 select city, dep, sum(sales) sal, grouping_id(city, dep) flag
9 from t
10 where trunc(year, 'y') = date '2007-01-01'
11 group by cube(city, dep))
12 group by city
13 order by nvl2(city, 0, 1), total desc
14 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ----------- ----------- ----------- ---------- ----------
Moscow 762 657 1020 487 87.9%
Omsk 34 213 156 0 12.1%
TOTAL 23.9% 26.1% 35.3% 14.6% 100%

SQL>

Model solution:

SQL> select city,dep1,dep2,dep3,dep4,total from t
2 where trunc(year,'y') = date '2007-01-01'
3 model
4 return updated rows
5 dimension by (city, dep)
6 measures(to_char(sales) dep1, to_char(sales) dep2, to_char(sales) dep3, to_char(sales) dep4, to_char(sales) total)
7 rules
8 upsert all
9 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
10 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
11 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
12 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
13 total['TOTAL',null]=sum(dep1)[any,null]+sum(dep2)[any,null]+sum(dep3)[any,null]+sum(dep4)[any,null],
14 dep1['TOTAL',null]=round(sum(dep1)[any,null]*100/total['TOTAL',null],1)||'%',
15 dep2['TOTAL',null]=round(sum(dep2)[any,null]*100/total['TOTAL',null],1)||'%',
16 dep3['TOTAL',null]=round(sum(dep3)[any,null]*100/total['TOTAL',null],1)||'%',
17 dep4['TOTAL',null]=round(sum(dep4)[any,null]*100/total['TOTAL',null],1)||'%',
18 total[city<>'TOTAL',null]=round((dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()])*100/total['TOTAL',null],1)||'%',
19 total['TOTAL',null]='100%'
20 )
21 /

CITY DEP1 DEP2 DEP3 DEP4 TOTAL
------ ----------- ----------- ---------- ---------- ----------
Moscow 762 657 1020 487 87.9%
Omsk 34 213 156 0 12.1%
TOTAL 23.9% 26.1% 35.3% 14.6% 100%

SQL>

PS
Added after the comment.

"A good problem description worth half a solution" :)
So again I'm not sure whether this is needed or not, but if you want to calculate only percentages in matrix report even for detailed data you can use the following:

So the scheme of our report would be:

| Departments names block | Total
------------+----------+---------+----------+---------------
| | | |
Cities -+- Ratio (%) of detailed -+- Ratio (%) of totals
Names | data for each city & | for every city
block -+- department to total -+- to the very total
| | | |
------------+----------+---------+----------+----------------
Total | Ratio (%) of totals for every | Overall total
| department to the very total | (100%)

Non-model solution:

SQL> select nvl(city, 'TOTAL') as city_,
2 round(sum(decode(dep, 'DEP1', sal, 0)), 2) || '%' dep1,
3 round(sum(decode(dep, 'DEP2', sal, 0)), 2) || '%' dep2,
4 round(sum(decode(dep, 'DEP3', sal, 0)), 2) || '%' dep3,
5 round(sum(decode(dep, 'DEP4', sal, 0)), 2) || '%' dep4,
6 round(sum(decode(flag, 1, sal, 3, sal, 0)), 2) || '%' total
7 from (select city, dep, sum(rtr) sal, grouping_id(city, dep) flag
8 from (select t.*, 100 * ratio_to_report(sales) over() rtr
9 from t
10 where trunc(year, 'y') = date '2007-01-01')
11 group by cube(city, dep))
12 group by city
13 order by nvl2(city, 0, 1), total desc
14 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ------------- ------------ ------------- ------------- -----------
Moscow 22.89% 19.74% 30.64% 14.63% 87.89%
Omsk 1.02% 6.4% 4.69% 0% 12.11%
TOTAL 23.91% 26.13% 35.33% 14.63% 100%

SQL>

Model solution:

SQL> select city,
2 round(dep1,2)||'%' dep1,
3 round(dep2,2)||'%' de2,
4 round(dep3,2)||'%' de3,
5 round(dep4,2)||'%' de3,
6 round(total,2)||'%' total from (
7 select t.*, 100*ratio_to_report(sales) over () rtr from t
8 where trunc(year,'y') = date '2007-01-01')
9 model
10 return updated rows
11 dimension by (city, dep)
12 measures(rtr dep1, rtr dep2, rtr dep3, rtr dep4, rtr total)
13 rules
14 upsert all
15 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
16 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
17 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
18 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
19 dep1['TOTAL',null]=sum(dep1)[any,null],
20 dep2['TOTAL',null]=sum(dep2)[any,null],
21 dep3['TOTAL',null]=sum(dep3)[any,null],
22 dep4['TOTAL',null]=sum(dep4)[any,null],
23 total[any,null]=dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()]
24 )
25 /

CITY DEP1 DE2 DE3 DE3 TOTAL
------ ----------- ------------ ----------- ------------ -----------
Moscow 22.89% 19.74% 30.64% 14.63% 87.89%
Omsk 1.02% 6.4% 4.69% 0% 12.11%
TOTAL 23.91% 26.13% 35.33% 14.63% 100%

SQL>