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 reportdbr.hmin
- Calculate horizontal minimum in a cross-tabulation reportdbr.hmax
- Calculate horizontal maximum in a cross-tabulation reportdbr.hminus
- Calculate horizontal value with formula Col1 - Col2 - Col3...dbr.hnull
- Creates a placeholder for horizontal column used with dbr.calcdbr.hcount
- Calculate number of values horizontally in a cross-tabulation reportdbr.havg
- Calculate horizontal average in a cross-tabulation reportdbr.hidecolumn.data
- Allows hiding crosstable data columns, keep summary column. Used with dbr.calcdbr.hidecolumn.set.data
- Allows hiding crosstable data columns based on data CrossTabSet namedbr.hidedatacolumn
- Same as dbr.hidecolumn.data, kept for compatibilitydbr.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.calcdbr.crosstab.title
- Redefine the default title for horizontal summary columndbr.crosstab.order
- Set the order of the cross-tabulation columnsdbr.crosstab.col
- Predefine the cross-tabulation columnsdbr.crosstab.options
- Add crosstab optiondbr.crosstab.col.title
- Set column title for crosstab horizontal summary columndbr.crosstab.col.style
- Set columnstyle for crosstab horizontal summary columndbr.crosstab.header
- Add additional header group in crosstabdbr.crosstab.header.col
- Predefine the 2nd level cross-tabulation header columns defined in dbr.crosstab.headerdbr.crosstab.group
- Group crosstable column values by adding another header leveldbr.crosstab.data.tooltip
- Define a corsstab header tooltip based on value the crosstab data
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' | 'summary_first'
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... ]
select 'dbr.crosstab.group', 'crosstab_value', 'group_name'
select 'dbr.crosstab.data.tooltip', 'crosstab_data_value', 'tooltip'
Where:
no_null_data
The 'no_null_data' option allows suppressing null crosstable column generated for example with LEFT JOIN
summary_first
Shows the horizontal summary columns before the data columns. Useful with large number of data columns.
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.
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;
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;