> ## Content Index > Fetch the complete content index at: https://nolongerset.com/llms.txt > Use this file to discover other available public pages before exploring further. # Report Builder: The Local ReportControls Table - URL: https://nolongerset.com/reportcontrols-table/ - Published: 2023-10-24T03:30:14.000Z - Updated: 2026-05-08T12:36:24.000Z - Description: A description of the local table that serves as the foundation for my Advanced Report Builder. - Author: Mike Wolfe - Tags: Report Builder, #Import 2026-05-20 02:59 *This is one of a series of articles detailing my [Advanced Report Builder](https://nolongerset.com/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 | ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/10/image-4.png) 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](https://nolongerset.com/report-builder-form/) (section 4 below). The screenshot below shows a sample custom report named "Amortization Report": ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/10/image-1-1.png) 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: ```sql 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. ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/10/image-5.png) 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.