SSRS
Microsoft enterprise reporting। RDL XML format।
- Data Source, Dataset, Parameters
- Tablix (table/matrix), Chart
- Expressions (VB.NET-like)
RDLC (Local)
LocalReport r = new LocalReport();
r.ReportPath = "Reports/Sales.rdlc";
r.DataSources.Add(new ReportDataSource("DS1", data));
byte[] pdf = r.Render("PDF");
Crystal Reports
SAP legacy। Strong sub-report, cross-tab। Licensed।
JasperReports
Java open-source। .jrxml format।
JasperReport r = JasperCompileManager.compileReport("s.jrxml");
JasperPrint p = JasperFillManager.fillReport(r, params, ds);
JasperExportManager.exportReportToPdfFile(p, "out.pdf");
Pivot/Cross-Tab ⭐
-- SQL PIVOT SELECT region, [A], [B] FROM ( SELECT region, product, sales FROM sales ) src PIVOT (SUM(sales) FOR product IN ([A],[B])) p;
Custom Report Builder
End-user drag-drop।
1. Metadata: allowed tables/columns 2. Query Builder UI 3. Generate SQL (parameterized) 4. Save template 5. Schedule, share by role
Security: whitelist tables, RLS, query timeout, row limit।
Audit Trail ⭐
audit_log: user_id, table, record_id, action, old_values JSON, new_values JSON, ts SELECT u.name, a.old_values->>'price', a.new_values->>'price' FROM audit_log a JOIN users u ON a.user_id=u.id WHERE a.table_name='products' AND a.record_id=42;
Performance Optimization
- Materialized views
- Read replica/data warehouse
- Index filter columns
- Pre-aggregate (daily summary)
- Cache static reports
- OLAP cube multi-dimensional
Modern Alternatives
Metabase (open source), Looker, Superset, Mode, Grafana।