Reports: matrix report

...previous

After a while I decided to write one more note on reports. Now it would be about matrix reports.

The input data you can take here.
As you can find out it is information on sales for two years (2006, 2007) of four departments (DEP1, DEP2, DEP3, DEP4) in two cities (Omsk, Moscow).
Imagine we need to get sales only for the year 2007, but present it as a matrix report.
Horizontally information on departments should be placed, vertically it should be information on cities. In the last row and column of a table we need to show totals on departments and cities consequently. At the bottom in the most right column we need to place overall total sales.
So the general draft of our report would look like:

| Departments names block | Total
------------+----------+---------+---------+-------------
| | | |
Cities -+- Detailed data -+- Totals
Names | for each | for
block -+- city & department -+- every city
| | | |
------------+----------+---------+---------+-------------
Total | Totals for every department | Overall total

Non-Model solution:
To build such a report we need to get subtotals both on departments and cities. We also need to get the overall total of all sales for the year 2007. For such requirments we need to use CUBE Extension to GROUP BY.
So the whole query would be:

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

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ---------- ---------- ---------- ---------- ----------
Moscow 762 657 1020 487 2926
Omsk 34 213 156 0 403
TOTAL 796 870 1176 487 3329

SQL>

This is the report we were looking for. So we can easily see that Dep3 sales in Omsk in the year 2007 were 156, while total sales of Dep3 were 1176, and total sales in Omsk were 403. Overall total sales were 3329.

Model solution:
Using model clause we need to have a tricky RETURN UPDATED ROWS clause. So that we would not group data in the outer query.
Also we will use UPSERT ALL command for all rules. What is this and how it can be used you can read in one of my previous notes. So the query would be:

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(sales dep1, sales dep2, sales dep3, sales dep4, 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 dep1['TOTAL',null]=sum(dep1)[any,null],
14 dep2['TOTAL',null]=sum(dep2)[any,null],
15 dep3['TOTAL',null]=sum(dep3)[any,null],
16 dep4['TOTAL',null]=sum(dep4)[any,null],
17 total[any,null]=dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()]
18 )
19 /

CITY DEP1 DEP2 DEP3 DEP4 TOTAL
------ ---------- ---------- ---------- ---------- ----------
Moscow 762 657 1020 487 2926
Omsk 34 213 156 0 403
TOTAL 796 870 1176 487 3329

SQL>

Notice that we have put as many measures for departments as we have in our data. So we are doing a column to row transformation, but as it was in the non-model solution it is static transformation. So we need to know how many columns we need to get beforehand.

As you know the model clause is working by executing a bunch of rules (one by one in a sequential order in our case).

We are placing the following groups of rules:
First we create new rows with department equal NULL, where we place sales of each department in each city.
Then we place rules for creating a TOTAL row, where we find the total sales for each department.
Finaly we write a rule (column TOTAL) - to calculate totals for every city and to find the overall total also.
So in the end we get the same results as with non-model solution.

to be continued...