The task is to display a fixed level hierarchy together with totals for each level. This can be done using breaks. The most straightforward implementation might look like this:

If there are many levels and the level names are long, the table will be very wide, with a lot of empty space. It would be better to have more compact layout, for example:

This post describes how to implement such layout.

Steps

Let’s prepare a test query from the Island Resorts Marketing universe:

The initial layout of the table:

Apply breaks on Country, Resort and Service Line. Add totals.

Show break headers for all breaks:

Clear the break headers. Then copy the expression from the table body to the header (for the break columns) as below:

Merge header and footer cells:

Hide the text for the body cells by setting its color to white. You should not clear the content of the cells because these columns are used for the breaks.

Make a nice and compact layout:

Adjust colors and borders:

I learned this technique from my brilliant colleague Maria Ruchko 🙂