TopBottomRuleFormatCondition Class

Represents a top/bottom format condition.

Namespace : DevExpress.Xpf.PivotGrid

Assembly : DevExpress.Xpf.PivotGrid.v25.2.dll

NuGet Package : DevExpress.Wpf.PivotGrid

Declaration

C#
VB.NET

csharp

public class TopBottomRuleFormatCondition :
    ExpressionConditionBase

Public Class TopBottomRuleFormatCondition
    Inherits ExpressionConditionBase

Remarks

The TopBottomRuleFormatCondition object allows you to find cell values that are above or below the column’s average or find the highest or lowest column values based on a cutoff value.

The TopBottomRuleFormatCondition class is inherited from the FormatConditionBase base class and represents a top/bottom rule format condition. To apply condition formatting, create a new TopBottomRuleFormatCondition instance, specify its parameters and add it to the PivotGridControl.FormatConditions collection.

The type of TopBottomRuleFormatCondition rule is specified by the TopBottomRuleFormatCondition.Rule property. To specify the number or percentage of cells to format, use the TopBottomRuleFormatCondition.Threshold property.

For each condition you can select one of the predefined formats or apply a custom format.

Use the FormatConditionBase.PredefinedFormatName property to select one of the predefined formats.
To apply a custom format, create a new TopBottomRuleFormatCondition instance, specify its parameters and assign this instance to the ExpressionConditionBase.Format property.

The following code sample shows how to apply the TopBottom format condition with the predefined format and specified rule.

C#
VB.NET

csharp

using DevExpress.Xpf.PivotGrid;
using DevExpress.Xpf.Core.ConditionalFormatting;

public MainWindow()
{
    // ...

    // Creates a new TopBottomRuleFormatCondition instance.
    TopBottomRuleFormatCondition formatRuleTopBottom = new TopBottomRuleFormatCondition();

    // Configures the format condition.
    formatRuleTopBottom.ApplyToSpecificLevel = true;
    formatRuleTopBottom.ColumnName = "fieldQuarter";
    formatRuleTopBottom.RowName = "fieldSalesPerson";
    formatRuleTopBottom.MeasureName = "fieldExtendedPrice";
    formatRuleTopBottom.Rule = TopBottomRule.BottomItems;
    formatRuleTopBottom.Threshold = 10;
    formatRuleTopBottom.PredefinedFormatName = "LightRedFillWithDarkRedText";

    // Adds this instance to the FormatConditionCollection.
    pivotGridControl1.AddFormatCondition(formatRuleTopBottom);
}

Imports DevExpress.Xpf.PivotGrid
Imports DevExpress.Xpf.Core.ConditionalFormatting

Public Sub New()
    ' ...

    ' Creates a new TopBottomRuleFormatCondition instance.
    Dim formatRuleTopBottom As New TopBottomRuleFormatCondition()

    ' Configures the format condition.
    formatRuleTopBottom.ApplyToSpecificLevel = True
    formatRuleTopBottom.ColumnName = "fieldQuarter"
    formatRuleTopBottom.RowName = "fieldSalesPerson"
    formatRuleTopBottom.MeasureName = "fieldExtendedPrice"
    formatRuleTopBottom.Rule = TopBottomRule.BottomItems
    formatRuleTopBottom.Threshold = 10
    formatRuleTopBottom.PredefinedFormatName = "LightRedFillWithDarkRedText"

    ' Adds this instance to the FormatConditionCollection.
    pivotGridControl1.AddFormatCondition(formatRuleTopBottom)
End Sub

Example

This example shows how to add format conditions to WPF Pivot Grid Control.

The Data Bar conditional formatting is applied to the ‘Extended Price’ measure and intersection of the ‘Sales Person’ and ‘Quarter’ fields. This condition formats data cells with a predefined orange gradient data bar.
The Top Bottom Rule conditional formatting is applied to the ‘Quantity’ measure and intersection of the ‘Sales Person’ and ‘Quarter’ fields. This condition formats data cells whose values are above average with green text and light green fill.
The Icon Set conditional formatting is applied to the ‘Extended Price’ measure and intersection of the ‘Sales Person’ and ‘Year’ fields. This condition displays a specific icon in a cell according to the range to which this cell value belongs.

The image below shows the result.

View Example

