Cross-tabulation report

In cross tabulation, the result set's data is divided into header and data sections divided by a cross-tabulation column. Header columns, left from the cross tabulation-column (and optionally also on the right) will determine the distinct rows in the final result set and the data columns will be repeated under distinct values under values derived for the cross tabulation-column.

The purpose of cross tabulation is to turn the table from this:

into this:

When using cross-tabulation, the order of the rows in the report will be determined by the order of the data in the query. Similarly, the order of the data columns is determined by the order of the data in the query. If you want the data columns to appear in a certain order or your data may not contain all the data columns, you can use dbr.crosstab.col-command to predefine the data columns.

Understanding the data parts in a cross-tabulation report

Say we have a basic data set:

select RepArea as 'Reporting area', 
       Period, 
       last_year as 'Last year', 
       this_year as 'This year', 
       next_year as 'Next year'
from mydb.ProductionSummary;

We can determine the different parts of the result set:

By defining the column 'Period' as a cross-tabulation column, the distinct values in that column are calculated as group columns and the data columns are calculated underneath each group column.

If any other column contains myDBR commands, they are automatically handled. In the example, we'll use style and some aggregate functions in data columns.

/* 
  Define the Period as a cross tabulation column
  Make sure you remember to order the result set accordingly
*/
select 'dbr.crosstab', 'Period';

/* Highlight the current year */
select 'dbr.colstyle', 'this', mydbr_style('Diff colors with bg'); 

/* Calculate sums */
select 'dbr.sum', 'last','this','next';

select RepArea as 'Reporting area', 
       Period, 
       last_year as 'Last year[last]', 
       this_year as 'This year[this]', 
       next_year as 'Next year[next]'
from mydb.ProductionSummary;

See full description of Cross Tabulation command.