System Tables, Group Level Management, and Bitmasks: The Report Manager

Robert Ruder

Database solutions are often evaluated based on the application’s ability to allow the user to modify the form and content of the data output. A "user-friendly" application provides tools that allow the user to make choices about what type of reports and in what style they’ll be printed. Access takes the concept of user-defined output to the ultimate degree, permitting even the casual user to build reports, queries, and forms with relative ease.

As a project matures, it becomes more difficult to stay on top of the various report needs and to avoid rewriting the entire user interface to provide new functionality. In an effort to manage and gain control of the reporting needs, I’ve developed a Report Manager interface that takes advantage of the power of Access while limiting the difficulties of maintaining various report options.

Because it’s easy to create another form, another query, or another report, I’ve often found myself creating a plethora of objects to solve what is, essentially, the same problem. During my first client/server projects using Access 2.0, I became a master at creating Johnny-on-the-spot solutions for each issue that was raised by the end users. As that project has matured and I’ve had to support the database, I’ve become painfully aware of the downside to having a single object that solves each specific problem. In fact, at times, tracking down a problem often requires extensive hunts through numerous reports, queries, and tables trying to decide where the problem is introduced. Over the past two years, my development efforts have focused on methods for streamlining the number of objects that I create while, at the same time, using the power of Access to keep track of it all for me. This article and the accompanying project demo will demonstrate three techniques that I use:

The Application System tables

Most Access users aren’t aware of the built-in system tables that Microsoft uses to track the database objects. In essence, Access is a set of relational database tables (the system tables) about a set of relational database tables (the user project). Not long after I became versed in the methods of using recordset objects in code, I came to the conclusion that I could solve the database object explosion problem by storing data about the objects rather than the objects themselves. I created my own AppSys tables, which would store relevant information about the objects in my project. Using that information, I could programmatically control the object and customize it as needed to fit any given situation.

As an example, for my Report Manager, I knew that I’d need a table that would store the data about the reports that would be available in the project. The table would also hold information about each report that might be useful when calling the report object. I settled on the table definition listed in Table 1, which I named AppSysReports.

Table 1. AppSysReports.

On the Report Manager form, the RepTitle and RepDescription fields are displayed on the form. The report object name is used by the application to call the report. The last two fields, CriteriaBitMask and ReqCriteriaBitMask, are used to store information about the report’s criteria options.

On the Report Manager form, I made the AppSysReports table the row source for the combo box. By passing the number of the column that I need to use to the combo box’s Column field, I can retrieve any of the fields in the AppSysReports table. To display the Description column from the table for the report selected in the combo box in the Description label on the form, I use this code:

 Description.Caption = ChosenReport.Column(2)

By placing this information in a table, I had made the reports a dynamic feature of the project. As new reports are developed, I can add them to the system table and have them immediately become available to my users. More importantly, because the report information is stored in a fixed structure, I can write code that uses that report rather than creating a new report. The result is fewer objects to manage. Effectively, I’m storing metadata, or data about the data. The metadata in the AppSysReports table contains information about the reports in the database. AppSysReports creates a way to centrally process reports.

Bitmasks and bitwise operators

The biggest problem with reusing reports is handling report criteria. I’ve found that most Access databases I’ve reviewed have two basic methods for dealing with criteria. Method 1 is to use parameter queries, designing the parameter prompt to elicit the appropriate response. Method 2 involves creating a form with the various criteria choices and then creating programmatic decisions about which controls will be enabled. Both solutions mean hard-coding the statements at design time and then having little or no control over their functionality at runtime. As a result, as new needs develop, new reports and forms have to be written.

What I wanted was a more general solution that would let me handle the criteria for a variety of reports without having to rebuild the user interface. I latched onto an idea that I’d discovered in my studies of Windows NT networking. When communicating information in a network packet, often a list of descriptors will be passed from one machine to another through the use of an Integer or Long Integer value. Each bit of the numeric value is given an interpretation, and the value of that item can either be 1 or 0 (True or False). By evaluating each bit and finding out whether its value is on or off, I could determine whether the defined meaning associated with the bit is true or false for the sender.

After reviewing the various reports that I was working on in my client/server application, I settled on a group of the most common criteria options. Beginning with the first byte of an integer variable, I arbitrarily assigned meaning to each bit. The resulting bitmask definition is shown in Table 2.

Table 2. A bitmask definition.

A single Integer could now tell me which criteria were used in a report. As an example, when the integer in the CriteriaBitMask field had its third bit set on, it meant that the report used Begin/End Date criteria. The ReqCriteriaBitMask worked the same way to tell me whether a criterion was required or optional. A bit set on in position three indicated that the Begin/End Date criteria had to be provided, for instance.

