Introduction

Enterprise systems eventually face a recurring challenge:

Every team wants custom reports — but engineering does not want to write SQL queries for every request.

A metadata-driven reporting architecture solves this. In such a system:

Users can create new reports, clone existing ones, customize columns, filters, layouts, scheduled exports, and drilldowns.

This article provides a production-grade blueprint with real SQL models, .NET backend implementation patterns, and Angular UI design.

Goals

Architecture Overview

       ┌─────────────────────┐
       │ Report Metadata DB │
       └─────────┬──────────┘
                 │
                 ▼
         ┌──────────────┐          ┌───────────────┐
         │ Metadata → SQL│ ───────> │ DB Execution  │
         │ Query Builder │          │ + Security     │
         └───────┬──────┘          └──────┬────────┘
                 │                        │
                 ▼                        ▼
           ┌───────────┐            ┌──────────────┐
           │ Report API│ <────────> │ Cache/History │
           └──────┬────┘            └──────────────┘
                  │
                  ▼
           ┌─────────────┐
           │ Angular UI  │
           │  (Dynamic)  │
           └─────────────┘

Metadata Model

1. Report Definition Table

CREATE TABLE Report (
    ReportId UNIQUEIDENTIFIER PRIMARY KEY,
    Name NVARCHAR(200),
    Category NVARCHAR(100),
    Description NVARCHAR(MAX),
    BaseTable NVARCHAR(200),
    IsPublic BIT DEFAULT 0,
    TenantScoped BIT DEFAULT 1,
    CreatedBy NVARCHAR(200),
    CreatedAt DATETIME2
);

2. Report Fields

CREATE TABLE ReportField (
    FieldId UNIQUEIDENTIFIER PRIMARY KEY,
    ReportId UNIQUEIDENTIFIER,
    ColumnName NVARCHAR(200),
    Label NVARCHAR(200),
    DataType NVARCHAR(50),
    IsVisible BIT DEFAULT 1,
    AllowSort BIT DEFAULT 1,
    AllowGroup BIT DEFAULT 0,
    AllowFilter BIT DEFAULT 1,
    Aggregate NVARCHAR(50) NULL,  -- SUM, COUNT, AVG, NONE
    Expression NVARCHAR(MAX) NULL -- computed expressions
);

3. Report Filters

CREATE TABLE ReportFilter (
    FilterId UNIQUEIDENTIFIER PRIMARY KEY,
    ReportId UNIQUEIDENTIFIER,
    FieldName NVARCHAR(200),
    Operator NVARCHAR(50), -- =, >, <, BETWEEN, LIKE, IN…
    ValueType NVARCHAR(50), -- static, user-input, runtime-param
    DefaultValue NVARCHAR(MAX) NULL
);

4. Joins Table (optional but powerful)

CREATE TABLE ReportJoin (
    JoinId UNIQUEIDENTIFIER PRIMARY KEY,
    ReportId UNIQUEIDENTIFIER,
    TableName NVARCHAR(200),
    JoinType NVARCHAR(10), -- INNER, LEFT, RIGHT
    Condition NVARCHAR(MAX)
);

Dynamic SQL Builder Rules

SELECT Clause

Example generation

SELECT 
    c.CustomerName AS [Customer],
    SUM(o.TotalAmount) AS [Revenue],
    o.OrderDate

FROM + JOIN

Build from metadata:

FROM Orders o 
LEFT JOIN Customer c ON o.CustomerId = c.Id

WHERE + Filter Logic

Filters are user-configurable. Built as:

WHERE (@TenantId IS NULL OR o.TenantId = @TenantId)
AND (@DateFrom IS NULL OR o.OrderDate >= @DateFrom)
AND (@DateTo IS NULL OR o.OrderDate <= @DateTo)

GROUP BY + ORDER BY

If any field has aggregation → GROUP BY non-aggregated fields.

Security Model

1. Tenant Isolation

Add tenant criteria automatically if report metadata says TenantScoped=1.

2. Dynamic Row Permissions

Join with a permission table:

AND EXISTS (
  SELECT 1 FROM UserAccess ua 
  WHERE ua.UserId = @UserId AND ua.ObjectId = o.Id
)

3. Dynamic Column Visibility

Backend removes columns based on:

4. Query Whitelisting

Users can’t write raw SQL — only metadata-driven syntax.

Report API Design (.NET Example)

Endpoint

GET /api/report/{reportId}/run

Query Pipeline

  1. Load metadata

  2. Apply security filters

  3. Build SQL

  4. Parameter bind

  5. Execute query with pagination

  6. Return schema + rows

  7. Cache if configured

Example C# Execution

var report = await _repo.GetMetadata(reportId);
var sql = _sqlBuilder.Build(report, userContext);
var parameters = _sqlBuilder.GetParameters(report, inputFilters);

var result = await _db.QueryAsync(sql, parameters);

return new ReportResponse {
    Columns = report.VisibleFields.Select(x => x.Label),
    Data = result.ToList()
};

Angular Dynamic UI

Dynamic Filter Form

Example

<ng-container *ngFor="let filter of report.filters">
   <app-dynamic-input [filter]="filter" [(ngModel)]="model[filter.fieldName]"></app-dynamic-input>
</ng-container>

Dynamic Data Table

<app-table [columns]="report.fields" [rows]="data"></app-table>

Optional Enhancements

FeatureSupported via Metadata?
Drilldown into linked reportYes
Save custom user layoutYes
Shareable report linksYes
Scheduled email exportYes
Graph view toggle (table → chart)Yes

Scheduling Engine

Store schedules:

CREATE TABLE ReportSchedule (
    ScheduleId UNIQUEIDENTIFIER PRIMARY KEY,
    ReportId UNIQUEIDENTIFIER,
    CronExpression NVARCHAR(200),
    ExportFormat NVARCHAR(50), -- pdf/csv/xlsx
    Recipients NVARCHAR(MAX),
    Active BIT DEFAULT 1
);

Quartz.NET or Hangfire runs scheduler jobs.

Caching Strategy

Cache TypeUse Case
Result CacheSlow reports with stable data
Partial CacheOnly aggregated or historical data
Warm-up CacheAuto-precompute before user runs

Performance Guidance

Example End-to-End Use Case

User Request: “I need a report showing monthly revenue per customer with ability to filter by product category.”

Solution

No code changes. Available instantly.

Summary

A metadata-driven reporting architecture delivers:

This approach replaces a decade of ad-hoc stored procedures and report requests with a platform.