Report Builder Development
A report builder is a UI that lets users independently formulate data queries: select fields, filters, grouping, visualization type—and save as named reports. Unlike Pivot Table, it works at a higher abstraction level: users work with business concepts (orders, customers, regions), not raw table fields.
Architecture
Report builder has four layers:
Metadata Layer—catalog of available entities and fields with human-readable names, types, permitted aggregations. Server-side, loaded at init.
Query Builder—UI for query composition. Client assembles query config.
Query Engine—server converts config to SQL (or OLAP query) and returns data.
Renderer—client renders table or chart.
Metadata Structure
interface FieldMeta {
id: string;
label: string;
type: 'string' | 'number' | 'date' | 'boolean';
entity: string;
aggregatable: boolean;
filterable: boolean;
// Permitted aggregations for numeric fields
aggregations?: ('sum' | 'avg' | 'count' | 'min' | 'max' | 'count_distinct')[];
}
interface EntityMeta {
id: string;
label: string;
fields: FieldMeta[];
// Available join paths
relations?: { entity: string; via: string; label: string }[];
}
// Example metadata for e-commerce
const metadata: EntityMeta[] = [
{
id: 'orders',
label: 'Orders',
fields: [
{ id: 'orders.created_at', label: 'Order Date', type: 'date', entity: 'orders', aggregatable: false, filterable: true },
{ id: 'orders.total', label: 'Order Amount', type: 'number', entity: 'orders', aggregatable: true, filterable: true, aggregations: ['sum', 'avg', 'min', 'max'] },
{ id: 'orders.status', label: 'Status', type: 'string', entity: 'orders', aggregatable: false, filterable: true },
{ id: 'orders.count', label: 'Order Count', type: 'number', entity: 'orders', aggregatable: true, filterable: false, aggregations: ['count'] },
],
relations: [
{ entity: 'customers', via: 'customer_id', label: 'Customer' },
],
},
];
Query Config
interface ReportQuery {
entity: string;
dimensions: string[]; // grouping fields
measures: { field: string; agg: string }[];
filters: { field: string; operator: string; value: any }[];
orderBy?: { field: string; desc: boolean }[];
limit?: number;
}
Query to SQL
function queryToSQL(query: ReportQuery, meta: EntityMeta[]): string {
const entity = meta.find(e => e.id === query.entity)!;
const cols = [...query.dimensions, ...query.measures.map(m => `${m.agg}(${m.field})`)];
let sql = `SELECT ${cols.join(', ')} FROM ${query.entity}`;
if (query.filters.length) {
const where = query.filters.map(f => `${f.field} ${f.operator} '${f.value}'`).join(' AND ');
sql += ` WHERE ${where}`;
}
if (query.dimensions.length) {
sql += ` GROUP BY ${query.dimensions.join(', ')}`;
}
if (query.orderBy?.length) {
const order = query.orderBy.map(o => `${o.field} ${o.desc ? 'DESC' : 'ASC'}`).join(', ');
sql += ` ORDER BY ${order}`;
}
if (query.limit) sql += ` LIMIT ${query.limit}`;
return sql;
}
Timeline
Basic query builder with table rendering—5–7 days. With saved reports, scheduling, and multiple chart types—10–14 days.