So, I now needed a tool that would allow me to evaluate each bit of the mask to see whether or not that particular item was necessary. To accomplish this, I turned to the "bitwise" operators of Access, which most Access developers know as the logical operators (AND, OR, NOT, EQV, IMP, XOR).

If you read the online help about the AND operator, the last section tells you how the operator also performs a bitwise operation on two integer values. If a bit in both integers is set to 1, then the result is also 1, since True AND True gives a result of True. For all other combinations (True AND False, False AND True, False AND False), the result will be 0. For instance, the values 12 and 7 share a common bit at position 3 (00001100 and 00000111). ANDing 12 and 7 together will give an integer that has the bit 3 set on. Since the two numbers share no other on bits, the result of ANDing the two integers is an integer with only bit 3 set on (00000100). This is the equivalent of the decimal number 4, by the way, so 12 AND 7 = 4:

Dim X As Integer
Dim Y As Integer
Dim Z As Integer
X = 2
Y = 7
'the next statement displays the number 4
MsgBox X AND Y

On the other hand, ANDing 10 and 7 would return a value of 2 (00001010 and 00000111 = 00000010), since the second bit is the only one they have in common.

In practice, the way to check for a particular bit is to compare the bitmask value with another integer that has only the bit that you’re looking for set. If the bit is set to 1 in the integer that you’re testing with (the mask), then the comparison will return the mask; if the bit isn’t set, then the comparison will return a 0. For instance, if I wanted to know whether the fifth bit was set in an integer, I’d compare it to a mask that had only the fifth bit set. The decimal integer that has only the fifth bit set is 16, so I use 16 as my mask to test for the fifth bit. If the integer that I’m testing has the fifth bit set and I AND it with 16, then I’ll get 16 back; otherwise, I’ll get 0. Typical code looks like this:

Dim intResult as Integer
intResult = intTest AND 16
If intResult = 16 Then
  …processing for bit set on
End If

Bitmasks can be built by using the OR operator. The OR operator sets a bit in the result to on if either of the two integers being used has the bit set on. For instance, if I had a report that used both Month and Department criteria, I’d need a bitmask with both the fourth and fifth bits set (decimal 8 and 16). I can create that bitmask with this code:

Dim intBitMask As Integer
IntBitMask = 8 Or 16

Using the bitmasks

The Report Manager interface (see Figure 1) is made up of areas. The first area is the combo that lists all of the reports in the system. The second area lists the various criteria options using controls appropriate for each data type. The last area provides a tool for customizing each report’s look.

The criteria area contains a series of text and combo boxes that can accept the values required by the criteria that I defined in my bitmask. The Tag property for each control holds two pieces of information: a decimal value to use as a mask and the name of the field that should be used. The two items are separated by a space. Initially, all of the controls have their Enabled property set to False.

When the user selects a report in the combo box, the code loops through all of the controls, comparing the mask in the control’s Tag property with the bitmask for the report. If a non-zero value is returned, it indicates that the selected report uses this criteria, and the code enables the control (after clearing any value already entered in the control). Here’s the code:

Dim i As Integer
For i = 0 To Controls.Count - 1
   If Controls(i).Tag <> " Then
        Controls(i).Value = "
        Controls(i).Enabled = False
        If (Val(Controls(i).Tag) And _
          ChosenReport.Column(4)) = _
          Val(Controls(i).Tag) Then
          Controls(i).Enabled = True
        End If
    End If
Next i

The Val function in the code is used to pull the integer mask out of the Tag property. The Val function, passed a string value, attempts to convert the string to a number. Val will process the string until it hits some character that it can’t convert. In this case, the function can only convert the integer mask at the start of the Tag property, which is all I want.

Once the user has entered his or her criteria, the code then loops through the controls, checking whether all of the required criteria have been provided. This code looks very similar to the code that enabled the controls, but it uses the ReqCriteriaBitMask field. If all of the required criteria have been entered, the code then assembles the criteria into a string that can be passed as the fourth parameter to the OpenReport method. This parameter allows you to restrict the data displayed in the report by passing a string that has the same format as the Where clause in a SQL statement. Here’s the code that assembles the statement:

Function buildcriteria()
Dim i As Integer, criteriaitem As String
Dim criteria As String
Dim tCtrl As Control
    
