With header levels, 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 calculate subtotals for each header level. Multiple header levels can be set by issuing the dbr.hdr command for each header level you want to assign.

To ensure efficient handling of header levels with minimal server load, it is necessary for myDBR to have the rows in the result set 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, the result is as follows:

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 applied to the selected header set. In the next step, we'll use the sum function:

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, the aggregate function sum is calculated for each header set, showing the total of all rows within each group. Note that the GROUP BY clause ensures the data is sorted correctly, which allows myDBR to process the report without needing to cache all the data.

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 a header level break occurs on either level.