Interactive Pivot Tables for Website Analytics

Why ready-made libraries fall short for big data

Picture an e-commerce site with 10 million orders. A manager needs a quarterly sales report grouped by category and month. A standard Excel export takes 30 minutes and hammers the database. A pivot table with server-side aggregation returns data in 200 ms and lets the user change slices in real time. In practice, off-the-shelf libraries struggle with that load: react-pivottable chokes on 200k+ rows, AG Grid requires an enterprise license for server mode ($2k/year), and Flexmonster doesn't support ad-hoc queries against relational databases. We combine libraries with our own server-side aggregation to fill those gaps. The license savings can exceed $10k/year for a team of 5 developers.

Wikipedia defines a pivot table as a data analysis tool that aggregates data by dimensions. In commercial projects, customization is often needed – something ready-made solutions don't provide.

How we build server-side aggregation for millions of rows

For large data, pivot configuration moves to the server. We use ClickHouse or PostgreSQL with indexes. Each axis change sends a query, the server returns aggregates in 100–500 ms. We cache results by configuration key. Below is an example of generating SQL from config.

// Server – generate SQL from config function buildPivotQuery(config: PivotConfig, dateRange: [Date, Date]): string { const rowsExpr = config.rows.map(r => `"${r}"`).join(', '); const colsExpr = config.cols.map(c => `"${c}"`).join(', '); const valExpr = config.values[0]; // simplified const aggExpr = { sum: `SUM("${valExpr}")`, count: `COUNT(*)`, avg: `AVG("${valExpr}")::numeric(18,2)`, min: `MIN("${valExpr}")`, max: `MAX("${valExpr}")`, }[config.aggFn]; return ` SELECT ${rowsExpr}, ${colsExpr}, ${aggExpr} AS value FROM events WHERE created_at BETWEEN $1 AND $2 GROUP BY ${rowsExpr}, ${colsExpr} ORDER BY ${rowsExpr}, ${colsExpr} `; } 

Step-by-step guide

1. Analyze data structure. Identify fields for grouping (rows, columns) and measures (sum, average).
2. Design SQL queries. Generate dynamic queries with GROUP BY.
3. Configure ClickHouse/PostgreSQL. Create indexes, set up result caching.
4. Integrate with the client. Send a query on every config change.

Comparison: client-side vs server-side aggregation

Client-side aggregation is 5× faster on small data, but server-side scales to billions of rows. The choice depends on data volume and interactivity needs.

How to implement client-side aggregation: a step-by-step guide

1. Filter data – keep only records matching the applied filters.
2. Group – derive keys for rows and columns based on selected field values.
3. Aggregate – compute the value for each cell (sum, count, average, min, max).
4. Render table – output an HTML table with totals and a fixed header.

type AggregateFunction = 'sum' | 'count' | 'avg' | 'min' | 'max'; interface PivotConfig { rows: string[]; cols: string[]; values: string[]; aggFn: AggregateFunction; filters: Record<string, string[]>; } interface PivotResult { rowKeys: string[][]; colKeys: string[][]; data: Map<string, Map<string, number>>; } function computePivot(rawData: Record<string, any>[], config: PivotConfig): PivotResult { const { rows, cols, values, aggFn, filters } = config; const filtered = rawData.filter(row => Object.entries(filters).every(([field, allowed]) => !allowed.length || allowed.includes(String(row[field])) ) ); const rowKeySet = new Set<string>(); const colKeySet = new Set<string>(); const accumulator = new Map<string, Map<string, number[]>>(); filtered.forEach(row => { const rowKey = rows.map(r => String(row[r] ?? '(empty)')).join('||'); const colKey = cols.map(c => String(row[c] ?? '(empty)')).join('||'); rowKeySet.add(rowKey); colKeySet.add(colKey); const numVal = values.reduce((sum, v) => sum + (Number(row[v]) || 0), 0); if (!accumulator.has(rowKey)) accumulator.set(rowKey, new Map()); const colMap = accumulator.get(rowKey)!; if (!colMap.has(colKey)) colMap.set(colKey, []); colMap.get(colKey)!.push(numVal); }); const aggregated = new Map<string, Map<string, number>>(); accumulator.forEach((colMap, rowKey) => { const row = new Map<string, number>(); colMap.forEach((vals, colKey) => { let result: number; switch (aggFn) { case 'sum': result = vals.reduce((a, b) => a + b, 0); break; case 'count': result = vals.length; break; case 'avg': result = vals.reduce((a, b) => a + b, 0) / vals.length; break; case 'min': result = Math.min(...vals); break; case 'max': result = Math.max(...vals); break; } row.set(colKey, result); }); aggregated.set(rowKey, row); }); return { rowKeys: Array.from(rowKeySet).sort().map(k => k.split('||')), colKeys: Array.from(colKeySet).sort().map(k => k.split('||')), data: aggregated, }; } 

