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>

## 3 comments:

Hi Volder. Thanks for the example! I should've been clearer. I want to know how to use ratio_to_report for vertical (as you've shown), and horizontal percentages. (e.g.) Moscow shows percentages across for Deps 1-4 and total to 100% as well.) I'm not understanding how the flag works here? This is where I stumble on my efforts to calc top/bottom & left/right. Thanks for your time!

hi.

Added the new solution after your comment. Hope it is what you were looking for.

Thanks, Volder! I see my error clearly now.

Post a Comment