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.

Sample data from the Reports_Builtin table. Open in new tab to view full size image.

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
Sample data from the Templates_Builtin table.

For more information about the related ReportControls table, see the following article:

Report Builder: The Local ReportControls Table
A description of the local table that serves as the foundation for my Advanced Report Builder.

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.