function PivotTable({ result, config, format }: { result: PivotResult; config: PivotConfig; format?: (val: number) => string; }) { const fmt = format ?? (v => v.toLocaleString('en-US')); const rowTotals = result.rowKeys.map(rk => { const rowKey = rk.join('||'); let total = 0; result.colKeys.forEach(ck => { total += result.data.get(rowKey)?.get(ck.join('||')) ?? 0; }); return total; }); const grandTotal = rowTotals.reduce((a, b) => a + b, 0); return ( <div className="overflow-auto max-h-[600px]"> <table className="text-sm border-collapse w-full"> <thead className="sticky top-0 bg-white z-10"> <tr> {config.rows.map(r => ( <th key={r} className="border px-3 py-2 text-left bg-gray-50 font-medium">{r}</th> ))} {result.colKeys.map(ck => ( <th key={ck.join('/')} className="border px-3 py-2 text-right bg-gray-50 font-medium whitespace-nowrap"> {ck.join(' / ')} </th> ))} <th className="border px-3 py-2 text-right bg-blue-50 font-semibold">Total</th> </tr> </thead> <tbody> {result.rowKeys.map((rk, ri) => { const rowKey = rk.join('||'); return ( <tr key={rowKey} className="hover:bg-gray-50"> {rk.map((label, i) => ( <td key={i} className="border px-3 py-1.5 font-medium">{label}</td> ))} {result.colKeys.map(ck => { const val = result.data.get(rowKey)?.get(ck.join('||')); return ( <td key={ck.join('/')} className="border px-3 py-1.5 text-right tabular-nums"> {val != null ? fmt(val) : '—'} </td> ); })} <td className="border px-3 py-1.5 text-right tabular-nums font-medium bg-blue-50"> {fmt(rowTotals[ri])} </td> </tr> ); })} </tbody> <tfoot> <tr className="font-semibold bg-gray-100"> <td colSpan={config.rows.length} className="border px-3 py-2">Total</td> {result.colKeys.map(ck => { const colTotal = result.rowKeys.reduce((sum, rk) => { return sum + (result.data.get(rk.join('||'))?.get(ck.join('||')) ?? 0); }, 0); return ( <td key={ck.join('/')} className="border px-3 py-2 text-right tabular-nums">{fmt(colTotal)}</td> ); })} <td className="border px-3 py-2 text-right tabular-nums bg-blue-100">{fmt(grandTotal)}</td> </tr> </tfoot> </table> </div> ); } 

Export to Excel

We use exceljs to generate .xlsx on the client:

import ExcelJS from 'exceljs'; async function exportToExcel(result: PivotResult, config: PivotConfig) { const wb = new ExcelJS.Workbook(); const ws = wb.addWorksheet('Pivot Table'); const headers = [...config.rows, ...result.colKeys.map(k => k.join(' / ')), 'Total']; ws.addRow(headers).font = { bold: true }; result.rowKeys.forEach(rk => { const rowKey = rk.join('||'); const row = [...rk]; result.colKeys.forEach(ck => { row.push(String(result.data.get(rowKey)?.get(ck.join('||')) ?? '')); }); ws.addRow(row); }); const buffer = await wb.xlsx.writeBuffer(); const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); const url = URL.createObjectURL(blob); const a = document.createElement('a'); a.href = url; a.download = 'pivot.xlsx'; a.click(); } 

How to choose between client-side and server-side aggregation?

If your data is up to 200k rows and doesn't need complex filtering, client-side is faster and cheaper. For millions of rows and ad-hoc queries, server-side aggregation is mandatory. We help clients pick the optimal architecture. With 7+ years of experience and 50+ successful pivot projects, we guarantee a custom solution that meets your performance needs.

Development stages and estimated timeline

Client-side pivot for up to 50k rows with drag-and-drop configurator and Excel export: 2–3 weeks. Server mode with ClickHouse or PostgreSQL, result caching, and support for multiple values: additional 1–2 weeks. A turnkey solution including all stages costs from $5k and is delivered in 4–6 weeks.

Scope of work (included)

• Data analysis and aggregation schema design
• Client UI development with drag-and-drop (React, TypeScript, Tailwind)
• Server-side aggregation on ClickHouse/PostgreSQL with caching
• Export to Excel/CSV, printing
• Load testing up to 10M rows
• Documentation and source code handover
• 3 months of post-launch support with guaranteed response time

We'll evaluate your project within one day. Contact us to discuss your data, metrics, and find the best solution. Get a free consultation and a custom quote today. We also offer a satisfaction guarantee – if the solution doesn't meet agreed performance benchmarks, we iterate until it does.