Designing Reports with Grouping and Sorting
Build reports that organize data into meaningful groups, with headers, footers, page numbering, and totals that print correctly.
By the end of this lesson you can
- Create a report with grouping and sorting
- Add headers, footers, and page numbering
- Add group and report totals
- Prepare a report so it prints legibly
Lesson Notes
Read through the key concepts before you try the challenge.
Grouping is what makes a report a report
You produce the quarterly supply report at Lakeside Medical Associates.
Your first attempt is 340 rows printed in entry order across eleven pages, with column headings only on page one. It contains every fact the manager asked for and answers none of her questions, because nothing is organized.
Your task: Use grouping, sorting, and totals so the report answers questions instead of listing data.
A report differs from a datasheet by being organized for a reader. Grouping collects records under headings; sorting orders them within each group; group totals summarize each section. Those three things turn a list into something someone can actually use.
| Section | Prints | Put here |
|---|---|---|
| Report Header | Once, at the very start | Title, date range, practice name |
| Page Header | At the top of every page | Column headings |
| Group Header | Before each group | The group's name, e.g. the category |
| Detail | Once per record | The record's fields |
| Group Footer | After each group | Subtotals for that group |
| Page Footer | At the bottom of every page | Page numbers |
| Report Footer | Once, at the very end | Grand totals |
Totals are added by placing a text box with an expression such as =Sum([TotalCost]) in the appropriate footer. Placed in the Group Footer it totals that group; placed in the Report Footer it totals everything. The same expression means different things depending only on where it sits, which is why understanding the sections matters.
Where should column headings be placed so they appear at the top of every printed page?
Challenge
Apply what you've learned in this lesson.
Build a report someone could actually take into a meeting.
- Build a report on your supplies data grouped by category and sorted by item name within each group.
- Add a report title in the Report Header, column headings in the Page Header, and page numbering in the Page Footer. Confirm in Print Preview that each appears where you expect across at least two pages.
- Add a subtotal of total cost in the Group Footer and a grand total in the Report Footer. Verify the subtotals sum to the grand total.
- Adjust the layout until it prints without any column running off the page, then write two sentences on what you had to change.
Finished this lesson?
Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.