Header Levels

Commands

dbr.hdr - Sets a header level in a query
dbr.hdr.options - Sets options for header level display

Syntax

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

Syntax Tips

Header level options:

• show - Keeps repeating headers visible. By default, myDBR hides repeating headers.

Explanation

With header levels, you can group rows with matching values in selected header columns, displaying the repeating data only once. Any aggregate function applied to the result set calculates subtotals for each header level. Multiple header levels can be defined by issuing the dbr.hdr command for each level.

For efficient processing with minimal server load, myDBR requires the result set rows to be sorted in the same order as the defined header levels.

Examples

Consider the following data set:

select SupplierName, Year, Quarter, sum(Items) as 'Items'
from mydb.exampledata e 
group by SupplierName, Year, Quarter;

which produces 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;

In most cases, a header level is combined with an aggregate function. In the next example, the sum function is added:

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;

The sum function calculates a subtotal for each header group, showing the total for all rows within it. The GROUP BY clause ensures the data is sorted correctly, allowing myDBR to process the report without caching the full data set.

Adding the Year column as a second header level gives 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;

Subtotals are now calculated whenever a header level break occurs at either level.