AdHoc Report Builder

Tom Heisler, MCSD, and Dennis Hollenbeck, MCSD

You've built every report that management has requested. So what happens the first time they use your product? They want more (or different) reports! Typically, the first thing an advanced user wants is more selection criteria. Here's how to build a table-driven, graphical, reusable Where expression generator that works on any Access report.

Can you predict how your users will want to select data on a report? Typically, every time new selection criteria are allowed, it requires altering a form or adding parameters to a query. You can save yourself a lot of work if you build software flexible enough to allow power users to make their own decisions on which data they want to see in a report. Here's a reusable tool to give users the power to build custom report filters.

Access allows you to filter a report without modifying its design by using a Where expression in the DoCmd.OpenReport method. Our AdHoc Query Builder provides a simple, easy-to-learn interface for building Where expressions. First, we'll show you what the user sees, then we'll show you how it works.

What the user sees
One of the advantages of the AdHoc Query Builder is that a user only has to learn one interface to customize any Access report. The alternative is to have your users build their own ad hoc Where expressions, something that you should only expect from your intermediate and advanced users. At a minimum, when building their own Where clauses, the users must understand how to use logical operators (And, Or) as well as comparison operators (=, <, Is Null, Like).

To create a customized report using our tool, the user first selects a report group from the combo box, then a report from that group in the form's list box. Once a report is selected, the Selection Criteria subform on our form is enabled. The user can then fill out the Selection Criteria grid by specifying the And/Or operator, field name, comparison operator, and value that he or she wants. There's no set limit on the number of criteria that the user can provide. Finally, the user clicks on the Print or Print Preview button to open the report. Figure 1 shows a typical report setup in our AdHoc Query Builder.

Under the hood
The AdHoc Query Builder is table-driven system, so adding a report to the system just consists of adding an entry to the ReportList table. The data in this table fills both the Report Groups combo box and the RepFormat list box on the form. The table requires you to specify the report name, the report group to which the report belongs, and the query used for each report.

When the user selects a report from the RepFormat list box, several things happen. First, the report description appears below the report list box. Next, the RowSource property of the FldName combo box in the Selection Criteria subform is set to the name of the query supplied in the table. This combo box's RowSourceType property has already been set to "Field List." This little-used setting fills the combo box list with the names of fields in the table or query specified in the RowSource property. When a combo box is used in this way, the RowSource can't be a SQL statement. Pressing the Print or Print Preview button on the form calls the function PrintEm, which assembles a Where expression, builds a caption (see the sidebar "Adding Captions to Reports"), and opens the report.

Components
Now we'll take you on a detailed tour of the components involved. There are only five objects to explain: the tables ReportList and tmpReportSelections, the forms ReportDialogAdHoc and ReportDialogAdHocSub, and the module ReportCaption. These components rely on each other, but this whole system is transportable to other MDBs simply by importing these objects.

Table ReportList
This table holds the list of reports available to the AdHoc Query Builder. To add a report, simply add a record to this table. The field ReportGroup is used to fill the combo box ReportGroup. ReportName holds the name of the report object. Together, ReportGroup and ReportName make up the primary key of the table, which allows the same report to show up in more than one group. The ReportQuery field holds the name of the QueryDef or TableDef that will supply the field names for the Selection Criteria subform. ReportDescription is the text the user will see in the list box when selecting a report, and it should be kept short. ReportNotes will show up below the ReportDescription once a report is selected.

Table tmpReportSelections
This table holds the user's selections from the subform ReportDialogAdHocSub. It's purged every time the Ad Hoc form is opened, or when the Clear button is pressed. Each field on the form corresponds to a field in this table.

Form ReportDialogAdHocSub
The user's entries in this subform control how the Where clause passed to the report is to be created. There are five fields in the form:

FldConjunction holds the conjunction operator (And, Or). The default is And. The conjunction in the first record is ignored. The conjunction is used to string together all of the criteria that the user enters.
FldName holds the name of a field in the table or query selected in ReportList. All of the fields available will show up in the combo box and will appear in the exact order they appear in the query or table. The query referenced in the ReportList table doesn't have to be the same query the report actually uses. As a result, if you want to present the field names in an order different from the one that FldName uses, you can prepare a separate query with the same fields and put the fields in whatever order you want.

FldType holds a description of the field type and is updated whenever the user selects a field in FldName. We determine the field type by getting a reference to the query pointed to by the FldName's RowSource (remember, that's set to the query used by the report). We then use the field name specified in FldName to get a reference to the field that the user has selected. Here's a code snippet that shows how that works:

 Set qryTmp = dbTmp.QueryDefs(Me!FldName.RowSource) 
 intTmp1 = qryTmp.Fields(Me!FldName).Type 
 Select Case intTmp1 
   Case DB_DATE: 
     Me!FldType = "Date" 
   Case DB_TEXT: 
     Me!FldType = "Text" 
 ...etc...

FldComp holds the comparison operator. The AdHoc Query Builder supports =, >, >=, <, <=, Like, Is Null, and Is Not Null. Note that all the comparison operators are available regardless of the field type, though some might not apply to all field types.
FldValue holds whatever text the user enters. No validation is applied to this text, so if "ABC" is entered for a date, an error will occur when the user attempts to open the report.

