How to fix grouping on a report

The issue is caused by the current combination of grouping and sorting levels. “Sale of Goods” is grouped more than once because there is at least one additional grouping or sort level that splits the data after the first subtotal.

To get all “Sale of Goods” records together before “Booth Rent,” adjust the grouping and sorting so that:

1. There is only one grouping level on Category.
2. All other sort levels are below that Category group and do not introduce additional grouping.

Use the Group, Sort, and Total pane to verify and fix this:

1. Open the report in Layout View or Design View.
2. On the ribbon, select Report Design and then Group & Sort to open the Group, Sort, and Total pane.
3. In the pane:
   • Confirm there is a single Group on line for the Category field.
   • If there is any second Group on or a sort that effectively groups by another field before Category, move Category to the top using the up arrow so it is the first grouping level.
   • Remove any extra grouping levels that are not needed (click the delete button on that row).
4. Under the Category group, add a Sort by line for the field that should control the order of transactions within each Category.
5. On the Category group row, click More and set Sort order to Z–A so that “Sale of Goods” appears before “Booth Rent.”
6. Ensure no other grouping level is defined on the same field or on an expression that splits Category (for example, an expression that groups some “Sale of Goods” rows differently).

If you need to group or sort on a calculated value instead of a field, use an expression in the Group, Sort, and Total pane so that all “Sale of Goods” rows share the same group expression value.

After these changes, all “Sale of Goods” transactions will appear together in one group and subtotal, followed by “Booth Rent.”

References:

• Create a grouped or summary report
• Learn to build an expression