Highly Customizable Reporting Architecture (Metadata → SQL → API → UI)

Rajesh Gami
16h
131
0
0

100

Article

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 Visible fields
Apply 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
)

3. Dynamic Column Visibility

Backend removes columns based on:

User role
Field sensitivity level (PII, financial, admin-only)

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

Load metadata
Apply security filters
Build SQL
Parameter bind
Execute query with pagination
Return schema + rows
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

Render UI controls (textbox, dropdown, date range) based on metadata

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

Columns generated from metadata
Support sorting, grouping, exporting

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

Optional Enhancements

Feature	Supported via Metadata?
Drilldown into linked report	Yes
Save custom user layout	Yes
Shareable report links	Yes
Scheduled email export	Yes
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 Type	Use Case
Result Cache	Slow reports with stable data
Partial Cache	Only aggregated or historical data
Warm-up Cache	Auto-precompute before user runs

Performance Guidance

Precompute large aggregations using materialized views
Use columnstore indexes for analytical workloads
Enforce paging at DB level
Limit max rows or enforce export-only for large reports

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

Admin creates a new report
Selects base table Orders
Adds join to Product and Customer
Adds fields:
- CustomerName (group)
- SUM(OrderAmount) (aggregate)
- Month(OrderDate) (expression)
Configure filters: Date, Category
Set tenant-scoped + hide financial fields from non-manager roles

No code changes. Available instantly.

Summary

A metadata-driven reporting architecture delivers:

Scalability (new reports without developer involvement)
Governance and security (rules apply before SQL runs)
Extensibility (joins, filters, export, drilldown, scheduling)
UI flexibility (Angular renders based on metadata)

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