Custom Formatting

• Jun 07, 2019
• 4 minutes to read

The Pivot Grid control allows you to format the display text of its summary cells, field values, total and grand total headers according to a custom formatting rule.

Formatting Data Cells

The PivotGridControl allows custom text to be displayed within individual data cells. To do this, handle the PivotGridControl.CustomCellDisplayText event. This event is fired for each cell in bound and unbound fields.

Each data cell displays a summary value calculated against a data field. This data field is returned by the event parameter’s PivotCellBaseEventArgs.DataField property. The cell’s current value is returned by the event parameter’s PivotCellBaseEventArgs.Value property.

To change the cell’s display text, use the PivotCellDisplayTextEventArgs.DisplayText property. Initially, this property contains the text currently displayed within a cell. To provide a custom text, assign it to the PivotCellDisplayTextEventArgs.DisplayText property.

Formatting Column and Row Headers

The PivotGridControl allows you to customize the text displayed within column or row headers (field values, total and grand total headers etc.). To do this, handle the PivotGridControl.FieldValueDisplayText event. The current field value is obtained using the event parameter’s PivotFieldDisplayTextEventArgs.Value property (if the event is fired for a grand total header or data field header, the PivotFieldDisplayTextEventArgs.Value property returns null). The custom display text should be assigned to the PivotFieldDisplayTextEventArgs.DisplayText property.

If the PivotGridControl.FieldValueDisplayText event is raised for a field value or total header, the PivotFieldEventArgs.Field property identifies the corresponding column field or row field. If this event is raised for a data field header, the PivotFieldEventArgs.Field property identifies the corresponding data field. If the event is raised for a grand total header, the PivotFieldEventArgs.Field property returns null. To get the type of the processed header, use the PivotFieldValueEventArgs.ValueType property.

The PivotGridControl.FieldValueDisplayText event is also raised for items in the filter dropdown. In this instance, the event parameter’s PivotFieldDisplayTextEventArgs.IsPopulatingFilterDropdown property is set to true. The PivotFieldEventArgs.Field parameter returns the column, row or filter field whose dropdown has been invoked.

Example: How to Display Custom Text within Data Cells

The following example shows how to provide custom text for the PivotGridControl's cells by handling the PivotGridControl.CustomCellDisplayText event.In this example, if a grand total value is less than 50 000, 'Low' is displayed instead. If the value exceeds 100 000, 'High' is displayed; otherwise, 'Middle'.

MainWindow.xaml

<Window xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" 
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
        x:Class="DXPivotGrid_FormattingViaEvents_VB.MainWindow" 
        Title="MainWindow" Loaded="Window_Loaded" 
        Height="430" Width="525">
    <Grid>
        <dxpg:PivotGridControl CustomCellDisplayText="pivotGridControl1_CustomCellDisplayText" 
                               HorizontalAlignment="Left" Name="pivotGridControl1" 
                               VerticalAlignment="Top">
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Name="fieldCountry" FieldName="Country" Area="RowArea" />
                <dxpg:PivotGridField Name="fieldCustomer" FieldName="Sales Person" Area="RowArea" 
                                     Caption="Customer" />
                <dxpg:PivotGridField Name="fieldYear" FieldName="OrderDate" Area="ColumnArea" 
                                     Caption="Year" GroupInterval="DateYear" />
                <dxpg:PivotGridField Name="fieldCategoryName" FieldName="CategoryName" 
                                     Area="ColumnArea" Caption="Product Category" />
                <dxpg:PivotGridField Name="fieldProductName" FieldName="ProductName" Area="FilterArea" 
                                     Caption="Product Name" />
                <dxpg:PivotGridField Name="fieldExtendedPrice" FieldName="Extended Price" 
                                     Area="DataArea" CellFormat="c0" />
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>

MainWindow.xaml.vb

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows
Imports DevExpress.Xpf.PivotGrid
Imports DevExpress.XtraPivotGrid
Imports DXPivotGrid_FormattingViaEvents_VB.nwindDataSetTableAdapters

Namespace DXPivotGrid_FormattingViaEvents_VB
    Partial Public Class MainWindow
        Inherits Window
        Private salesPersonDataTable As New nwindDataSet.SalesPersonDataTable()
        Private salesPersonDataAdapter As New SalesPersonTableAdapter()
        Public Sub New()
            InitializeComponent()
            pivotGridControl1.DataSource = salesPersonDataTable
            pivotGridControl1.PrefilterString = "[Country] == 'UK' OR [Country] == 'USA'"

        End Sub
        Protected Sub pivotGridControl1_CustomCellDisplayText(ByVal sender As Object, _
                                                    ByVal e As PivotCellDisplayTextEventArgs)
            If e.RowValueType <> PivotGridValueType.GrandTotal OrElse
                e.ColumnValueType = PivotGridValueType.GrandTotal OrElse
                e.ColumnValueType = PivotGridValueType.Total Then
                Return
            End If
            If Convert.ToSingle(e.Value) < 50000 Then
                e.DisplayText = "Low"
            ElseIf Convert.ToSingle(e.Value) > 100000 Then
                e.DisplayText = "High"
            Else
                e.DisplayText = "Middle"
            End If
        End Sub
        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            salesPersonDataAdapter.Fill(salesPersonDataTable)
        End Sub
    End Class
End Namespace

MainWindow.xaml.cs

using System;
using System.Windows;
using DevExpress.Xpf.PivotGrid;
using FormattingViaEvents.DataSet1TableAdapters;

namespace FormattingViaEvents {
    public partial class MainWindow : Window {
        FormattingViaEvents.DataSet1.SalesPersonDataTable salesPersonDataTable =
            new FormattingViaEvents.DataSet1.SalesPersonDataTable();
        SalesPersonTableAdapter salesPersonDataAdapter = new SalesPersonTableAdapter();
        public MainWindow() {
            InitializeComponent();
            pivotGridControl1.DataSource = salesPersonDataTable;
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
            salesPersonDataAdapter.Fill(salesPersonDataTable);
        }
        private void pivotGridControl1_CustomCellDisplayText(object sender,
            PivotCellDisplayTextEventArgs e) {
                if (e.RowValueType != FieldValueType.GrandTotal ||
                    e.ColumnValueType == FieldValueType.GrandTotal ||
                    e.ColumnValueType == FieldValueType.Total) return;
                if (Convert.ToSingle(e.Value) < 50000)
                    e.DisplayText = "Low";
                else if (Convert.ToSingle(e.Value) > 100000)
                    e.DisplayText = "High";
                else
                    e.DisplayText = "Middle";
        }
    }
}