MainWindow.xaml.cs
MainWindow.xaml
MainWindow.xaml.vb

csharp

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

namespace WpfPivotGridConditionalFormatting
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            FilterFieldValues(fieldYear, new int[]{2016}, FieldFilterType.Included);

            // Creates a new DataBarFormatCondition instance.
            DataBarFormatCondition formatRulesDataBar = new DataBarFormatCondition();

            // Adds this instance to the FormatConditionCollection.
            pivotGridControl1.AddFormatCondition(formatRulesDataBar);

            // Specifies a column field.
            formatRulesDataBar.ColumnName = "fieldQuarter";

            // Specifies a row field.
            formatRulesDataBar.RowName = "fieldSalesPerson";

            // Specifies a data field.
            formatRulesDataBar.MeasureName = "fieldExtendedPrice";

            // Applies the condition to intersection of row and column fields.
            formatRulesDataBar.ApplyToSpecificLevel = true;

            // Sets the predefined format.
            formatRulesDataBar.PredefinedFormatName = "OrangeGradientDataBar";

        }

        private void FilterFieldValues(PivotGridField field, int[] filterValues, 
            FieldFilterType filterType)
        {
            pivotGridControl1.BeginUpdate();
            try
            {
                field.FilterValues.Clear(); 
                foreach (object filterValue in filterValues)
                    field.FilterValues.Add(filterValue);
            }
            finally
            {
                field.FilterValues.FilterType = filterType;
                pivotGridControl1.EndUpdate();
            }
        }
    }
}

xaml

<Window x:Class="WpfPivotGridConditionalFormatting.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
        xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core"
        xmlns:my="clr-namespace:WpfPivotGridConditionalFormatting.nwindDataSetTableAdapters" 
        xmlns:my1="clr-namespace:WpfPivotGridConditionalFormatting"
        dx:ThemeManager.Theme="Office2013LightGray" 
        Title="Pivot Grid Conditional Formatting" 
        Height="427" Width="755" >
    <Window.Resources>
        <dx:TypedSimpleSource x:Key="TypedSimpleSource" 
                              AdapterType="my:SalesPersonTableAdapter" 
                              ContextType="my1:nwindDataSet" 
                              Path="SalesPerson">
            <dx:DesignDataManager.DesignData>
                <dx:DesignDataSettings RowCount="5" />
            </dx:DesignDataManager.DesignData>
        </dx:TypedSimpleSource>
    </Window.Resources>
    <Grid>
        <dxpg:PivotGridControl Name="pivotGridControl1" 
                               DataSource="{Binding Path=Data, Source={StaticResource TypedSimpleSource}}" 
                               AllowConditionalFormattingMenu="True">
            <dxpg:PivotGridControl.FormatConditions>
                <dxpg:IconSetFormatCondition ApplyToSpecificLevel="True" 
                                             MeasureName="fieldExtendedPrice" 
                                             RowName="fieldSalesPerson" ColumnName="fieldYear">
                    <dxpg:IconSetFormatCondition.Format>
                    <dx:IconSetFormat>
                        <dx:IconSetElement Threshold="66.666666666666671" ThresholdComparisonType="GreaterOrEqual">
                            <dx:IconSetElement.Icon>
                                <BitmapImage UriCachePolicy="{x:Null}" 
        UriSource="pack://application:,,,/DevExpress.Xpf.Core.v15.1;component/Core/ConditionalFormatting/Images/IconSets/Arrows3_1.png" />
                            </dx:IconSetElement.Icon>
                        </dx:IconSetElement>
                        <dx:IconSetElement Threshold="33.333333333333336" ThresholdComparisonType="GreaterOrEqual">
                            <dx:IconSetElement.Icon>
                                <BitmapImage UriCachePolicy="{x:Null}" 
         UriSource="pack://application:,,,/DevExpress.Xpf.Core.v15.1;component/Core/ConditionalFormatting/Images/IconSets/Arrows3_2.png" />
                            </dx:IconSetElement.Icon>
                        </dx:IconSetElement>
                        <dx:IconSetElement Threshold="0" ThresholdComparisonType="GreaterOrEqual">
                            <dx:IconSetElement.Icon>
                                <BitmapImage UriCachePolicy="{x:Null}" 
         UriSource="pack://application:,,,/DevExpress.Xpf.Core.v15.1;component/Core/ConditionalFormatting/Images/IconSets/Arrows3_3.png" />
                            </dx:IconSetElement.Icon>
                        </dx:IconSetElement>
                        </dx:IconSetFormat>
                    </dxpg:IconSetFormatCondition.Format>
                </dxpg:IconSetFormatCondition>
                <dxpg:TopBottomRuleFormatCondition ApplyToSpecificLevel="True" 
                                                   ColumnName="fieldQuarter" 
                                                   MeasureName="fieldQuantity" 
                                                   RowName="fieldSalesPerson" 
                                                   Rule="TopItems">
                    <dxpg:TopBottomRuleFormatCondition.Format>
                        <dx:Format Background="LightGreen" Foreground="Green" />
                    </dxpg:TopBottomRuleFormatCondition.Format>
                </dxpg:TopBottomRuleFormatCondition>
            </dxpg:PivotGridControl.FormatConditions>
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Area="RowArea" FieldName="Country" 
                                     Name="fieldCountry" AreaIndex="0" />
                <dxpg:PivotGridField Area="DataArea" FieldName="Extended Price" 
                                     Name="fieldExtendedPrice" AreaIndex="0" />
                <dxpg:PivotGridField Area="ColumnArea" Caption="Year" FieldName="OrderDate"
                                     Name="fieldYear" GroupInterval="DateYear" AreaIndex="0" />
                <dxpg:PivotGridField Area="ColumnArea" Caption="Quarter" FieldName="OrderDate" 
                                     Name="fieldQuarter" GroupInterval="DateQuarter" AreaIndex="1" ValueFormat="Quarter {0}" />
                <dxpg:PivotGridField Area="DataArea" FieldName="Quantity" 
                                     Name="fieldQuantity" AreaIndex="1" />
                <dxpg:PivotGridField Area="RowArea" FieldName="Sales Person" 
                                     Name="fieldSalesPerson" AreaIndex="1" />
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>

