blazor-405459-components-pivottable-pivot-table-basics.md
The DevExpress Blazor Pivot Table is designed to convert lengthy table data into compact, interactive reports that display summarized and grouped data. The following image displays how raw tabular data can be transformed into a simple Pivot Table.
This topic describes the component’s visual elements and key concepts of data presentation:
The Blazor Pivot Table consists of four areas where you or your user can place database fields.
Rows AreaField headers allow users to re-arrange fields and sort or filter data. Unique values from corresponding fields are displayed as row headers.Columns AreaField headers allow users to re-arrange fields and sort or filter data. Unique values from corresponding fields are displayed as column headers.Data AreaField headers allow users to re-arrange fields and sort or filter data. Data cells display information from these fields. Each cell value is a summary calculated against the dataset (filtered by corresponding row/column values).Filter AreaFilter fields do not affect data layout. Use field headers to filter data against fields that are not used in row, column, or data areas. Refer to Filter Data for additional information.
Pivot table fields are data fields that you add to the component to summarize and analyze data.
For example, a SaleInfo class with the OrderId, Region, Country, Date, Product, Amount fields:
public class SaleInfo {
public int OrderId { get; set; }
public string Region { get; set; }
public string Country { get; set; }
public DateTime Date { get; set; }
public string Product { get; set; }
public int Amount { get; set; }
}
You can create a Pivot Table that:
For detailed steps, see Add Fields.
Follow the steps below to add fields to a Pivot Table:
<Fields>...</Fields> tags to the component markup to define the Fields collection.@rendermode InteractiveServer
<DxPivotTable Data="SalesData">
<Fields>
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Region)"
Area="@PivotTableArea.Row"
AreaIndex="0" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Country)"
Area="@PivotTableArea.Row"
SortOrder="@PivotTableSortOrder.Descending"
AreaIndex="1" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Date)"
GroupInterval="@PivotTableGroupInterval.DateYear"
Area="@PivotTableArea.Column"
AreaIndex="0"
Caption="Year" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Date)"
GroupInterval="@PivotTableGroupInterval.DateQuarter"
Area="@PivotTableArea.Column"
AreaIndex="1"
Caption="Quarter" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Amount)"
SortOrder="@PivotTableSortOrder.Ascending"
Area="@PivotTableArea.Data"
SummaryType="@PivotTableSummaryType.Sum" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.City)"
Area="@PivotTableArea.Filter"/>
</Fields>
</DxPivotTable>
@code {
IEnumerable<Sales.SaleInfo> SalesData;
protected override async Task OnInitializedAsync() {
SalesData = await Sales.GetSalesAsync();
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
public class Sales {
static IList<SaleInfo> dataSource;
public class SaleInfo {
public int OrderId { get; set; }
public string Region { get; set; }
public string Country { get; set; }
public string City { get; set; }
public int Amount { get; set; }
public DateTime Date { get; set; }
}
static Sales() {
CreateDataSource();
}
public static Task<IQueryable<SaleInfo>> GetSalesAsync() {
return Task.FromResult(dataSource.AsQueryable());
}
static void CreateDataSource() {
dataSource = new List<SaleInfo> {
new SaleInfo {
OrderId = 10248,
Region = "North America",
Country = "United States",
City = "New York",
Amount = 1740,
Date = DateTime.Parse("2017/01/06")
},
new SaleInfo {
OrderId = 10249,
Region = "North America",
Country = "United States",
City = "Los Angeles",
Amount = 850,
Date = DateTime.Parse("2017/02/13")
},
new SaleInfo {
OrderId = 10250,
Region = "North America",
Country = "United States",
City = "Denver",
Amount = 2235,
Date = DateTime.Parse("2017/02/07")
},
new SaleInfo {
OrderId = 10251,
Region = "North America",
Country = "Canada",
City = "Vancouver",
Amount = 1965,
Date = DateTime.Parse("2017/03/03")
},
new SaleInfo {
OrderId = 10252,
Region = "North America",
Country = "Canada",
City = "Edmonton",
Amount = 880,
Date = DateTime.Parse("2017/03/10")
},
new SaleInfo {
OrderId = 10253,
Region = "South America",
Country = "Brazil",
City = "Rio de Janeiro",
Amount = 5260,
Date = DateTime.Parse("2017/01/17")
},
new SaleInfo {
OrderId = 10254,
Region = "South America",
Country = "Argentina",
City = "Buenos Aires",
Amount = 2790,
Date = DateTime.Parse("2017/05/21")
},
new SaleInfo {
OrderId = 10255,
Region = "South America",
Country = "Paraguay",
City = "Asuncion",
Amount = 3140,
Date = DateTime.Parse("2017/05/01")
},
new SaleInfo {
OrderId = 10256,
Region = "Europe",
Country = "United Kingdom",
City = "London",
Amount = 6175,
Date = DateTime.Parse("2017/06/24")
},
new SaleInfo {
OrderId = 10257,
Region = "Europe",
Country = "Germany",
City = "Berlin",
Amount = 4575,
Date = DateTime.Parse("2017/06/11")
},
new SaleInfo {
OrderId = 10517,
Region = "North America",
Country = "United States",
City = "New York",
Amount = 7710,
Date = DateTime.Parse("2018/07/18")
},
new SaleInfo {
OrderId = 10518,
Region = "North America",
Country = "United States",
City = "Los Angeles",
Amount = 7975,
Date = DateTime.Parse("2018/01/10")
},
new SaleInfo {
OrderId = 10519,
Region = "North America",
Country = "United States",
City = "Denver",
Amount = 3285,
Date = DateTime.Parse("2018/03/13")
},
new SaleInfo {
OrderId = 10520,
Region = "North America",
Country = "Canada",
City = "Vancouver",
Amount = 2580,
Date = DateTime.Parse("2018/04/22")
},
new SaleInfo {
OrderId = 10521,
Region = "North America",
Country = "Canada",
City = "Edmonton",
Amount = 2160,
Date = DateTime.Parse("2018/05/26")
},
new SaleInfo {
OrderId = 10522,
Region = "South America",
Country = "Brazil",
City = "Rio de Janeiro",
Amount = 1100,
Date = DateTime.Parse("2018/05/25")
},
new SaleInfo {
OrderId = 10523,
Region = "South America",
Country = "Argentina",
City = "Buenos Aires",
Amount = 4425,
Date = DateTime.Parse("2018/08/21")
},
new SaleInfo {
OrderId = 10524,
Region = "South America",
Country = "Paraguay",
City = "Asuncion",
Amount = 1360,
Date = DateTime.Parse("2018/08/22")
},
new SaleInfo {
OrderId = 10525,
Region = "Europe",
Country = "United Kingdom",
City = "London",
Amount = 3250,
Date = DateTime.Parse("2018/11/14")
},
new SaleInfo {
OrderId = 10526,
Region = "Europe",
Country = "Germany",
City = "Berlin",
Amount = 5550,
Date = DateTime.Parse("2018/12/21")
},
};
}
When you add multiple fields to the Row or Columns area of a Pivot Table, the table automatically groups related data fields into multiple levels (nested rows/columns). This hierarchical organization provides the following benefits for users:
You can bind multiple Pivot Table fields to the same source field, but set different group intervals to split data into different levels. The following example creates a Pivot Table that contains three fields bound to the Date data source field. Field values are grouped as follows: Year → Quarter → Month.
@rendermode InteractiveServer
<DxPivotTable Data="SalesData">
<Fields>
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Date)"
GroupInterval="@PivotTableGroupInterval.DateYear"
Area="@PivotTableArea.Column"
AreaIndex="0"
Caption="Year" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Date)"
GroupInterval="@PivotTableGroupInterval.DateQuarter"
Area="@PivotTableArea.Column"
AreaIndex="1"
Caption="Quarter" >
<ValueTemplate>
<span>@($"Q{context.Text}")</span>
</ValueTemplate>
</DxPivotTableField>
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Date)"
GroupInterval="@PivotTableGroupInterval.DateMonth"
Area="@PivotTableArea.Column"
AreaIndex="1"
Caption="Month" />
@*...*@
</Fields>
</DxPivotTable>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
public class Sales {
static IList<SaleInfo> dataSource;
public class SaleInfo {
public int OrderId { get; set; }
public string Region { get; set; }
public string Country { get; set; }
public string City { get; set; }
public int Amount { get; set; }
public DateTime Date { get; set; }
}
static Sales() {
CreateDataSource();
}
public static Task<IQueryable<SaleInfo>> GetSalesAsync() {
return Task.FromResult(dataSource.AsQueryable());
}
static void CreateDataSource() {
dataSource = new List<SaleInfo> {
new SaleInfo {
OrderId = 10248,
Region = "North America",
Country = "United States",
City = "New York",
Amount = 1740,
Date = DateTime.Parse("2017/01/06")
},
new SaleInfo {
OrderId = 10249,
Region = "North America",
Country = "United States",
City = "Los Angeles",
Amount = 850,
Date = DateTime.Parse("2017/02/13")
},
new SaleInfo {
OrderId = 10250,
Region = "North America",
Country = "United States",
City = "Denver",
Amount = 2235,
Date = DateTime.Parse("2017/02/07")
},
new SaleInfo {
OrderId = 10251,
Region = "North America",
Country = "Canada",
City = "Vancouver",
Amount = 1965,
Date = DateTime.Parse("2017/03/03")
},
new SaleInfo {
OrderId = 10252,
Region = "North America",
Country = "Canada",
City = "Edmonton",
Amount = 880,
Date = DateTime.Parse("2017/03/10")
},
new SaleInfo {
OrderId = 10253,
Region = "South America",
Country = "Brazil",
City = "Rio de Janeiro",
Amount = 5260,
Date = DateTime.Parse("2017/01/17")
},
new SaleInfo {
OrderId = 10254,
Region = "South America",
Country = "Argentina",
City = "Buenos Aires",
Amount = 2790,
Date = DateTime.Parse("2017/05/21")
},
new SaleInfo {
OrderId = 10255,
Region = "South America",
Country = "Paraguay",
City = "Asuncion",
Amount = 3140,
Date = DateTime.Parse("2017/05/01")
},
new SaleInfo {
OrderId = 10256,
Region = "Europe",
Country = "United Kingdom",
City = "London",
Amount = 6175,
Date = DateTime.Parse("2017/06/24")
},
new SaleInfo {
OrderId = 10257,
Region = "Europe",
Country = "Germany",
City = "Berlin",
Amount = 4575,
Date = DateTime.Parse("2017/06/11")
},
new SaleInfo {
OrderId = 10517,
Region = "North America",
Country = "United States",
City = "New York",
Amount = 7710,
Date = DateTime.Parse("2018/07/18")
},
new SaleInfo {
OrderId = 10518,
Region = "North America",
Country = "United States",
City = "Los Angeles",
Amount = 7975,
Date = DateTime.Parse("2018/01/10")
},
new SaleInfo {
OrderId = 10519,
Region = "North America",
Country = "United States",
City = "Denver",
Amount = 3285,
Date = DateTime.Parse("2018/03/13")
},
new SaleInfo {
OrderId = 10520,
Region = "North America",
Country = "Canada",
City = "Vancouver",
Amount = 2580,
Date = DateTime.Parse("2018/04/22")
},
new SaleInfo {
OrderId = 10521,
Region = "North America",
Country = "Canada",
City = "Edmonton",
Amount = 2160,
Date = DateTime.Parse("2018/05/26")
},
new SaleInfo {
OrderId = 10522,
Region = "South America",
Country = "Brazil",
City = "Rio de Janeiro",
Amount = 1100,
Date = DateTime.Parse("2018/05/25")
},
new SaleInfo {
OrderId = 10523,
Region = "South America",
Country = "Argentina",
City = "Buenos Aires",
Amount = 4425,
Date = DateTime.Parse("2018/08/21")
},
new SaleInfo {
OrderId = 10524,
Region = "South America",
Country = "Paraguay",
City = "Asuncion",
Amount = 1360,
Date = DateTime.Parse("2018/08/22")
},
new SaleInfo {
OrderId = 10525,
Region = "Europe",
Country = "United Kingdom",
City = "London",
Amount = 3250,
Date = DateTime.Parse("2018/11/14")
},
new SaleInfo {
OrderId = 10526,
Region = "Europe",
Country = "Germany",
City = "Berlin",
Amount = 5550,
Date = DateTime.Parse("2018/12/21")
},
};
}
When your dataset includes multiple related tables, a Pivot Table can bring them together into a single view and allows users to explore their hierarchical structure. The following Pivot Table combines data and constructs a hierarchy for Orders, OrderDetails, Products, and Categories data tables.
@inject IDbContextFactory<NorthwindContext> NorthwindContextFactory
@implements IDisposable
@using DevExpress.Blazor.PivotTable
<DxPivotTable Data="Data">
<Fields>
<DxPivotTableField Field="CategoryName"
Area="@PivotTableArea.Row" />
<DxPivotTableField Field="ProductName"
Area="@PivotTableArea.Row" />
<DxPivotTableField Field="OrderDate"
Area="@PivotTableArea.Column"
GroupInterval="@PivotTableGroupInterval.DateYear"
Caption="Year" />
<DxPivotTableField Field="OrderDate"
Area="@PivotTableArea.Column"
GroupInterval="@PivotTableGroupInterval.DateQuarter"
Caption="Quarter">
<ValueTemplate>
<span>@($"Q{context.Text}")</span>
</ValueTemplate>
</DxPivotTableField>
<DxPivotTableField Field="UnitPrice"
Area="@PivotTableArea.Data"
SummaryType="@PivotTableSummaryType.Sum" />
<DxPivotTableField Field="ShipCountry"
Area="@PivotTableArea.Filter" />
<DxPivotTableField Field="ShipCity"
Area="@PivotTableArea.Filter" />
</Fields>
</DxPivotTable>
@code {
NorthwindContext Northwind { get; set; }
IEnumerable<object> Data { get; set; }
protected override async Task OnInitializedAsync() {
Northwind = NorthwindContextFactory.CreateDbContext();
var products = await Northwind.Products.ToListAsync();
var categories = await Northwind.Categories.ToListAsync();
var orders = await Northwind.Orders.ToListAsync();
var orderDetails = await Northwind.OrderDetails.ToListAsync();
Data = (from c in categories
join p in products on c.CategoryId equals p.CategoryId
join od in orderDetails on p.ProductId equals od.ProductId
select new {
CategoryName = c.CategoryName,
ProductName = p.ProductName,
UnitPrice = p.UnitPrice,
OrderDate = od.Order.OrderDate,
ShipCountry = od.Order.ShipCountry,
ShipCity = od.Order.ShipCity
})
.ToList();
}
public void Dispose() {
Northwind?.Dispose();
}
}
View Example: Pivot Table - Data binding using Entity Framework Core
The Blazor Pivot Table allows users to customize their data layout by dragging field headers between Rows , Columns , Data , and Filter areas. This capability helps users personalize the Pivot Table structure without modifying code.
Run Demo: Pivot Table - Field Customization
The Pivot Table also includes a Field List that offers an alternative way to manage component structure:
To display the Field List, call the ShowFieldList() method. The following code adds a button that displays the Field List:
@rendermode InteractiveServer
<DxButton Text="Show Field List" Click="OnShowFieldList"/>
<DxPivotTable Data="SalesData" @ref=MyPivotTable>
<Fields>
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Region)"
Area="@PivotTableArea.Row"
AreaIndex="0" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Country)"
Area="@PivotTableArea.Row"
SortOrder="@PivotTableSortOrder.Descending"
AreaIndex="1" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Date)"
GroupInterval="@PivotTableGroupInterval.DateYear"
Area="@PivotTableArea.Column"
AreaIndex="0"
Caption="Year" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Date)"
GroupInterval="@PivotTableGroupInterval.DateQuarter"
Area="@PivotTableArea.Column"
AreaIndex="1"
Caption="Quarter" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Amount)"
SortOrder="@PivotTableSortOrder.Ascending"
Area="@PivotTableArea.Data"
SummaryType="@PivotTableSummaryType.Sum" />
<DxPivotTableField Field="@nameof(Sales.SaleInfo.City)"
Area="@PivotTableArea.Filter"/>
</Fields>
</DxPivotTable>
@code {
IPivotTable MyPivotTable { get; set; }
IEnumerable<Sales.SaleInfo> SalesData;
protected override async Task OnInitializedAsync() {
SalesData = await Sales.GetSalesAsync();
}
void OnShowFieldList() {
MyPivotTable.ShowFieldList();
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
public class Sales {
static IList<SaleInfo> dataSource;
public class SaleInfo {
public int OrderId { get; set; }
public string Region { get; set; }
public string Country { get; set; }
public string City { get; set; }
public int Amount { get; set; }
public DateTime Date { get; set; }
}
static Sales() {
CreateDataSource();
}
public static Task<IQueryable<SaleInfo>> GetSalesAsync() {
return Task.FromResult(dataSource.AsQueryable());
}
static void CreateDataSource() {
dataSource = new List<SaleInfo> {
new SaleInfo {
OrderId = 10248,
Region = "North America",
Country = "United States",
City = "New York",
Amount = 1740,
Date = DateTime.Parse("2017/01/06")
},
new SaleInfo {
OrderId = 10249,
Region = "North America",
Country = "United States",
City = "Los Angeles",
Amount = 850,
Date = DateTime.Parse("2017/02/13")
},
new SaleInfo {
OrderId = 10250,
Region = "North America",
Country = "United States",
City = "Denver",
Amount = 2235,
Date = DateTime.Parse("2017/02/07")
},
new SaleInfo {
OrderId = 10251,
Region = "North America",
Country = "Canada",
City = "Vancouver",
Amount = 1965,
Date = DateTime.Parse("2017/03/03")
},
new SaleInfo {
OrderId = 10252,
Region = "North America",
Country = "Canada",
City = "Edmonton",
Amount = 880,
Date = DateTime.Parse("2017/03/10")
},
new SaleInfo {
OrderId = 10253,
Region = "South America",
Country = "Brazil",
City = "Rio de Janeiro",
Amount = 5260,
Date = DateTime.Parse("2017/01/17")
},
new SaleInfo {
OrderId = 10254,
Region = "South America",
Country = "Argentina",
City = "Buenos Aires",
Amount = 2790,
Date = DateTime.Parse("2017/05/21")
},
new SaleInfo {
OrderId = 10255,
Region = "South America",
Country = "Paraguay",
City = "Asuncion",
Amount = 3140,
Date = DateTime.Parse("2017/05/01")
},
new SaleInfo {
OrderId = 10256,
Region = "Europe",
Country = "United Kingdom",
City = "London",
Amount = 6175,
Date = DateTime.Parse("2017/06/24")
},
new SaleInfo {
OrderId = 10257,
Region = "Europe",
Country = "Germany",
City = "Berlin",
Amount = 4575,
Date = DateTime.Parse("2017/06/11")
},
new SaleInfo {
OrderId = 10517,
Region = "North America",
Country = "United States",
City = "New York",
Amount = 7710,
Date = DateTime.Parse("2018/07/18")
},
new SaleInfo {
OrderId = 10518,
Region = "North America",
Country = "United States",
City = "Los Angeles",
Amount = 7975,
Date = DateTime.Parse("2018/01/10")
},
new SaleInfo {
OrderId = 10519,
Region = "North America",
Country = "United States",
City = "Denver",
Amount = 3285,
Date = DateTime.Parse("2018/03/13")
},
new SaleInfo {
OrderId = 10520,
Region = "North America",
Country = "Canada",
City = "Vancouver",
Amount = 2580,
Date = DateTime.Parse("2018/04/22")
},
new SaleInfo {
OrderId = 10521,
Region = "North America",
Country = "Canada",
City = "Edmonton",
Amount = 2160,
Date = DateTime.Parse("2018/05/26")
},
new SaleInfo {
OrderId = 10522,
Region = "South America",
Country = "Brazil",
City = "Rio de Janeiro",
Amount = 1100,
Date = DateTime.Parse("2018/05/25")
},
new SaleInfo {
OrderId = 10523,
Region = "South America",
Country = "Argentina",
City = "Buenos Aires",
Amount = 4425,
Date = DateTime.Parse("2018/08/21")
},
new SaleInfo {
OrderId = 10524,
Region = "South America",
Country = "Paraguay",
City = "Asuncion",
Amount = 1360,
Date = DateTime.Parse("2018/08/22")
},
new SaleInfo {
OrderId = 10525,
Region = "Europe",
Country = "United Kingdom",
City = "London",
Amount = 3250,
Date = DateTime.Parse("2018/11/14")
},
new SaleInfo {
OrderId = 10526,
Region = "Europe",
Country = "Germany",
City = "Berlin",
Amount = 5550,
Date = DateTime.Parse("2018/12/21")
},
};
}
Each data cell in the Pivot Table represents a summarized value at the intersection of a Row field and a Column field. Follow the steps below to define data cells.
Quantity, SalesAmount, or Price). Add this field to the Fields collection and set the Area property to Data.<DxPivotTable Data="SalesData">
<Fields>
@*...*@
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Amount)"
SortOrder="@PivotTableSortOrder.Ascending"
Area="@PivotTableArea.Data"
SummaryType="@PivotTableSummaryType.Sum" />
</Fields>
</DxPivotTable>
Commonly used summary types are:
| Summary Type | Description |
|---|---|
| Sum | |
| (the default type) | The sum of field values. This type applies to numeric fields only. |
| Avg | The average of field values. This type applies to numeric fields only. |
| Count | The total number of field values. |
| Max | The largest field value. |
| Min | The smallest field value. |
| Other types |
You can use the CellFormat property to specify the display format for data cells.
The DevExpress Blazor Pivot Table calculates totals for its data and displays them as separate columns and rows. There are four types of totals:
You can use the following properties to change totals visibility:
The following code hides row and column grand totals:
<DxPivotTable Data="SalesData"
ShowColumnGrandTotals="false"
ShowRowGrandTotals="false">
<Fields>
@*...*@
</Fields>
</DxPivotTable>
You can also use the following properties to set the position of row/column totals relative to rows/columns:
The following code changes the position of row and column totals to Before:
<DxPivotTable Data="SalesData"
RowTotalsPosition="PivotTableRowTotalsPosition.Before"
ColumnTotalsPosition="PivotTableColumnTotalsPosition.Before">
<Fields>
@*...*@
</Fields>
</DxPivotTable>
Run Demo: Pivot Table - Totals
The control applies the following default widths to cells:
Use the Width property to set cell widths for fields. The Width property behavior depends on the area where the field is located:
<DxPivotTable Data="SalesData">
<Fields>
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Region)"
Area="@PivotTableArea.Row"
AreaIndex="0"
Width="100"/>
<DxPivotTableField Field="@nameof(Sales.SaleInfo.Amount)"
SortOrder="@PivotTableSortOrder.Ascending"
Area="@PivotTableArea.Data"
SummaryType="@PivotTableSummaryType.Sum"
Width="80"/>
@*...*@
</Fields>
</DxPivotTable>