# Blazor Pivot Table (PivotGrid)

URL: https://flexcoreui.com/demo/pivot  
Section: Data Grid  
Controls: PivotControl, GridControl  
Keywords: pivot table, pivot grid, pivotgrid, cross-tab, crosstab, calculated field

Turn a flat list into a cross-tab in Blazor: grouped headers, subtotals, several measures, formula columns and a drag-and-drop field builder.

## Documentation

### Params

| Name | Type | Default | Description |
|---|---|---|---|
| DataSource | IEnumerable<TValue> | Enumerable.Empty<TValue>() | The rows to aggregate. A field is read by property name, case-insensitive, or by key when the rows are IDictionary<string, object>. |
| RowFields | IReadOnlyList<string> | Array.Empty<string>() | Fields that label the rows, outermost first. With two or more, the first spans its group and a subtotal row closes it. With none, the table is empty. |
| ColumnFields | IReadOnlyList<string> | Array.Empty<string>() | Fields spread across the columns. One field gives one header row, two give a grouped header above the leaves; the headers are laid out for at most two. With none, a single Value column. |
| ValueField | string | "" | The field to aggregate when ValueFields is not set. |
| Aggregation | AggregateType | AggregateType.Sum | How ValueField is aggregated. Count counts rows; the others skip values that do not convert to a number. With nothing left, Sum prints 0 and Average, Min and Max print an empty cell. |
| ValueFormat | string | "" | Format for ValueField's cells, such as N0 or C2. Blank prints the plain number without grouping. |
| ValueFields | IReadOnlyList<PivotValueConfig>? | null | Several measures, each with its own aggregation, format and label; overrides ValueField and Aggregation. With more than one, every column gets a sub-column per measure and the Total column is repeated per measure. |
| FieldLabels | Dictionary<string, string>? | null | Display text by field name, used in headers, chips and filter labels. A field that is not listed shows its name. |
| ShowSubTotals | bool | true | With two or more row fields, adds a "group Total" row after each first-level group. |
| ShowGrandTotals | bool | true | Adds a Total column per measure and a Grand Total row. |
| AllowSorting | bool | true | Makes the headers, and the chips' arrows in the builder, clickable. Sorting a row or column field reorders its values; sorting a value column orders the rows by that column's aggregate and keeps rows inside their group. A click cycles ascending, descending, off; Shift-click adds a key. |
| SortDescriptors | IReadOnlyList<PivotSortDescriptor>? | null | Sort keys set by the page, one per field; applied whenever the list differs from the one last applied. An empty list clears the sort. |
| Interactive | bool | false | Shows the field builder: the available fields (the row type's public readable properties, or the first row's keys for dictionary rows, minus names ending in Formula), the Filters, Rows, Columns and Values zones, an aggregation dropdown and format box per measure, and a filter bar above the table. The builder is seeded from the parameters once and then keeps its own state. |
| Height | string | "420px" | Maximum height of the scrolling table; the builder and the filter bar are outside it. |
| Width | string | "" | Maximum width of the scrolling table. Blank sets none. |
| CssClass | string | "" | Added to the root element. |
| Style | string | "" | Inline style of the root element. |

### Events

| Name | Type | Description |
|---|---|---|
| SortDescriptorsChanged | IReadOnlyList<PivotSortDescriptor> | After a header click, a chip arrow, SortByAsync or ClearSortingAsync: the complete list of keys. |

### Methods

| Name | Type | Description |
|---|---|---|
| SortByAsync(string field, SortDirection? direction, bool add = false) | Task | Sorts a row field, a column field or a value column by its generated field name. A null direction removes that key; add keeps the other keys, otherwise they are replaced. Throws for an unknown field and does nothing while AllowSorting is off. |
| ClearSortingAsync() | Task | Removes every key and raises SortDescriptorsChanged. |

### Measure