Imports System.Windows
Imports System
Imports DevExpress.Xpf.PivotGrid

Namespace WpfPivotGridConditionalFormatting
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()

            FilterFieldValues(fieldYear, New Integer(){2016}, FieldFilterType.Included)

            ' Creates a new DataBarFormatCondition instance.
            Dim formatRulesDataBar As New DataBarFormatCondition()

            ' Adds this instance to the FormatConditionCollection.
            pivotGridControl1.AddFormatCondition(formatRulesDataBar)

            ' Specifies a column field.
            formatRulesDataBar.ColumnName = "fieldQuarter"

            ' Specifies a row field.
            formatRulesDataBar.RowName = "fieldSalesPerson"

            ' Specifies a data field.
            formatRulesDataBar.MeasureName = "fieldExtendedPrice"

            ' Applies the condition to intersection of row and column fields.
            formatRulesDataBar.ApplyToSpecificLevel = True

            ' Sets the predefined format.
            formatRulesDataBar.PredefinedFormatName = "OrangeGradientDataBar"

        End Sub

        Private Sub FilterFieldValues(ByVal field As PivotGridField,
                                      ByVal filterValues() As Integer,
                                      ByVal filterType As FieldFilterType)
            pivotGridControl1.BeginUpdate()
            Try
                field.FilterValues.Clear()
                For Each filterValue As Object In filterValues
                    field.FilterValues.Add(filterValue)
                Next filterValue
            Finally
                field.FilterValues.FilterType = filterType
                pivotGridControl1.EndUpdate()
            End Try
        End Sub
    End Class
End Namespace

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the TopBottomRuleFormatCondition class.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

XAML

wpf-pivot-grid-apply-format-conditions-to-data-cells/CS/WpfPivotGridConditionalFormatting/MainWindow.xaml#L29

xml

RowName="fieldSalesPerson" ColumnName="fieldYear" PredefinedFormatName="Arrows3ColoredIconSet"/>
<dxpg:TopBottomRuleFormatCondition ApplyToSpecificLevel="true"
                                   ColumnName="fieldQuarter"

Inheritance

Object DispatcherObject DependencyObject FormatConditionBase ExpressionConditionBase TopBottomRuleFormatCondition

TopBottomRuleFormatCondition Class

TopBottomRuleFormatCondition Class

Declaration

Remarks

Example

Related GitHub Examples

Inheritance