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 an 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 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;

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.

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 the 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 a 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 the 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 the column list.

You can add a second crosstab level based on the data:

  
select 'dbr.crosstab.header', 'Year';

select 'dbr.crosstab', 'Quarter', 'Items';
select 'dbr.sum', 'Items';

select 
  Name, 
  year(InsertDate) as 'Year',
  concat('Q',quarter(InsertDate)) as 'Quarter[Quarter]', 
  sum(Items) as '[Items]
from data
group by Name, Year, 3
order by Name, Year, 3;

You can add a second crosstab level header row:

select distinct 'dbr.crosstab.group', 
  date_format(InsertDate, '%Y-%m'), 
  concat(date_format(concat(InsertDate,'-01'), '%Y Q'), QUARTER(concat(InsertDate,'-01'))) as 'Quarter'
from data;

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

select Division, date_format(InsertDate, '%Y-%m') as 'Month', Value as '[Value]'
from data;

You can attach linked reports to any column in the table apart from the crosstab column. Any column can be used as a linked report parameter. If you attach a linked report to a crosstable's data column, the linked report will be generated for each repeated data item.

select 'dbr.report', 'sp_DBR_show_count', 'popup', '[value]', 'inID=id', 'inCnt=cnt';
select 'dbr.report', 'sp_DBR_show_count', 'popup', '[value.h]', 'inID=id',  'inCnt=cnt.h';

select 'dbr.crosstab', 'ct', 'cnt';
select 'dbr.sum','value';
select 'dbr.hsum','value';
select 'dbr.hsum','cnt';
select 'dbr.hidecolumn', 'cnt'; 


select 
  1 as id,
  'Sammple crosstab' as title,
  'Q1' as  '[ct]',
  1200 as value,
  11 as cnt,
  'last column'
union
select 
  1,
  'Sammple crosstab',
  'Q2',
  2300,
  12,
  'last column';

The report output

Internally the data looks like this (without the hidden 'cnt' column):

The first linked report is attached to the column value and has two parameters: id and the cnt. As the id column is before the repeated data columns, all inID parameters get the same value 1. The cnt column is within the data column sets, so each set (Q1 and Q2) have their own value (11 for Q1 and 12 for Q2). When the user clicks the value from Q2 (=2300), the database is called with "sp_DBR_show_count(1, 12)".

The second linked report is attached to the horizontal summary column value.h. The cnt.h reference points to the horizontal summary column's cnt value of 23. When the user clicks the horizontal summary value (3500), the database is called with "sp_DBR_show_count(1, 23)".

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 a 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;