Report Builder: The Local ReportControls Table
A description of the local table that serves as the foundation for my Advanced Report Builder.
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 |
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:
- The five VARIABLE fields are cleared of data for all rows
- 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;
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.