| Name | Type | Default | Description |
|---|---|---|---|
| Field | string | "" | The field to aggregate. |
| Label | string? | null | Header text for the measure. Null gives "Aggregation of Field" on the builder chip and the aggregation name in the header. |
| Aggregation | AggregateType | AggregateType.Sum | Sum, Average, Count, Min or Max. |
| Format | string | "" | .NET numeric format for the cells; blank prints the plain number. |
| ValueAggregator | Func<IEnumerable<object?>, object>? | null | Your own aggregate over the cell's raw field values. When set, Aggregation is not used. |
| DisplayLabel | string |  | Read-only: Label, or "Aggregation of Field". |
| ShortLabel | string |  | Read-only: Label, or the aggregation name. |

### Formula

| Name | Type | Default | Description |
|---|---|---|---|
| Formula | string? | null | On GridColumn: a per-row expression evaluated instead of reading Field. [Name] and bare property names become the row's values (non-numbers count as 0) and the result comes from DataTable.Compute; a failing expression shows as # plus the expression. |
| AllowCellFormulas | bool | false | On GridControl: a cell whose value is a string starting with = is evaluated the same way. |

### Css

| Name | Description |
|---|---|
| fx-pivot | The root element; fx-pivot-interactive is added with the builder. |
| fx-pivot-builder | The field builder: fx-pivot-field-list holds the available fields, fx-pivot-zones the four fx-pivot-zone drop areas (fx-pivot-zone-hover while a chip is over one). |
| fx-pivot-field-chip | A field chip, with fx-pivot-chip-available, -row, -col, -value or -filter; fx-pivot-chip-remove is its ✕ button, fx-pivot-agg-select and fx-pivot-format-input the measure controls. |
| fx-pivot-filter-bar | The filter dropdowns above the table, one fx-pivot-filter-item each. |
| fx-pivot-scroll | The scrolling wrapper that takes Height and Width; fx-pivot-table is the table inside it. |
| fx-pivot-header | A header row, with fx-pivot-header-group on upper levels and fx-pivot-header-leaf on the last; cells are fx-pivot-hdr-row, -colgroup, -colgroup2, -value or -total, and fx-pivot-sort is the arrow. |
| fx-pivot-data-row | A data row; fx-pivot-subtotal-row and fx-pivot-grand-total-row are the total rows. |
| fx-pivot-cell-value | A value cell; row labels are fx-pivot-cell-rowgroup (spanning) and fx-pivot-cell-rowfield, totals fx-pivot-cell-total, -subtotal, -subtotal-label, -subtotal-grand, -grand-label, -grand-value and -grand-total. |
| fx-pivot-empty | The hint shown when neither rows nor values are placed. |

## Examples

### Builder

```razor
@using Fx.ControlKit.Grid

<PivotControl TValue="AirportFacts"
              DataSource="@_facts"
              RowFields="@_byContinent"
              ColumnFields="@_byType"
              ValueField="Airports"
              ValueFormat="N0"
              Interactive="true"
              FieldLabels="@_labels"
              Height="420px" />
```

```csharp
// Builder: the airports cut down to the fields worth pivoting on.
private static readonly List<AirportFacts> _facts = AirportData.Airports
    .Select(a => new AirportFacts(a.Continent, a.Type, RunwayClass(a.LongestRunwayFt), a.Latitude >= 0 ? "North" : "South",
        a.Runways, a.LongestRunwayFt, a.ElevationFt))
    .ToList();
private readonly string[] _byContinent = ["Continent"];
private readonly string[] _byType = ["Type"];
private readonly Dictionary<string, string> _labels = new()
{
    ["Runway"] = "Runway class",
    ["LongestRunwayFt"] = "Longest runway (ft)",
    ["ElevationFt"] = "Elevation (ft)"
};

// As text these sort longest first, with Unknown last.
private static string RunwayClass(int? longestFt) => longestFt switch
{
    null => "Unknown",
    >= 10_000 => "10,000 ft or more",
    >= 6_000 => "6,000 to 9,999 ft",
    _ => "Under 6,000 ft"
};

private sealed record AirportFacts(string Continent, string Type, string Runway, string Hemisphere,
    int Runways, int? LongestRunwayFt, int? ElevationFt)
{
    public int Airports => 1;
}
```

