Report Builder: The Reports & Templates Tables
This is one of a series of articles detailing my Advanced Report Builder.
The report templates for my Advanced Report Builder are saved in two logical tables:
- Reports: the report name, creator, report options, and saved filters
- Templates: a child table with the list of controls and their grouping, sorting, and sequence options
I refer to these as "logical" tables because Reports is actually broken down into three physical tables and Templates is broken down into two physical tables.
Reports Tables
- Reports_Builtin: a local table with the names of custom reports that cannot be modified by end users
- Reports_Users: a linked table with the names of custom reports created and saved by the end users
- Reports_Custom: a local table with a single ReportName field whose sole purpose is to populate the Saved Reports combo box (see below); these are fully custom reports that do not run on the report builder engine
The report names that populate the combo box in the Saved Reports section (section 1 below) come from the UNION of these three tables. The options in sections 6 and 7 are saved in these tables, along with select filters from section 8.
Templates Tables
- Templates_Builtin: a local table with the column data associated with the records in the Reports_Builtin table
- Templates_User: a linked table with the column data associated with the records in the Reports_User table
The RecordSource for Section 4 in the screenshot above is either the Templates_Builtin or Templates_User table, depending on whether the report selected in Section 1 is a built-in report or a user report.
Reports Definition
NOTE: "K" marks primary key fields and asterisks (*) indicate required fields
| Field Name | Data Type | Description | |
|---|---|---|---|
| *K | ReportName | Text (50) | Name report saved under |
| Creator | Text (50) | Login name of the person who built the report | |
| PrintLayout | Long [1] | 1=Portrait,2=Landscape,3=Legal Landscape | |
| IsAltShaded | Boolean [Yes] | Does the report shade alternate lines? | |
| IsSummary | Boolean [No] | Is the report a summary only? | |
| FontSize | Long [8] | Default font size for field labels and controls | |
| HasDividingLines | Boolean [No] | Does the report have dividing lines between detail records? | |
| IsMultShaded | Boolean [No] | Does the report shade lines in multi-line reports? | |
| HasApprovalLine | Boolean [No] | Does the report have a 'Prepared by'/ 'Approved by' line? | |
| DataSource | Text (255) | Name of table or query report is based on (no longer used) | |
| DispositionFilter | Text (50) | Default Disposition filter setting for this report | |
| InvTypeFilter | Text (50) | Default InvType filter setting for this report | |
| FixedFilter | Text (50) | Default Fixed filter setting for this report | |
| FHAFilter | Boolean | Default FHA/VA filter setting for this report | |
| CollAcctsFilter | Boolean | Default Collateralized Accounts filter setting for this report |
NOTE: Both the Reports_User and Reports_Builtin tables have the same structure. This makes it easy to UNION them together.
Templates Definition
NOTE: "K" marks primary key fields and asterisks (*) indicate required fields
| Field Name | Data Type | Description | |
|---|---|---|---|
| *K | ReportName | Text (50) | Foreign Key to Reports table |
| * | ControlID | Long [0] | Foreign Key to ReportControls table |
| *K | Sequence | Long [0] | Order in which the control appears on the report |
| GroupOn | Boolean | True if the report should group on this control | |
| DisplaySum | Boolean | Not used (see note below) | |
| Sum | Boolean | Not used (see note below) | |
| Sort | Text (50) | "ASC" or "DESC" to indicate the field's sort order |
For more information about the related ReportControls table, see the following article:
Note About DisplaySum and Sum Fields
My original plan was to give users the option of showing or hiding the summary calculation for each field (via DisplaySum) as well as controlling the actual expression used, such as Sum(), Average(), Max(), etc. (via Sum).
To simplify the user interface, though, I decided against that approach.
Instead, I assume that for report controls that support it, users will always want to see the summary calculation in group and report footers. Also, each ControlID supports only one summary expression (usually it's Sum()). If users really need to pick from two different summary expressions (such as Sum() and Average()), then we can always add an additional ControlID record to the ReportControls table.