For i = 0 To Controls.Count - 1
 Set tCtrl = Controls(i)
  If TypeOf tCtrl Is ComboBox _
  Or TypeOf tCtrl Is TextBox Then
   If tCtrl.Enabled And tCtrl <> " Then
    criteriaitem = Mid(Controls(i).Tag, _
    InStr(1, Controls(i).Tag, " ") + 1, _
    Len(Controls(i).Tag))
                
    Select Case criteriaitem
    Case Is = "BeginDate"
     criteria = _
      "Date Between #" & Controls(i) & "# "
    Case Is = "EndDate"
     criteria = _
      criteria & "and #" & Controls(i) & "# "
    Case Else
     If criteriaitem <> " And criteria <> " Then
      criteria = criteria & "and " & _
      Mid(Controls(i).Tag, InStr(1, _ 
      Controls(i).Tag, " ") + 1, _
      Len(Controls(i).Tag)) _
      & "= " & Controls(i)
     ElseIf criteriaitem <> " Then
      criteria = criteria & _
         Mid(Controls(i).Tag, _
          InStr(1, Controls(i).Tag, " ") + 1, _
          Len(Controls(i).Tag)) & "= " & _
         Controls(i)
     End If
    End Select
   End If
  End If
 Next i

The code extracts the field name from the Tag property, adds the appropriate comparison operators and field delimiters, and concatenates the result to the existing string. This is the most specific data coding that I do in the project because it focuses on the type of data that’s available in the tables.

Formatting the report

One other feature of the Report Manager is some code that lives in the report itself. Each report’s Report_Open procedure refers back to the Report Manager form to pick up some formatting options provided by the user. The report uses the Report Title field from the AppSysReports table unless the user has entered a different title in the txtRepTitle text box on the form. The code also checks the current status of three check boxes to see whether or not page numbers are going to be shown and, if so, whether they’ll appear on the top right or the bottom center of each page. Here’s that code:

Dim tmpForm As Form
Dim i As Integer, tCtrl As Control
Set tmpForm = Screen.ActiveForm
    
If tmpForm.Controls("txtRepTitle") <> " Then
 lblReportTitle.Caption =  _
 tmpForm.Controls("txtRepTitle")
Else
 lblReportTitle.Caption = _
 tmpForm.Controls("cboChosenReport").Column(1)
End If 

Select Case tmpForm.Controls("optPageNumbers")
Case Is = 1
 'Do nothing; page numbers already hidden
Case Is = 2
 'Display page header
 Section(3).Visible = True
Case Is = 3
 'Display page footer
 Section(4).Visible = True
End Select

The code also manages the report’s GroupLevel and Section array. When you’re creating reports and you use the Grouping and Sorting tool, for instance, you’re creating entries in the GroupLevel array for your report. The GroupLevel has a ControlSource that determines the data to be displayed at that level.

A report is broken up into sections, each represented by an entry in the Section array. Some sections are built into a report, and others are generated by the design selections that you make. The detail section for the report is Section(0), the report header is Section(1), the report footer is Section(2), the page header is Section(3), and the page footer is Section(4). The numbering then goes on from there to include all of the GroupLevels. A report with a single group with both a header and a footer would have Section(5) as the header for the group and Section(6) as the footer. All of these features are fully controllable from code, so even after you’ve selected a choice for grouping and sorting, in code you can always program other options.

In the sample database that’s included in the accompanying Download file, I created the report with its Grouping and Sorting options set to the most common options I could imagine. Then, using the Layout Options section of the Report Manager, I check to see whether the user wants to have grouping on the report. By default, I leave each section’s "visible" property set to False. If the user selects a group level on the Report Manager form, I make that group level visible in the report. I also set a text box in the header to display the appropriate field for the grouping level.

The following code handles the first level of grouping. If the user select the Top Level check box, the code sets the control source for the GroupLevel(0) to group on that data. The routine then sets the control source for the text box in the header to the field name that the level groups on in order to display the field in the current set of records. The last two lines make the header and footer sections for the first group level visible:

If Screen.ActiveForm.Controls("chkTopLvl") Then
 GroupLevel(0).ControlSource = _
   tmpForm.Controls("cboTopLvl").Column(1) & "ID"
 
 txtTopLvl.ControlSource = _
   tmpForm.Controls("cbotoplvl").Column(2)
        
 Section(5).Visible = _
    tmpForm.Controls("chkTopLvlHdr")
 
 Section(6).Visible = _
    tmpForm.Controls("chkToplvlFtr")
End If

Reformatting reports on the fly is just one example of Access’s abilities. I believe that Access is often overlooked as a development platform because, on the surface, it appears to be an "ease of use" application. I believe that the "ease of use" factor lets me deliver more powerful applications by combining the user tools with my own code. Creatively applying techniques like system tables, bitwise operators, and report objects means that I can deliver much more flexibility and power to the user.

Download RPTMNGR.exe