Multi-Level Pivot Table
PivotControl builds Excel-style pivot tables with multi-level column headers,
row-level grouping with subtotals, drag-and-drop field configuration, report filters,
and multiple value fields. It also integrates with GridControl formula columns.
Interactive Pivot Builder
Drag fields from the Available Fields list into the Rows, Columns, Values, or Filters areas. The pivot table updates instantly. Change the aggregation type on value fields using the dropdown.
| Country | Q1 | Q2 | Q3 | Q4 | Total |
|---|---|---|---|---|---|
| Germany | $72,000 | $83,000 | $64,000 | $84,000 | $303,000 |
| UK | $82,000 | $92,000 | $82,000 | $105,000 | $361,000 |
| USA | $189,000 | $209,000 | $115,000 | $143,000 | $656,000 |
| Grand Total | $343,000 | $384,000 | $261,000 | $332,000 | $1,320,000 |
<PivotControl TValue="SaleRecord"
DataSource="@_sales"
RowFields="@(new[] { "Country" })"
ColumnFields="@(new[] { "Quarter" })"
ValueField="Amount"
Interactive="true"
ShowSubTotals="true"
ShowGrandTotals="true" />How the Pivot Builder Works
The interactive builder mirrors Excel's PivotTable Fields pane. Each field in your data model can be placed into one of four areas:
≡ Rows
Row fields define the left-hand labels of the pivot table.
With multiple row fields, the first field groups rows with
rowspan and subtotals are inserted per group.
⊞ Columns
Column fields create the horizontal dimension. With multiple
column fields, multi-level headers are rendered using
colspan — the first field spans the second.
Σ Values
Value fields supply the numbers. Each value field can use a different aggregation: Sum, Count, Average, Min, or Max. Multiple value fields add sub-columns under each column key.
⊞ Filters
Filter fields add dropdown selectors above the pivot. Choosing a value narrows the data before grouping — like Excel's Report Filter area.
Multiple Row Fields
When two or more fields are in the Rows area, the first field becomes a group header. Its cell spans all detail rows plus the subtotal row. This is equivalent to Excel's "Group and Outline" for pivot row labels.
| Country | Category | Q1 | Q2 | Q3 | Q4 | Total |
|---|---|---|---|---|---|---|
| Germany | Clothing | $17,000 | $20,000 | $13,000 | $16,000 | $66,000 |
| Electronics | $55,000 | $63,000 | $51,000 | $68,000 | $237,000 | |
| Germany Total | $72,000 | $83,000 | $64,000 | $84,000 | $303,000 | |
| UK | Clothing | $12,000 | $15,000 | $18,000 | $21,000 | $66,000 |
| Electronics | $70,000 | $77,000 | $64,000 | $84,000 | $295,000 | |
| UK Total | $82,000 | $92,000 | $82,000 | $105,000 | $361,000 | |
| USA | Clothing | $36,000 | $37,000 | $28,000 | $44,000 | $145,000 |
| Electronics | $153,000 | $172,000 | $87,000 | $99,000 | $511,000 | |
| USA Total | $189,000 | $209,000 | $115,000 | $143,000 | $656,000 | |
| Grand Total | $343,000 | $384,000 | $261,000 | $332,000 | $1,320,000 | |
<PivotControl TValue="SaleRecord"
RowFields="@(new[] { "Country", "Category" })"
ColumnFields="@(new[] { "Quarter" })"
ValueField="Amount"
ShowSubTotals="true" />Multiple Column Fields
Placing two fields in Columns creates multi-level headers.
The first column field (Quarter) spans the values of the second
field (Category). This matches Excel's nested column labels.
| Country | Q1 | Q2 | Q3 | Q4 | Total | ||||
|---|---|---|---|---|---|---|---|---|---|
| Clothing | Electronics | Clothing | Electronics | Clothing | Electronics | Clothing | Electronics | ||
| Germany | $17,000 | $55,000 | $20,000 | $63,000 | $13,000 | $51,000 | $16,000 | $68,000 | $303,000 |
| UK | $12,000 | $70,000 | $15,000 | $77,000 | $18,000 | $64,000 | $21,000 | $84,000 | $361,000 |
| USA | $36,000 | $153,000 | $37,000 | $172,000 | $28,000 | $87,000 | $44,000 | $99,000 | $656,000 |
| Grand Total | $65,000 | $278,000 | $72,000 | $312,000 | $59,000 | $202,000 | $81,000 | $251,000 | $1,320,000 |
Multiple Value Fields
Use the ValueFields parameter to aggregate more than one
measure. Each value field can have its own aggregation and format.
Below, Sum of Amount and Count of Quantity
appear side by side under each column group.
| Country | Q1 | Q2 | Q3 | Q4 | Total | |||||
|---|---|---|---|---|---|---|---|---|---|---|
| Revenue | Units | Revenue | Units | Revenue | Units | Revenue | Units | Revenue | Units | |
| Germany | $72,000 | 109 | $83,000 | 125 | $64,000 | 126 | $84,000 | 161 | $303,000 | 521 |
| UK | $82,000 | 106 | $92,000 | 126 | $82,000 | 86 | $105,000 | 107 | $361,000 | 425 |
| USA | $189,000 | 211 | $209,000 | 221 | $115,000 | 144 | $143,000 | 210 | $656,000 | 786 |
| Grand Total | $343,000 | 426 | $384,000 | 472 | $261,000 | 356 | $332,000 | 478 | $1,320,000 | 1,732 |
<PivotControl TValue="SaleRecord"
RowFields="@(new[] { "Country" })"
ColumnFields="@(new[] { "Quarter" })"
ValueFields="@_multiValueFields" />
@code {
PivotValueConfig[] _multiValueFields = [
new() { Field = "Amount", Aggregation = Sum, Format = "C0" },
new() { Field = "Quantity", Aggregation = Count, Format = "N0" }
];
}Grid Formula Cells
GridControl supports computed columns via GridColumn.Formula
and cell-level = formulas when AllowCellFormulas is enabled.
| Region | Rep | Product | Revenue | Cost | Gross Margin | Margin % | Commission |
|---|---|---|---|---|---|---|---|
PivotControl API
RowFields / ColumnFields
String arrays naming the data fields used for row labels and column headers.
ValueField / ValueFields
Single field + aggregation, or a list of PivotValueConfig for multiple measures.
Interactive
Set true to show the drag-and-drop field builder panel alongside the table.
ShowSubTotals / ShowGrandTotals
Insert per-group subtotal rows and a grand total row at the bottom.
FieldLabels
Optional Dictionary<string, string> mapping field names to friendly display labels.
Aggregation Types
Sum, Count, Average, Min, Max — set per value field in PivotValueConfig.