Custom Report Groups, Total, and Collapse Options
When you create a custom report, you can group the data so that accounts or entries that share a common value are together. You can also show totals for each group or collapse each group to a single line for easier viewing.
From the report setup screen, under Group Report By, select an option to group data in your report. You can select any field that appears in your report. You can also group by account or household.
When you select a group option, the report automatically creates a header in the first column to show the value that the items in the group share. For easier viewing, make the field that you use to group your data the first column in the report.
When you group by account, the following is an example of the report.
Tip: The fields you select to include in your report are the available options in the Group Report By field. Therefore, you may want to select this after you select the fields you want to appear in the report.
-
You can use fields with multiple values to group your report. Each combination of values are a group.
-
Do not group by free form text fields. These entries do not have standard names because users manually enter the values in these fields. This is likely to cause a group for each variation of a field.
-
If you have aggregated a field, you cannot use the same field to group the data. Aggregates calculate based on all of the transactions in a group. Since the calculation occurs after the grouping is applied, the field cannot also be used to group the data.
When you select a group option, the groups sort according to these rules.
| If grouping by | Groups will be listed |
|---|---|
| Account | Alphabetically by account's Sort Name |
| Household | Alphabetically by the head of household's Sort Name |
| Text Field | Alphabetically by the field value |
| Date Field | Chronologically by the field value |
| Currency Field | Numerically by the field value |
| Number Field | Numerically by the field value |
In this example, the report is grouped by fund and the funds appear in alphabetical order. Within each group, the entries sort by date.
After you select a group option, you can select to show group totals or collapse groups.
-
When you select Show Group Totals, a total line appears under each group to show the sum of any currency and number fields in the report.
When you group by fund and select to show the totals, the following is an example of the report.
-
When you select Collapse Groups, a summary of any currency or number fields appears on the collapsed, single line.
When you group by fund and select to collapse it, the following is an example of the report.
Date or text fields that vary from one entry to another within a group cannot be summed. Therefore, you should not include them in your report when you collapse groups.
If you collapse groups, the groups automatically sort based on the rules in the above table. However, when you select sorting options, they override the default sorting rules.
Tip: You can also export the report as a .mrg or .xls file, open the file in another program (such as Microsoft Excel) and manipulate groups there. For example, you can do this to group data by a range of dates, currency, or number information.