Cross tabulation

Commands

dbr.crosstab - Turn a table report into a cross tabulation report. The first parameter is the crosstable column, the optional second parameter marks the last data column inside a crosstable.
dbr.hsum - Calculate horizontal sum in a cross tabulation report
dbr.hmin - Calculate horizontal minimum in a cross tabulation report
dbr.hmax - Calculate horizontal maximum in a cross tabulation report
dbr.hminus - Calculate horizontal value with formula Col1 - Col2 - Col3...
dbr.hnull - Creates a placeholder for horizontal column used with dbr.calc
dbr.hcount - Calculate number of values horizontally in a cross tabulation report
dbr.havg - Calculate horizontal average in a cross tabulation report
dbr.hidecolumn.data - Allows hiding crosstable data columns, keep summary column. Used with dbr.calc
dbr.hidecolumn.set.data - Allows hiding crosstable data columns based on data CrossTabSet name
dbr.hidedatacolumn - Same as dbr.hidecolumn.data, kept for compatibility
dbr.hidecolumn.data.all - Hide all crosstable data columns, keep summary column.
dbr.hidecolumn.summary - Allows hiding crosstable summary columns, keeping data columns. . Used with dbr.calc
dbr.crosstab.title - Redefine the default title for horizontal summary column
dbr.crosstab.order - Set the order of the cross tabulation columns
dbr.crosstab.col - Predefine the cross tabulation columns
dbr.crosstab.options - Add crosstab option
dbr.crosstab.col.title - Set column title for crosstab horizontal summary column
dbr.crosstab.col.style - Set columnstyle for crosstab horizontal summary column
dbr.crosstab.header - Add additional header group in crosstab
dbr.crosstab.header.col - Predefine the 2nd level cross tabulation header columns defined in dbr.crosstab.header

Syntax

select 'dbr.crosstab', ColumnReference [, ColumnReference]
select 'dbr.hsum', ColumnReference, [ColumnReference... ]
select 'dbr.hmin', ColumnReference, [ColumnReference... ]
select 'dbr.hmax', ColumnReference, [ColumnReference... ]
select 'dbr.hminus', ColumnReference, [ColumnReference... ]
select 'dbr.hnull', ColumnReference, [ColumnReference... ]
select 'dbr.hcount', ColumnReference, [ColumnReference... ]
select 'dbr.havg', ColumnReference, [ColumnReference... ]
select 'dbr.hidecolumn.data', ColumnReference, [ColumnReference... ]
select 'dbr.hidecolumn.set.data', 'CrossTabSet', ColumnReference, [ColumnReference... ]
select 'dbr.hidedatacolumn', ColumnReference, [ColumnReference... ]
select 'dbr.hidecolumn.data.all' {, 1/0 }
select 'dbr.hidecolumn.summary', ColumnReference, [ColumnReference... ]
select 'dbr.crosstab.title', 'title'
select 'dbr.crosstab.order', 'asc'|'desc'
select 'dbr.crosstab.col', col_value1, [col_value2... ]
select 'dbr.crosstab.options', 'no_null_data'
select 'dbr.crosstab.col.title', ColumnReference, 'title'
select 'dbr.crosstab.col.style', ColumnReference, columnstyle
select 'dbr.crosstab.header', ColumnReference
select 'dbr.crosstab.header.col', col_value1, [col_value2... ]

Explanation

In cross tabulation the result set's data is separated into header and data sections divided by a cross tabulation column. Header columns (left from cross tabulation-column) will determine the distinct rows in the final result set. The data columns (derived for the cross tabulation-column) will be repeated to the right. By defining the second parameter in the dbr.crosstab-command, header columns can also appear on the right side of cross tabulation data columns.

Examples

Say we have 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 the column are calculated as group columns and the data columns are calculated underneath each group column.

/* 
  Define the Period as a cross tabulation column
*/
select 'dbr.crosstab', 'Period';

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

Calculating column and row summaries

You can apply the aggregate functions over row and column values. For columns use: dbr.sum, dbr.min, dbr.max, dbr.avg or dbr.count. For rows use dbr.hsum, dbr.hmin or dbr.hmax.

myDBR handles the hard work by doing the calculation on applicable columns and adds summary columns if necessary. If the data column has no name (set to empty string) the header part of it will be hidden (see example below).

select 'dbr.crosstab', 'Quarter';
select 'dbr.sum', 'Items','Weight';
select 'dbr.crosstab.title', '2008 total';
select 'dbr.hsum', 'Items','Weight';


select Name, 
       concat(year(InsertDate), ' Q',quarter(InsertDate)) as 'Quarter', 
       sum(Items) as 'Items',
       sum(Weight) as 'Weight'
from mydb.Production
group by Name, 2;

You can also apply calculate multiple horizontal aggregates for a column by duplicating the column and hiding the datacolumn. A datacolumn is the repeated crosstab-data column. In the example multiple multiple horizontal aggregates are calculated and styled:

 /* Set titles for horizontal aggregate columns */
