catalog.fn.pivot()
Create multi-dimensional pivot tables for cross-tabulation analysis. Organize data into rows and columns with aggregated values at each intersection.
Syntax
Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
rows | Array<{ attribute: string }> | Yes | Row dimensions for grouping (primary grouping) |
columns | Array<{ attribute: string }> | Yes | Column dimensions for grouping (secondary grouping) |
values | Array<{ attribute: string; types: AggregationType[] }> | Yes | Aggregations to compute at each row/column intersection |
options.calculateTotals | boolean | No | Include grand totals (default: true) |
Return Type
Returns a PivotResponse object with nested structure:
Usage Notes
- Pivot tables respect current query filters - results update as filters change
- Supports multiple row and column dimensions for hierarchical grouping
- Deep queries supported (e.g., "product.category" or "customer.region")
- Use calculateTotals: false to disable grand totals for performance
- Ideal for sales reports, time-based analysis, and cross-tabulations
- Performance: Limit row/column dimensions - each dimension multiplies result size
- Performance: Pre-filter data before pivoting to reduce the dataset
- Performance: Request only needed aggregation types in values array
- Troubleshooting: For sparse data, expect many cells with null/0 values - this is normal
Related Methods
catalog.fn.groupBy()- Simpler single-dimension groupingcatalog.fn.aggregate()- Compute aggregations without groupingcatalog.fn.facets()- Get unique values before pivoting
Examples
Sales by Region and Product
Classic pivot table with regions as rows, products as columns, and revenue/count as values.
Budget vs Actual by Department
Compare budgeted and actual amounts with variance calculations across departments and quarters.
Sales by Salesperson and Region
Single-row pivot showing salesperson performance across regions with commission calculations.
Email Campaign Metrics
Multi-metric pivot for email campaigns showing sent, opened, clicked with calculated rates.
Weather Data Analysis
Statistical analysis pivot using avg, min, max, and sum for weather data across locations.
Subscription Churn by Plan
Pre-filter to churned subscriptions then pivot by plan and month for churn analysis.
Deep Property Pivot
Pivot using referenced model properties via dot notation for cross-model analysis.