Sort, Filter, and Group in Reports

Last week we looked at sorting and filtering in forms.  This week we'll look at sort, filter, and group in reports and see the new tools we've got to make building reports quicker and easier.  The next post will look at the new interactive reports (instead of print preview, there's now a "report view") and how sorting and filtering work there.  There will be a week off next week while I'm out of town on vacation, so this will be at the end of the month.

Grouping and Sorting in Reports

Access has always supported grouping in reports, but has new tools to make this easier in Access 2007 (that's the real name, and I need to wean myself off of "Access12").  The quickest way to create a group in a report is to simply right click in the column you want to group and select "Group on {column name}" from the context menu.  In the example below we're grouping on the Assigned to column:

This creates the grouping level, and opens the "Group, Sort, and Total" pane to allow you to edit the group.  Sorting behaves in just the same way, and the same controls are present.  In fact you can turn a Group into a Sort by simply turning off the header and footer rows for the Group.  Since they're so similar, I've just shown grouping in this example.  You can click on the image below to see the report grouped:

(Click image to enlarge)

You can also manually open the grouping pane from the ribbon by clicking in the contextual ribbon for Report Tools / Formatting:

When the group is created, you can edit its structure by clicking on the entry in the grouping pane and editing its settings.  These settings are displayed in a natural language string with controls to edit appropriate portions, much like the Outlook rules wizard.

(Click image to enlarge)

You can use the grouping pane to add another grouping level or to sort by another column as well.  You simply click on "Add a Group" or "Add a Sort" buttons and then select which field to use.  Of course, you can right click and use the context menu as described above as well.

(Click image to enlarge)

The new group is added to the report and again, the new line in the pane is completely editable.

(Click image to enlarge)

Groups can be moved by simply dragging and dropping its line in the pane, so it is simple to change a report or to use one report to create another with different sorting and grouping. 

After the group's line is moved, the report is updated to match the order of the pane:

(Click image to enlarge)

Filtering Reports

We discussed Filtering in the last post, and the same tools apply to reports, so I won't go into them here:

Report Totals and Summary Reports

It is simple to apply totals to a group as well, and this can either be done from the pane or from the ribbon.  For number fields you can select from a set of numerical operations, for other fields you can count either the total number of rows or the number of non-null rows.

(Click image to enlarge)

Applying totals adds a new footer row to each group, and shows the results of the operation in that row:

Creating summary reports is as easy as building the report you'd like and then selecting "Hide Details" from the ribbon.

(Click image to enlarge)

Next Post

I'll try to sneak in a small post on filtering the Navigation Pane before I leave town, but the next regular post will be in 2 weeks and will cover the new Report View, which provides interactive reports with Sort, Filter, and Drill Through functionality online.