18 Tips to Build a Great Report Filtering User Experience in Microsoft Access

Report filtering is an underappreciated contributor to an Access application's overall user experience (UX).

NOTE: By report filtering UX, I mean the form(s)/ribbon(s)/input box(es)/parameter prompt(s) that your application uses to get input from the end user to show the information they want–and only the information they want–on your Access reports.  I'm not talking about the performance of the filtering itself (e.g., are you filtering on indexed columns, etc.).  While that's important, it is not the focus of this article.

In my opinion, a great report filtering UX should be:

• Intuitive
• Consistent
• Simple
• Powerful
• Efficient
• Beautiful

And (generally speaking) in that order.  In other words, it's more important for the user experience to be intuitive than consistent.  It's more important to be simple than powerful.  

Occasionally, there are situations where you need to adjust the order.  

For instance, if the report has a massive amount of underlying data, a powerful filtering UX may trump an intuitive one.  If the report gets run dozens of times per day, an efficient filtering UX may be most important.  If the application will be running on a kiosk at a contemporary art gallery, a beautiful interface may be a requirement.

In general, though, the above order should serve you well as a starting point.  You should have a good reason to reprioritize the list.  Besides, with a few notable exceptions (e.g., simple vs. powerful), these characteristics are not mutually exclusive.  There's no reason you can't have a report filtering UX that is intuitive, consistent, simple, efficient, and beautiful.

Let's explore these concepts in more depth.

Intuitive

The ultimate test of an intuitive UX is if it requires no explanation.  No user manual.  No tutorial.  Ideally, not even a tooltip or status bar message.  I'm not saying your application shouldn't include those things.  I'm just saying it's better if they are not needed.  

Some tips to make your report filtering UX intuitive:

• Use matching labels for the filtering form and the report.  For example, don't use a combo box labeled "Vendor" to filter a report column named "Company."
• Provide immediate feedback as the user fills out the form.  For example, if you are looking for a date in a text box, set the Format property of the control to a date format.  
• Consider progressive filtering.  Let's say you have an invoice report that you are filtering by date and vendor.  After the user enters a start and end date, you could update the row source of the vendor combo box to show only those vendors with invoices during the user entered date range.

Consistent

This goes hand-in-hand with being intuitive.  The more consistent you are among your report preview forms, the more intuitive the UX will seem to the end user because it behaves the way they expect.

Here are some relevant tips:

• Use the same color scheme throughout the application for your report filtering UX.
• Maintain tab order of common filters.  For example, every preview report form that includes a start and end date, could include those two fields as the first two in the tab order.
• Reuse forms.  While I rarely reuse report filtering forms in my own applications (see Simple section below), there's no better way to ensure consistency among filtering forms.

Simple

Keep It Simple, Stupid.  

The paradox of choice tells us that offering too many options actually makes it harder for users to make decisions.  Rather than allow users to filter by every possible field, identify a subset of fields that will help them achieve an 80% solution.

Incidentally, this is why I tend to avoid reusing forms when filtering reports.  When you do that, there is a natural inclination to include all the possible filtering criteria on a single all-purpose form, even though several criteria may not apply at all to a given report.  

For example, imagine an invoice report that you want to filter by invoice date and vendor; an inventory report that you want to filter by product; and a shipping report that you want to filter by ship date, product, and customer.  A single form-to-rule-them-all would include the following fields:

1. Invoice start date
2. Invoice end date
3. Vendor combo
4. Product combo
5. Ship start date
6. Ship end date
7. Customer combo

That's seven fields for every report.  But if each report had its own preview form, no single form would include more than four of those controls.

Powerful

A powerful report filtering form allows your users to filter data in whatever way they may need.

This attribute is often in tension with simplicity.  If you have a ten-column report, how do you allow a user to filter by each of those columns without overwhelming them with choice?  In most cases, we simply acknowledge there are tradeoffs, and we work with our clients to decide which fields must be filtered and which we can skip.  

However, there are techniques we can use to make our filtering more powerful without making it completely overwhelming, such as:

• Showing a small subset of filtering fields by default, but providing an [Advanced...] button/link/tab that exposes additional filtering fields for special situations.
• Using optional cascading combo boxes, where the second combo box doesn't even appear unless the first is populated.  For example, imagine a company that makes products that may come in multiple configurations.  The inventory report would include a product combo box.  If the user selected a product with multiple configurations, then the configuration combo box would appear (and of course be filtered to show only the configurations that apply to that product).
• Include an export to Excel option so that the user can slice and dice the report's data however they want.  This is especially powerful for financial data or for data where the user is trying to find a particular record.

Efficient

An efficient UX means that the user spends no more time than necessary filtering their report.

Here are a few tips to make your report filtering UX as efficient as possible:

• Set the tab order so that the user can intuitively navigate the form without a mouse.
• Avoid input masks for dates, as they tend to require more keystrokes than setting a date-specific Format property.
• Provide a combo box of common date ranges to populate start/end date boxes with values like: "last month", "last quarter", "year to date", etc.  Set focus to the next control in the tab order after the date pair when using the combo box to populate the date range.
• Leave the report filtering form open when previewing the report.  When the user closes the report, they can tweak the filtering criteria and easily re-run the report.
• In special cases, consider allowing the user to save and restore filtering criteria.  This typically makes sense only for powerful filters that include half a dozen or more filtered fields.

Beautiful

The overwhelming majority of professionally developed Access apps are line of business (LOB) applications.

In that context, functionality far outweighs appearance.  That's why the UX's beauty is at the bottom of the priority list... But it's still on the list.  

There are a few simple things you can do to make your report filtering forms more beautiful:

• Take the time to align your text and combo boxes, as well as their associated labels.  The legacy shortcut keys for control alignment can speed up your development process in this regard.
• Use negative space.  Instead of jamming all your controls right on top of each other, spread them out a bit.  
• Use the correct type of font for each control: sans serif for the labels and buttons; monospace for text and combo boxes.

Cover image generated by DALL-E-3.