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:
Reports are defined as metadata (tables, fields, filters, aggregations, joins)
The backend dynamically generates SQL based on metadata
The API executes the query securely (row/column permissions applied)
The UI builds dynamic tables, filters, graphs, and exports based on metadata — no code change required
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
Users create and modify reports without database or engineering support
Support SQL→API→UI mapping dynamically
Apply security rules: tenant isolation, dynamic column visibility
Support pagination, sorting, grouping, drilldown, export (CSV/Excel/PDF)
Run reports live, cached, or scheduled
Keep performance high even for complex reports
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
Include only
VisiblefieldsApply expression or raw column
Add aggregation if defined
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
)
Join the conversation! Your thoughts help the community grow.