select 'dbr.crosstab.col.title', 'value',  'Avg';
select 'dbr.crosstab.col.title', 'sum',  'Sum';
select 'dbr.crosstab.col.title', 'count',  'Count';
/* Style the horizontal average column */
select 'dbr.crosstab.col.style', 'value', '[color:red]%.1f';
/* Style the horizontal sum column */
select 'dbr.crosstab.col.style', 'sum', '[color:green]';

select 'dbr.crosstab', 'Quarter';

/* Horizontal aggregates */
select 'dbr.havg', 'value';
select 'dbr.hsum', 'sum';
select 'dbr.hcount', 'count';

/* Show the sum and count columns only in the horizontal summary section */
select 'dbr.hidecolumn.data', 'sum';
select 'dbr.hidecolumn.data', 'count';

/* Vertical aggregates */
select 'dbr.sum', 'value';
select 'dbr.sum', 'sum';
select 'dbr.sum', 'count';

select 
  Name, 
  concat('Q',quarter(InsertDate)) as 'Quarter', 
  sum(Items) as '[value]',
  sum(Items) as '[sum]',
  sum(Items) as '[count]'
from TestTable
group by Name, 2
order by Name, 2;

Define the crosstab column header order

If you need to define the order of existing crosstab column headers, you can use dbr.crosstab.order-command.

select 'dbr.crosstab', 'Month';
select 'dbr.crosstab.order', 'asc';
select 'dbr.sum', 'value';
select 'dbr.hsum', 'value';

select Name,
       Month,
       Value as '[value]',
from mydb.monthlyvalues
order by Name;

If your data does not include all crosstab column headers you want the report to show, use the dbr.crosstab.col-command.

Predefine data columns

By predefining the data columns you can define which data columns are shown in cross tabulation report (and in which order). This is useful in cases where you already know what cross tabulation columns you want in the report (series of months, fixed quarters etc.) and/or you want to include columns that do not exist in data itself.

select 'dbr.crosstab', 'Month';
select 'dbr.sum', 'value';
select 'dbr.hsum', 'value';

select Name,
       Month,
       Value as '[value]',
from mydb.monthlyvalues
order by Name;

In the example we'll create a report with a monthly distribution. The data in the example does not include all the months (1-12).

In the example the months were derived from the fetched data. This includes both order and the actual values. In order to define all the months (1-12) and also define the order of the columns you can use the dbr.crosstab.col-command.

select 'dbr.crosstab', 'Month';
select 'dbr.crosstab.col', 1,2,3,4,5,6,7,8,9,10,11,12;
select 'dbr.sum', 'value';
select 'dbr.hsum', 'value';

select Name, 
       Month, 
       Value as '[value]',
from mydb.monthlyvalues
order by Name;

As a result you'll get a result where all the months are defined and are defined in logical order.

If you have a dynamic number of columns (for example the report parameter contains a date range), you can also issue the dbr.crosstab.col-command multiple times. Each subsequent dbr.crosstab.col-command will add columns to final report.

select 'dbr.crosstab', 'Month';
declare iMonth int;

set iMonth = 1;
while( iMonth <= inMonthParameter ) do
  select 'dbr.crosstab.col', iMonth;
  set iMonth = iMonth + 1;
end while;

select 'dbr.sum', 'value';
select 'dbr.hsum', 'value';

select Name, 
       Month, 
       Value as '[value]',
from mydb.monthlyvalues
order by Name;

If the report query will produce data columns not defined in predefined data columns, the columns will be added to the end of column list.

Full cross tabulation example

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

/* 
  Define the Period as a cross tabulation column
  Add column and horizontal sums
  Name the crosstab column
*/
select 'dbr.crosstab', 'Period';
select 'dbr.sum', 'last', 'this', 'next';
select 'dbr.hsum', 'last', 'this', 'next';
select 'dbr.crosstab.title', 'Total H1';
select 'dbr.summary.text', 'area','Total';

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

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

/* 
  Define the Period as a cross tabulation column and 'next' column to be the last data column inside the cross tabulation.
*/
select 'dbr.crosstab', 'Period', 'next';
select 'dbr.sum', 'last', 'this', 'next';
select 'dbr.hsum', 'last', 'this', 'next';
select 'dbr.crosstab.title', 'Total H1';
select 'dbr.summary.text', 'area','Total';

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

The 'no_null_data' option allows suppressing null crosstable column generated for example with LEFT JOIN. The row is shown (if other rows contain data), but the NULL column will not be created as crosstab data column.

The example creates rows for each month even if the month has no given rate for the hotel. As crosstab column 'name' will create a NULL value due LEFT JOIN, the NULL column is suppressed with the 'no_null_data'-option.

create temporary table d (
d date
);

insert into d values ('2016-01-01'),('2016-01-02'),('2016-01-03');
select 'dbr.crosstab', 'name';
select 'dbr.crosstab.options', 'no_null_data';

select d.d as 'Day', h.name as '[name]', h.rate as ''
from d d
  left join (
    select cast('2016-01-02' as date) as d, 'The Imprerial' as name, 680 as 'rate'
    from mydbr_version 
  ) as h on h.d=d.d
order by d.d;