Basic Reporting Part V FAQ: How to Add Grouping to a Report
Watch the Part I: Basic Grouping Video (written instructions below the videos):
Watch Part II: Advanced Grouping Techniques Webinar Recording
- By adding grouping to a report, users can:
- Add section headers, such as pictured below. This list of contacts is grouped by state that each contact lives in;
- Roll up information that appears multiple times on a report so that it only appears once. For example, in the sample report below, the donor names and addresses appear repeated in each line (the first picture). Adding grouping condenses the repeated information to a single line (the second picture);
- Add summations to reports, including totals, averages, and counts. In the picture below, each donor’s total amount donated is summed up and displayed on the report.
- Users can group on any field—a report can be grouped by contact name, household ID, address, donation date, donation amount, event name, etc. Users can also group based on formulas (for instance, using the “if/then” function in grouping can allow the user to set the report to group by one particular field and then another field in instances of the first field being blank (for instance, group by household ID if the contact is in a household and group by contact ID if the contact is not in a household).
To Add Grouping in the Advanced Reports Interface:
- Please note: in the advanced reports interface, you must first sort the report by the field you want to group on. For instance, if you want to group donors by state, you must first the report by state. If you want to group by donation amount, you must first sort by donation amount. If you wish to group by household, you must first sort by household (household ID). Etc.
- To get started, while in “edit report” mode, right click anywhere in the “Section” column (furthest column on the left).
- Select Add Section from the pop up menu.
- Select Group Header to add information before/at the start of each new group. Select Group Footer to add information after/at the end of each new group (please note: mathematical functions such as averages, counts, and totals/sums can ONLY be placed in a group footer; they cannot be placed in a group header).
- Select the field you wish to group on from the Sort Categories section of the Group Header/Footer menu. Only those fields you have added to the “sorts” tab of the report will be selectable here. Then click OK.
- A new row will have been added to your report for your new group header/footer.
- Drag any fields you want to see in the header to the header row. For instance, in this report, we are sorting our report by State and wish to add a header that displays the State at the start of each new group, so we have added the State field to the header row.
- To make your headers stand out more, if desired, you can make the font larger or even change the color, using the font tools on the toolbar.
- If you wish to add totals, averages, or counts to your report, once you have added the group footer to your report, select the cell where you want the summation to appear, then click on the formula/function editor button (“fx”) on the toolbar.
- This will open the Formula Editor screen.
- Insert the appropriate formula from the Aggregate Table (AggSum provides a total, AggAvg provides an average, Agg Count provides a count of the records) and then click OK.
- You will see the formula for the total/average/count in your footer. Now, when you run the report you will see a total per grouping.
- To get rid of repeated information on a report, move the information you only want to see once to the group header or footer, then delete or suppress the Details row. Right click in the Row Number of the row you wish to delete or hide, including group headers or footers that you have added to your report.
- Then choose Delete Row from the pop-up menu to remove the row/section from your report completing. Choose Suppress Row to hide the row on the executed report (the row will still be visible in edit mode, but will be grayed out).
- Now when we run the report, only the Group Footer, which contains contact name and donation total amount, shows.
- Using group headers/footers to suppress duplicates is covered in more detail in our “Creating a Mailing List Report” FAQ/tutorial.