Grouping and Summarization of Tabular Data

This section discussed grouping and summarization of tabular data. Many business reports require some processing on the raw data before presenting them as tables. The most common processing is the grouping and summarization of table rows. This is usually a post data retrieval operation and takes place after the data is loaded into a lens object and bound to an element.

Sorting and Grouping

Sorting and grouping of tables is by far the most common operation in business reports. Grouping is dependent on the sorting of grouping columns. After row grouping is produced, further processes can be performed, such as finding the top N groups from the grouped results.

Sorting

Sorting of tables is ideally done by a database. When a data set is large, however, sorting in memory may be impossible. There are two classes in Style Intelligence that produce a sorted table.

Grouping

Grouping in Style Intelligence consists of two functions: grouping of rows based on one or more columns and summarization on the grouped sections. If grouping is based on one column, all rows with the same value on that column are grouped into one section. A summarization row is added to each section. The summarization is specified with any valid formula. The end result would be in the following form (assuming subtotal and grand total summarization):

A 1
2
3
B 1
4
5

Total 8

If grouping is based on multiple columns, the grouping is nested. What this means is that a table is first grouped on the first grouping column and within each section, rows are grouped on the second grouping column, and so on. The end result would be in the following form (assuming subtotal and grand total summarization):

There are a few options that control how the grouping is done. By default, the grouping columns are shown in full. The first example would actually be rendered as the following by default:

A 1
A 2
3
B 1
B 4
5
Total 8

It is possible to control whether an extra row, containing the group headers (the values of the grouping column), is added before each section. By default this is turned off. If we turn it on, the result would look like the following:

A
A 1
A 2
3
B
B 1
B 4
5
Total 8

Grand Total Label property controls the label of the table summarization row. If it is set, the label is added to the table summarization row (grand total) as the first cell value.

A
1
2
3
B
1
4
5
Total 8

If we do not want to add an additional row for the group headers, we can set the option 'Group Header In Place', so the above table would look like the following:

A 1
2
3
B 1
4
5
Total 8

Although it is possible to implement Grouping and Summarization programmatically, the recommended way is to use the Report Designer GUI. See the Report Creation With Grouping/Summarization chapter for a complete example.