Grouping Basics

Understanding Groups

Groups break up a data set into sections of related information, allowing for aggregation and/or the removal of repeated values from the tabular detail. We see grouping in lots of settings, but restaurant menus provide a useful example. We never see menus like this:

Or like this:

In the above example, the menu is stored by dish type, but there are repeated values in the "Type" column, which makes the menu difficult to read at a glance. For this reason, menus typically group on dish type:

Grouping works a bit differently in the application, but its function is the same. In either case, the first step in creating a group is sorting on the appropriate data field.

Creating Sorts

Users may either build sorts from the report Wizard or from the report Designer.

The Sorts Tab is the third tab in the report Wizard. To create a sort, drag and drop a data field into the sorts pane. Select "Ascending" or "Descending" from the Sort Order drop-down menu. Add as many sorts as you like, and change sort priority by using the up () and down () arrows.

Once in the report Designer, users may adjust their Sorts from the settings menu ().

The Designer Sorts menu functions much the same as the Wizard Sorts menu does.

Once you've sorted on a field, you're ready to group on that field.

NOTE. Nested groups should reflect the sort order. The outermost group header should be the highest priority sort, and the footer order should be reversed. Find more information on this Understanding Sorting and Grouping.

Sorting By Formula

To sort and group by information that may not be contained within an individual data field, you can use Formulas. See What are formulas for additional help.

Pressing the button or the Formula Editor () button opens the Formula Editor window:

Add a Data Field by dragging and dropping it into the 'Formula' box or double-clicking it. Or enter it manually using the following format: {DataCategory.DataField}.
Add a Parameter by entering it manually using the following format: @ParameterName@.
Add a function by dragging and dropping it into the 'Formula' box or double-clicking it. Or enter it manually.

For example, say I had a data field containing a full date and time, and I wanted to analyze my sales by each month of the year over a multi-year period.

I could use the formula Month({Orders.OrderDate}) to return only the Month component of each date. Then I could sort my sales by Month.

Adding Group Sections

In order to add the group sections, click anywhere in the sections tab in the report designer. From here, select Add Section and then add Group Header.

Position of Group sections

Group sections are the primary way in which data is grouped into “buckets” that each share a unique attribute. Detail rows are arranged into the groups they belong to. The common attribute is defined by a preexisting sort. In order to make a group, first add a sort in the Sort Window.

For example, if you wanted to group a set of rows by each Product, first add a sort on the Products.ProductName field.

A sort is required for a group

The main difference between the header and footer is that the data comes after the header, but before the footer.

When you add a group section, you are asked which sort to base the group on. For each sort, you can choose to group on either the sort field or the sort category.

If you group on a field, the group breaks at each new value of the field.
If you group on a category, the group breaks at each new instance of the category’s unique identifier.

The group window appears when adding a group section

For example, instead of grouping by each Product, you may want to instead group by product quantity, so that products that are low or out of stock are grouped together. First, add a sort on the Products.Quantity field, then group by that field.

You can have multiple group sections, if you wanted to add additional levels of stratification to the report. Each group requires a corresponding sort.

TIP: THE ORDER OF THE SORTS HAS AN EFFECT ON THE WAY THE DATA IS ARRANGED. DATA IS SORTED BY THE FIRST FIELD, THEN ANY AMBIGUITIES ARE SORTED BY THE SECOND FIELD, THEN ANY REMAINING AMBIGUITIES ARE SORTED BY THE THIRD, AND SO ON. IN GENERAL, YOU SHOULD ORDER GROUP HEADERS BY THEIR ASSOCIATED SORTS, AND GROUP FOOTERS IN INVERSE ORDER.

Repeating Group

Position of Repeating Group sections

Repeating groups are a way to organize and display data that contain multiple one-to-many relationships. Each repeating group has data from “many” category, grouped by the “one” category. The following diagrams demonstrate how this works.

Two categories, X and Y, with a one-to-many relationship from X to Y, will display like so in a Detail section.

TIP: CATEGORY X HAS DUPLICATES SUPPRESSED – THIS IS SIMILAR TO GROUPING ON X.

Each X has 1 or more Y

If there is a third category, Z, with a one-to-many relationship from X to Z, there is not necessarily a relationship between Y and Z. So there may be blank rows with only Y or only Z.

Each X has 1 or more Y, and 1 or more Z

There are two potential solutions. If there is a relationship between Y and Z, then you can use Advanced Joins to impose additional constraints so that only the rows with both Y and Z are shown. For more information, see Joins.

On the other hand, there may be no relationship between Y and Z, only between X and Y, X and Z. Or there may be a relationship, but it does not matter for this data set. You can use repeating groups to ignore that relationship and simply organize the data by X instead.

To do so, create two repeating groups for X, and put Y in the first, and Z in the second:

Organizing Y and Z into repeating groups for X

Each X repeats twice: once for Y, once for Z

Each repeating group section has its own group header, detail, and group footer. These follow the same principles as their general purpose equivalents, but within the repeating groups. For example, to calculate aggregate data for Y for each X, use an aggregate formula in the footer for the repeating group with the Y data.

Reprinting Group Headers

As of v2018.2+, you have the option to reprint Group Header rows at the top of the page if the detail section of any given group spans multiple pages. This option is only available on Group and Repeating Group Header sections. To reprint a Group Header row when its detail section continues onto another page, click the row number in the Report Designer and select Repeat Row. If the Header has been set to repeat, two blue lines will display next to the row number.