Form ReportDialogAdHoc
The ReportDialogAdHoc form provides a consistent, easy-to-learn user interface. The table tmpReportSelections is cleared in the Form_Open event handler (pressing the Clear button also clears the table). The ReportGroup combo box contains a list of the distinct ReportGroup values in ReportList. Selecting a report group fires the combo box's AfterUpdate event, which populates the RepList list box with the list of reports. When a report is selected, the field below the list box is updated with the name of the report.

When the user presses either the Print or Preview buttons, the routine PrintEm is called, and the Access constants for print or preview are passed to it as an argument. This allows PrintEm to be used for both print and preview. The method PrintEm builds a Where expression, constructs a report caption, then opens the selected report. With the Where condition built and stored in strWhere, the report name in strDocName and the print mode in intPreview, the call to print the report looks like this:

DoCmd.OpenReport strDocName, _ intPreview, , sWhere

Since Access uses different delimiters in SQL statements for different data types -- "'" (single quote) for strings, "#" for dates, and none for numbers -- the AdHoc Query Builder chooses the correct delimiter based on the data type of the field. These delimiters would make the dynamic report caption difficult to read, so they're not included in the caption. Since the first conjunction in the Where expression is ignored, the AdHoc Query Builder strips it off. The PrintEm function then opens the selected report in the view passed into the function as an argument.

Enhancements
The AdHoc Query Builder has been used by our clients for several years, and they all appreciate the ability to tweak their reports without calling us to add selection criteria. Nonetheless, we've thought of some ways to improve the AdHoc Query Builder.

Currently, the AdHoc Query Builder doesn't perform any validation on the contents of the FldValue text box in the subform. Validating this input against the data type would be handy. In addition, the AdHoc Query Builder would be more powerful if users could add their own parentheses to the Where expression. We hope to add those improvements in the future.

Building robust, flexible, reusable software is one of the ways to remain profitable in the software business. The AdHoc Query Builder can be used for almost any set of reports in any Access application, reduces time spent building reports, is easy to maintain, and is fully reusable.

Download REPMNGR.exe

Tom Heisler, MCSD, serves as vice president of n-Dimensional Software, Inc., and has way too much fun. tom@ndimensional.com.

Dennis Hollenbeck, MCSD and reformed Boeing 737 driver, is president of n-Dimensional Software, Inc., an Anchorage, AK-based ISV that produces the Access Upsizer and Access Report Upsizer. dennis@ndimensional.com.

Sidebar: Adding Captions to Reports

When you add flexibility to your reports, it's easy to add confusion as well. To minimize confusion when a user can specify a custom Where expression, it's a good idea to add a dynamic caption to your report that shows the user what selection criteria he or she used.

We originally built our caption builder in Access 2.0, but it works equally well in Access 95 and 97. There are three procedures that we use while building the Where expression for the report that we also use to build a caption. First, we call RPTC_ClearCaption to empty the string variable that we use to hold the caption. As each set of criteria is added, we call RPTC_AppendCaption to add to the caption. In the report, we place a text box at the top of the report to display the caption and set its ControlSource property to "=RPTC_GetCaption()" to retrieve the string containing the caption. Here's the code from the module that holds these routines:

Option Explicit Private RPTC_ReportCaption As String Sub RPTC_AppendCaption(ByVal ToAdd As String) RPTC_ReportCaption = RPTC_ReportCaption & ToAdd End Sub Sub RPTC_ClearCaption() RPTC_ReportCaption = "" End Sub Function RPTC_GetCaption() As String RPTC_GetCaption = RPTC_ReportCaption End Function

Sidebar: Foundation Concepts: Where Expressions

The AdHoc Query Builder assembles Where expressions to filter the records in your report. A Where expression is a clause in a query written in Structured Query Language (SQL). A typical SQL query has several parts (some are optional), which must be in this order:

Select clause -- Determines what fields of data to return from the query.
From clause -- Determines the tables from which to extract the data.
Where clause -- Limits the number of records returned.
Group By clause -- Determines how the records are totaled and summarized.
Order By clause -- Determines the order in which the records are returned.

This query has a Select, From, Where, and Order By clause:

Select PartNo, Qty, Description From Inventory Where Qty = 0 Order By Description;

This SQL statement would return the part number, quantity, and description for all parts from the inventory table where the quantity was zero. The records would be sorted by description in order from A to Z.

When you open a report in Access, your report will get data from Access using the query stored with the report. The OpenReport method of the DoCmd object allows you to specify a Where expression that limits the records returned by the query. If you specify a Where expression, Access will temporarily insert the expression into the query before it runs.

Here are some more examples of how the Where clause can be used:

Records where Description starts with `A' will be selected. Note that strings must be enclosed in single or double quotes:
Where Description Like 'A*'
Records are selected where Sales are greater than or equal to $100,000, or the Country is France, Canada, or USA:
Where ((Country In ('France', 'Canada', 'USA')) Or (Sales >= 100000))
Records where the value of the StartDate fields falls between May 12, 1994 and June 30, 1996 inclusive will be selected (that is, a record containing 5/12/1994 would be included in the report). As the code shows, dates must be enclosed in pound signs (#):
Where StartDate Between #5/12/1994# And #6/30/1996#