Cross-tabulation Report

In cross-tabulation, the result set data is divided into header and data sections, separated by a cross-tabulation column. Header columns, located to the left (and optionally to the right) of the cross-tabulation column, define the unique rows in the final result set. Data columns are then repeated under each distinct value derived from the cross-tabulation column.

The primary purpose of cross-tabulation is to transform a standard table structure into a pivoted layout. For example, converting this structure:

Into this:

When using cross-tabulation, the sequence of rows and data columns is determined by the order of the data in your query. To explicitly specify the column order or ensure all columns are displayed even if some data is missing, use the dbr.crosstab.col command.

Data Structure in a Cross-tabulation Report

Consider the following 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 identify the various components of the result set:

By designating the 'Period' column as the cross-tabulation column, its unique values are used as group headers, with the data columns calculated beneath each header.

Any myDBR commands applied to other columns are processed automatically. In the following example, we apply styles and aggregate functions to the data columns:

/* 
  Define 'Period' as the cross-tabulation column.
  Ensure the result set is ordered correctly.
*/
select 'dbr.crosstab', 'Period';

/* Apply a visual style to the current year data */
select 'dbr.colstyle', 'this', mydbr_style('Diff colors with bg'); 

/* Calculate totals for each year column */
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;

For a complete description of available options, refer to the dbr.crosstab command documentation.