aspnet-9220-components-pivot-grid-examples-layout-how-to-split-field-value-cells.md
The following example demonstrates how to split field value cells. In this example, the Grand Total column header is split into two cells: Price and Count. To do this, handle the CustomFieldValueCells event and use the event parameter’s Split method. Cells that should be split are identified by a predicate that returns true for those cells. The quantity, size and captions of newly created cells are specified by an array of cell definitions (the FieldValueSplitData objects).
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:RadioButtonList ID="radioButtonList" runat="server"
AutoPostBack="true" CellSpacing="10" >
<asp:ListItem Selected="True">Default Layout</asp:ListItem>
<asp:ListItem>Split Grand Total Column Header</asp:ListItem>
</asp:RadioButtonList>
<div>
<dx:ASPxPivotGrid ID="pivotGrid" runat="server" Width="500px"
OnFieldValueDisplayText="pivotGrid_FieldValueDisplayText"
OnCustomFieldValueCells="pivotGrid_CustomFieldValueCells"
OptionsCustomization-AllowFilter="False"
OptionsCustomization-AllowDrag="False">
</dx:ASPxPivotGrid>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Web.UI;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraPivotGrid.Data;
namespace ASPxPivotGrid_SplittingCells {
public partial class _Default : Page {
protected void Page_Load(object sender, EventArgs e) {
if (!IsCallback && !IsPostBack) {
PivotHelper.FillPivot(pivotGrid);
}
pivotGrid.DataSource = PivotHelper.GetDataTable();
}
protected void pivotGrid_CustomFieldValueCells(object sender,
PivotCustomFieldValueCellsEventArgs e) {
if (pivotGrid.DataSource == null) return;
if (radioButtonList.SelectedIndex == 0) return;
// Creates a predicate that returns true for the Grand Total
// headers, and false for any other column/row header.
// Only cells that match this predicate are split.
Predicate<FieldValueCell> condition =
new Predicate<FieldValueCell>(delegate(FieldValueCell matchCell) {
return matchCell.ValueType == PivotGridValueType.GrandTotal &&
matchCell.Field == null;
});
// Creates a list of cell definitions that represent newly created cells.
// Two definitions are added to the list. The first one identifies
// the Price cell, which has two nested cells (the Retail Price and Wholesale Price
// data field headers). The second one identifies the Count cell with
// one nested cell (the Quantity data field header).
List<FieldValueSplitData> cells = new List<FieldValueSplitData>(2);
cells.Add(new FieldValueSplitData("Price", 2));
cells.Add(new FieldValueSplitData("Count", 1));
// Performs splitting.
e.Split(true, condition, cells);
}
protected void pivotGrid_FieldValueDisplayText(object sender,
PivotFieldDisplayTextEventArgs e) {
if (e.Field == pivotGrid.Fields[PivotHelper.Month] && e.Value is int) {
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
}
}
}
}
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Web.UI
Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraPivotGrid.Data
Namespace ASPxPivotGrid_SplittingCells
Partial Public Class _Default
Inherits Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If (Not IsCallback) AndAlso (Not IsPostBack) Then
PivotHelper.FillPivot(pivotGrid)
End If
pivotGrid.DataSource = PivotHelper.GetDataTable()
End Sub
Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object, _
ByVal e As PivotCustomFieldValueCellsEventArgs)
If pivotGrid.DataSource Is Nothing Then
Return
End If
If radioButtonList.SelectedIndex = 0 Then
Return
End If
' Creates a predicate that returns true for the Grand Total
' headers, and false for any other column/row header.
' Only cells that match this predicate are split.
Dim condition As New Predicate(Of FieldValueCell)(AddressOf AnonymousMethod1)
' Creates a list of cell definitions that represent newly created cells.
' Two definitions are added to the list. The first one identifies
' the Price cell, which has two nested cells (the Retail Price and Wholesale Price
' data field headers). The second one identifies the Count cell with
' one nested cell (the Quantity data field header).
Dim cells As New List(Of FieldValueSplitData)(2)
cells.Add(New FieldValueSplitData("Price", 2))
cells.Add(New FieldValueSplitData("Count", 1))
' Performs splitting.
e.Split(True, condition, cells)
End Sub
Private Function AnonymousMethod1(ByVal matchCell As FieldValueCell) As Boolean
Return matchCell.ValueType = PivotGridValueType.GrandTotal AndAlso _
matchCell.Field Is Nothing
End Function
Protected Sub pivotGrid_FieldValueDisplayText(ByVal sender As Object, _
ByVal e As PivotFieldDisplayTextEventArgs)
If e.Value Is Nothing Then
Return
End If
If Object.Equals(e.Field, pivotGrid.Fields(PivotHelper.Month)) Then
e.DisplayText = _
CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt(Fix(e.Value)))
End If
End Sub
End Class
End Namespace
using System.Data;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;
namespace ASPxPivotGrid_SplittingCells {
public static class PivotHelper {
public const string Employee = "Employee";
public const string Widget = "Widget";
public const string Month = "Month";
public const string RetailPrice = "Retail Price";
public const string WholesalePrice = "Wholesale Price";
public const string Quantity = "Quantity";
public const string Remains = "Remains";
public const string EmployeeA = "Employee A";
public const string EmployeeB = "Employee B";
public const string WidgetA = "Widget A";
public const string WidgetB = "Widget B";
public const string WidgetC = "Widget C";
public static void FillPivot(ASPxPivotGrid pivot) {
pivot.Fields.Add(Employee, PivotArea.RowArea);
pivot.Fields.Add(Widget, PivotArea.RowArea);
pivot.Fields.Add(Month, PivotArea.ColumnArea).AreaIndex = 0;
pivot.Fields.Add(RetailPrice, PivotArea.DataArea);
pivot.Fields.Add(WholesalePrice, PivotArea.DataArea);
pivot.Fields.Add(Quantity, PivotArea.DataArea);
foreach (PivotGridField field in pivot.Fields) {
field.AllowedAreas = GetAllowedArea(field.Area);
}
pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Far;
pivot.OptionsView.ColumnTotalsLocation = PivotTotalsLocation.Far;
pivot.OptionsDataField.Area = PivotDataArea.ColumnArea;
pivot.OptionsDataField.AreaIndex = 1;
}
static PivotGridAllowedAreas GetAllowedArea(PivotArea area) {
switch (area) {
case PivotArea.ColumnArea:
return PivotGridAllowedAreas.ColumnArea;
case PivotArea.RowArea:
return PivotGridAllowedAreas.RowArea;
case PivotArea.DataArea:
return PivotGridAllowedAreas.DataArea;
case PivotArea.FilterArea:
return PivotGridAllowedAreas.FilterArea;
default:
return PivotGridAllowedAreas.All;
}
}
public static DataTable GetDataTable() {
DataTable table = new DataTable();
table.Columns.Add(Employee, typeof(string));
table.Columns.Add(Widget, typeof(string));
table.Columns.Add(Month, typeof(int));
table.Columns.Add(RetailPrice, typeof(double));
table.Columns.Add(WholesalePrice, typeof(double));
table.Columns.Add(Quantity, typeof(int));
table.Columns.Add(Remains, typeof(int));
table.Rows.Add(EmployeeA, WidgetA, 6, 45.6, 40, 3);
table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6);
table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7);
table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5);
table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4);
table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5);
table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2);
table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1);
table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10);
table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4);
table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6);
table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7);
return table;
}
}
}
Imports Microsoft.VisualBasic
Imports System.Data
Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid
Namespace ASPxPivotGrid_SplittingCells
Public NotInheritable Class PivotHelper
Public Const Employee As String = "Employee"
Public Const Widget As String = "Widget"
Public Const Month As String = "Month"
Public Const RetailPrice As String = "Retail Price"
Public Const WholesalePrice As String = "Wholesale Price"
Public Const Quantity As String = "Quantity"
Public Const Remains As String = "Remains"
Public Const EmployeeA As String = "Employee A"
Public Const EmployeeB As String = "Employee B"
Public Const WidgetA As String = "Widget A"
Public Const WidgetB As String = "Widget B"
Public Const WidgetC As String = "Widget C"
Private Sub New()
End Sub
Public Shared Sub FillPivot(ByVal pivot As ASPxPivotGrid)
pivot.Fields.Add(Employee, PivotArea.RowArea)
pivot.Fields.Add(Widget, PivotArea.RowArea)
pivot.Fields.Add(Month, PivotArea.ColumnArea).AreaIndex = 0
pivot.Fields.Add(RetailPrice, PivotArea.DataArea)
pivot.Fields.Add(WholesalePrice, PivotArea.DataArea)
pivot.Fields.Add(Quantity, PivotArea.DataArea)
For Each field As PivotGridField In pivot.Fields
field.AllowedAreas = GetAllowedArea(field.Area)
Next field
pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Far
pivot.OptionsView.ColumnTotalsLocation = PivotTotalsLocation.Far
pivot.OptionsDataField.Area = PivotDataArea.ColumnArea
pivot.OptionsDataField.AreaIndex = 1
End Sub
Private Shared Function GetAllowedArea(ByVal area As PivotArea) As PivotGridAllowedAreas
Select Case area
Case PivotArea.ColumnArea
Return PivotGridAllowedAreas.ColumnArea
Case PivotArea.RowArea
Return PivotGridAllowedAreas.RowArea
Case PivotArea.DataArea
Return PivotGridAllowedAreas.DataArea
Case PivotArea.FilterArea
Return PivotGridAllowedAreas.FilterArea
Case Else
Return PivotGridAllowedAreas.All
End Select
End Function
Public Shared Function GetDataTable() As DataTable
Dim table As New DataTable()
table.Columns.Add(Employee, GetType(String))
table.Columns.Add(Widget, GetType(String))
table.Columns.Add(Month, GetType(Integer))
table.Columns.Add(RetailPrice, GetType(Double))
table.Columns.Add(WholesalePrice, GetType(Double))
table.Columns.Add(Quantity, GetType(Integer))
table.Columns.Add(Remains, GetType(Integer))
table.Rows.Add(EmployeeA, WidgetA, 6, 45.6, 40, 3)
table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6)
table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7)
table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5)
table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4)
table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5)
table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2)
table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1)
table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10)
table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4)
table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6)
table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7)
Return table
End Function
End Class
End Namespace