Report Builder: The Local ReportControls Table

Mike Wolfe -

This is one of a series of articles detailing my Advanced Report Builder.

The available fields for my advanced report builder are stored in a local table named ReportControls.  Rows with asterisks are required fields.

Field Name Data Type Description
* ControlID Long AUTONUMBER "VARIABLE" fields need to be saved in report template; they are the only fields that can be changed by the user
* Hidden Boolean [No] STATIC Whether this field is hidden from the user (but still available for 'Hidden' built-in reports)
* LabelText Text (255) STATIC Text for the label that is displayed in the page header
* ControlSrc Text (255) STATIC ControlSource of the control for the detail section
GrpHdrCtlSrc Text (255) STATIC (Optional) ControlSource of the control when used as Group Header (allow for short/long versions)
* LabelAlign Long [0] STATIC Set label's TextAlign Property (Integer, 0 - 4)
* TextAlign Long [1] STATIC Set control's TextAlign Property (Integer, 0 - 4)
Format Text (50) STATIC (Optional) Set control's Format Property
* CanGrow Boolean [No] STATIC Whether the column is allowed to grow (set to Yes for long, variable-length fields such as comments)
* DataType Text (10) STATIC "Text","Numeric","Date"
* CanSum Boolean [No] STATIC Whether the column can sum for reports (generally Yes for numeric fields, No for others)
SumCtlSrc Text (255) STATIC (Optional) Custom ControlSource of the control for the group and report footers (used for Wtd Avg's, etc)
Sequence Long VARIABLE Order to print controls on report (Null if not printed on current report)
GroupOn Boolean VARIABLE Whether to group on this control
DisplaySum Boolean VARIABLE If grouped on this control, whether to display summary data for this grouping level
Sum Boolean VARIABLE Whether to show field in group footers as sum
Sort Text (4) VARIABLE "ASC", "DESC", Null
Sample rows of data from my local ReportControls table. Open image in new tab to see the full-size screen shot.

STATIC vs. VARIABLE Fields

The table is divided into two different types of fields:

  • STATIC: these fields are set at design time and do not change
  • VARIABLE: these fields are set at runtime based on the report template

The "static" fields (shaded yellow above) are set by the developer to define how each custom control behaves when it is included on a report:

  • What's the caption of its associated label?
  • What is its ControlSource?
  • How should the label be aligned (left/center/right)?
  • How should the text be aligned (left/center/right)?
  • How should the value be formatted?
  • Should the value be allowed to grow?
  • Does it make sense to allow the value to be summed?
  • If so, does the value require a special control source for the sum control in the group/report footer section?
  • If the report is grouped on the field, what text should appear in the group header? (Since we have the full width of the report available in the group header, we often want to show a full description of a value, whereas if the control is in the detail section, we want to show an abbreviation or code to save space.)

The "variable" fields (shaded blue above) are updated just before generating the report at runtime following this algorithm:

  1. The five VARIABLE fields are cleared of data for all rows
  2. The rows with the report controls that comprise the current report template have their VARIABLE field values set to match the report template (see section 4 in the Report Builder Form screenshot below)

A Sample Report

The LabelText field holds the text that will be set as the column header's Label Caption property when we generate the report.  It's also used to populate the "Field" combo box in the Field List subform on my report builder form (section 4 below).

The screenshot below shows a sample custom report named "Amortization Report":

Right after the user hits the [Preview Report] button in the above screenshot, the algorithm runs to clear out and set the data for the "VARIABLE" fields.  

Here's what the ReportControls table looks like after that process finishes:

SELECT RC.ControlID
     , RC.LabelText
     , RC.Sequence
     , RC.GroupOn
     , RC.DisplaySum
     , RC.Sum
     , RC.Sort
FROM ReportControls AS RC
WHERE RC.Sequence Is Not Null
ORDER BY RC.Sequence;
The above SQL was used to generate the Datasheeet view shown below.
Note how the VARIABLE fields in blue match the corresponding values of the data in section 4 of the screenshot above.

Design Decisions

Technically speaking, the "VARIABLE" fields could easily be omitted from this table.  

In fact, if I were writing this report builder from scratch today, that's probably what I would do.  Something about mixing permanent values with working values in the same table seems wrong.  It feels like bad design.

And, yet, younger me may have been on to something:

  • The mixed "STATIC"/"VARIABLE" approach is easy to troubleshoot as evidenced by the screenshot above; we can see what's going on and can even manually change the data to aid in debugging.
  • Front-end bloat is not an issue because the amount of space the VARIABLE fields occupy is the same (the most common source of front-end bloat comes from inserting a large number of temporary records in local tables; updating existing records will not contribute to front-end bloat).
  • After sixteen years, the design has proven itself to be rock-solid.  It has stood the test of time.