Creating System Reports

Step-by-step guide to creating configuration-driven system reports

Overview

System-defined reports are fully configuration-driven — no backend code changes required. A new report is created entirely by inserting records into the appropriate tables.

The reporting engine flow:

Category → Source → Dictionary Metrics → Report → Template Mapping

If these mappings exist correctly, the report automatically works.

Step-by-Step Process

1. Create Category

   Table: category_master

   Add a logical grouping for the platform if one doesn’t exist.

   Paid Media, Commerce, Analytics/Attribution
   
2. Create Source

   Table: source_master

   Add the platform under the category.

   • Set client_id = 0 for system/global sources

   LinkedIn Ads → category_id = Paid Media
   
3. Add Metrics and Dimensions

   Table: report_data_dictionary

   Insert metrics (clicks, spend, impressions) and dimensions (account, campaign). For each entry define:

   Field	Description
   source_table	Database table to pull from
   source_column	Column name in source table
   entry_type	Set to System
   level	Account, Campaign, AdSet, Ads, Orders
   attribute	For custom dimensions

   This step defines what data exists.

4. Map Metrics to Source

   Table: report_custom_fields

   Create the mapping between dictionary entries and sources:

   dictionary_id ↔ category_id ↔ source_id
   

   This ensures only valid metrics appear for that source and enables safe query generation.

5. Create Report

   Table: report_master

   Insert a new report record:

   Field	Value
   report_name	e.g., “LinkedIn Ads”
   report_type	System

   This generates a report_id used in subsequent steps.

6. Map Report to Source

   Table: report_source_master

   Link the report to its data source:

   report_id ↔ source_id
   

   This tells the engine which data sources feed this report.

7. Configure Columns (Template)

   Table: report_template_master

   Define how the report appears in the UI:

   • Which metrics appear
   • Display order
   • Column labels
   • Sticky columns
   • Formula overrides (if needed)

   For system reports, only add Account-level metrics. Do not add Campaign, AdSet, or Ads level metrics — Account level already contains aggregated totals.

System Report Constraints

System-defined reports follow these rules:

Rule	Detail
Aggregation	Always aggregated (no granular drill-down)
Granularity	Always daily
Level behavior	Varies by source type (see below)

Level by Source Type

Source Type	Level Used
Paid Media	Account
Shopify	null
Analytics	Analytics Report
Mission Control	null

Only top-level aggregated metrics should be added to report_template_master for system reports.