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 in cross tabulation is to turn the table from 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 are determined by the order of the data in the query. If you want the data columns to appear in certain order
or you data may not contain all the data columns, you can use
dbr.crosstab.col-command to predefine the data columns.
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.