dbr.hdr
- Set header level in a querydbr.hdr.options
- Do not hide repeating headers
select 'dbr.hdr', ColumnReference [, ColumnReference ...]
select 'dbr.hdr.options', 'option'
Header level options:
show
- Keep repeating headers visible. By default, a myDBR report hides repeating headers
With a header level, you can group rows that have the same data under selected header columns as a single entity. When specifying a header level,
the repeating data is shown only once and any aggregate function applied to the result set will be calculated as a subtotal for each header
level. You can set more than one header level by simply reissuing the dbr.hdr
command for each header level you want to assign.
In order for myDBR to do the header levels efficiently (minimal server load) it is required that the rows in the result set are sorted in the same order as the header levels are set.
We have our data set:
select SupplierName, Year, Quarter, sum(Items) as 'Items' from mydb.exampledata e group by SupplierName, Year, Quarter;
which brings out the following data:
By assigning a header level to the first column, we get the following result:
select 'dbr.hdr', 'SupplierName'; select SupplierName, Year, Quarter, sum(Items) as 'Items' from mydb.exampledata e group by SupplierName, Year, Quarter;
However, in most cases the header level is combined with an aggregate function to be applied to the selected header set. We'll use sum in next step:
select 'dbr.hdr', 'SupplierName'; select 'dbr.sum', 'Items'; select SupplierName, Year, Quarter, sum(Items) as 'Items' from mydb.exampledata e group by SupplierName, Year, Quarter;
As a result, we see that the aggregate function sum is calculated for each header set with a total of all rows. Note that the group by-clause will sort the data into the right order, so myDBR does not have to cache all the data when processing the report.
Now if we add the Year-column as another header level to the report query we have the following query:
select 'dbr.hdr', 'SupplierName'; select 'dbr.hdr', 'Year'; select 'dbr.sum', 'Items'; select SupplierName, Year, Quarter, sum(Items) as 'Items' from mydb.exampledata e group by SupplierName, Year, Quarter;
Now subtotals are calculated each time the header level break occurs on either level.