### Grouped

```razor
@using Fx.ControlKit.Grid

<PivotControl TValue="Airport"
              DataSource="@AirportData.Airports"
              RowFields="@_byContinentAndCountry"
              ColumnFields="@_byType"
              ValueFields="@_measures"
              SortDescriptors="@_sorts"
              SortDescriptorsChanged="s => _sorts = s"
              ShowGrandTotals="@_grand"
              ShowSubTotals="@_sub"
              Height="480px" />
<p class="result">Sort: @SortText</p>
```

```csharp
private bool _grand = true, _sub = true;
private readonly string[] _byType = ["Type"];

// Grouped: Continent and Country on the full Airport records.
private readonly string[] _byContinentAndCountry = ["Continent", "Country"];
private readonly PivotValueConfig[] _measures =
[
    new() { Field = "Code", Aggregation = AggregateType.Count, Format = "N0", Label = "Airports" },
    new() { Field = "LongestRunwayFt", Aggregation = AggregateType.Max, Format = "N0", Label = "Longest runway (ft)" }
];
// A Total column's field name is "__Total_" + the measure's Field + "_" + its Aggregation.
private static readonly PivotSortDescriptor MostAirports = new("__Total_Code_Count", SortDirection.Descending);
private static readonly PivotSortDescriptor LongestRunway = new("__Total_LongestRunwayFt_Max", SortDirection.Descending);
private IReadOnlyList<PivotSortDescriptor> _sorts = [MostAirports];
private string SortText => _sorts.Count == 0 ? "none." : string.Join(", ", _sorts.Select(s => $"{s.Field} {s.Direction}")) + ".";
```

### Formulas

```razor
@using Fx.ControlKit.Grid

<GridControl TValue="Hub" DataSource="@_hubs"
             AllowCellFormulas="true"
             AllowSorting="true"
             AllowFiltering="true"
             AllowResizing="true"
             Height="400px">
    <GridColumnsBase>
        <GridColumn Field="@nameof(Hub.Code)" HeaderText="Code" Width="80px" />
        <GridColumn Field="@nameof(Hub.City)" HeaderText="City" Width="150px" />
        <GridColumn Field="@nameof(Hub.Country)" HeaderText="Country" Width="160px" />
        <GridColumn Field="@nameof(Hub.ElevationFt)" HeaderText="Elevation (ft)" Type="ColumnType.Number" Format="N0" TextAlign="TextAlign.Right" Width="120px" />
        <GridColumn Field="ElevationM" HeaderText="Elevation (m)" Formula="[ElevationFt] * 0.3048" Type="ColumnType.Number" Format="N0" TextAlign="TextAlign.Right" Width="120px" />
        <GridColumn Field="@nameof(Hub.LongestRunwayFt)" HeaderText="Longest runway (ft)" Type="ColumnType.Number" Format="N0" TextAlign="TextAlign.Right" Width="160px" />
        <GridColumn Field="@nameof(Hub.RunwayKmFormula)" HeaderText="Longest runway (km)" Type="ColumnType.Number" Format="N2" TextAlign="TextAlign.Right" Width="160px" />
    </GridColumnsBase>
</GridControl>
```

```csharp
// Formulas: twelve airports from below sea level to La Paz.
private static readonly string[] _hubCodes = ["AMS", "LHR", "LIS", "KEF", "DXB", "HND", "SYD", "DEN", "JNB", "MEX", "BOG", "LPB"];
private readonly List<Hub> _hubs = _hubCodes
    .Select(code => AirportData.Airports.First(a => a.Code == code))
    .Select(a => new Hub(a.Code, a.City, a.Country, a.ElevationFt, a.LongestRunwayFt))
    .ToList();

private sealed record Hub(string Code, string City, string Country, int? ElevationFt, int? LongestRunwayFt)
{
    public string RunwayKmFormula => "=[LongestRunwayFt] * 0.0003048";
}
```

