Header levels

Commands

dbr.hdr - Set header level in a query
dbr.hdr.options - Do not hide repeating headers

Syntax

select 'dbr.hdr', ColumnReference [, ColumnReference ...]
select 'dbr.hdr.options', 'option'

Header level options:

Explanation

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.

Examples

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.