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.
In the Report Wizard, Sorts is the third tab. To create a sort, drag-and-drop a data object into the sorts pane. Select Ascending or Descending from the Sort Order dropdown. A formula can be added to the sort by clicking on the Formula Editor icon. Add as many sorts as desired, and change sort priority by using the Move Item Up and Move Item Down arrows to move the sort priority up and down the list.
In the Report Designer sorts may be added, deleted or changed from the Sorts dialog:
- (pre-v2021.1) Open the Report Settings menu, then click Sorts.
- (v2021.1) Click the Sorts or icon on the toolbar
- (v2021.2) In the Add Section menu, click Add Sort
The Report Sorts dialog works much the same as the Report Wizard sorts tab does.
To add a sort, review the Advanced Reports: Sorts article. Once you've sorted on a field, you're ready to group on that field.
NoteNested groups should reflect the sort order. The outermost group header should be the highest priority sort, and the footer order should be reversed.
Sorting By Formula
To sort and group by information that may not be contained within an individual data field, Formulas can be used. Refer to the article on Formulas for more information including descriptions of each formula.
Click the Add Formula button or the Formula Editor icon in either the Report Designer or Report Wizard to open the Formula Editor window:
- Add a data object by dragging and dropping it into the formula box, double-clicking it or entering 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, double-clicking it or entering it manually.
For example, consider a data field containing a full date and time, {Orders.OrderDate}
. To sort by sales of each month of the year over a multi-year period, the formula Month({Orders.OrderDate}) will return only the month component of each date. Refer to the article on Formulas for more information including descriptions of each formula.
Adding Group Sections
The following section applies to pre-v2021.1 versions of the application. For v2021.1+, refer to the Advanced Reports: Sections article.
In order to add group sections, click anywhere in the sections column of the Report Designer. From here, select Add Section and then Group Header.
In the Group Header menu, use the dropdown to select the proper sort field. For this example report, Categories.CategoryName
has been selected.
NoteGroups can be sorted by formula instead of a Category or Field by adding a formula to the report Sorts, then selecting it in the group dropdown menu.
After adding the Group Header, repeat the same steps above to create a group footer by selecting Group Footer from the Add Section menu.
Group Header and Footer Content
Headers and Footers are designed to display content that will appear once per unique value in the group. Group Headers appear above the group detail, and Group Footers appear below the group detail.
(pre-v2019.1.4) Group Footers may be used to perform aggregate functions on the group detail.
(v2019.1.4+) Group Headers and Footers may be used to perform aggregate functions on the group detail.
In this example, there is a group header on Categories.CategoryName
, and there are two rows in that header. Row #3 contains the Categories.CategoryName
field, which will repeat once for each unique Category Name in the Categories object. Row #4 contains column titles for the details section. They will appear once for each unique Category Name in the Categories object.
In the footer section, the average of the products unit price in each category is calculated with a formula. Like the values in the Group Header, this average will appear once for each Category Name in the Categories data object.
NoteBecause headers are not designed for content that will produce many different results for each group, it is best to select content that will only change with each group. For example, if
{OrderDetails.Quantity}
is placed in a group header, it will only display the first record for each group break. Additionally, placing this field in a group footer will display only the last record in the group.
Upon execution, the report now shows each food category name and displays aggregate information for the unit prices in each category.
Reprinting Group Headers
Beginning in v2018.2 of the application, the reprint Group Header rows can be reprinted at the top of the page if the detail section spans multiple pages. This option is only available on Group and Repeating Group Header sections. To reprint a Group Header row across multiple pages, 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.