How to: Apply Conditional Formatting to Pivot Cells

• Mar 31, 2018
• 2 minutes to read

The following example demonstrates how to apply conditional formatting to Pivot cells at different detail levels.

• The Value format condition (FormatConditionValue) is used to apply formatting to the first-level data cells.
• The Range Gradient condition (FormatConditionRangeGradient) is used to highlight the last-level data cells
• The Color Range condition (FormatConditionRangeSet) is used to classify values of Grand Total cells.
• The Top-Bottom condition (FormatConditionTopBottom) highlights row field values corresponding to top 3 categories.

Note

The complete sample project How to Apply Conditional Formatting to Pivot Cells is available in the DevExpress Examples repository.

Form1.cs

using DevExpress.XtraEditors;
using DevExpress.DashboardCommon;

namespace Pivot_ConditionalFormatting {
    public partial class Form1 : XtraForm {
        public Form1() {
            InitializeComponent();
            Dashboard dashboard = new Dashboard();
            dashboard.LoadFromXml(@"..\..\Data\Dashboard.xml");
            PivotDashboardItem pivot = (PivotDashboardItem)dashboard.Items["pivotDashboardItem1"];

            PivotItemFormatRule firstLevelRule = new PivotItemFormatRule(pivot.Values[0]);
            FormatConditionValue greaterThanCondition = new FormatConditionValue();
            greaterThanCondition.Condition = DashboardFormatCondition.Greater;
            greaterThanCondition.Value1 = 30000;
            greaterThanCondition.StyleSettings =
                new AppearanceSettings(FormatConditionAppearanceType.Green);
            firstLevelRule.Condition = greaterThanCondition;
            firstLevelRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.FirstLevel;

            PivotItemFormatRule lastLevelRule = new PivotItemFormatRule(pivot.Values[0]);
            FormatConditionRangeGradient rangeCondition =
                new FormatConditionRangeGradient(FormatConditionRangeGradientPredefinedType.WhiteGreen);
            lastLevelRule.Condition = rangeCondition;
            lastLevelRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.LastLevel;

            PivotItemFormatRule grandTotalRule = new PivotItemFormatRule(pivot.Values[0]);
            FormatConditionRangeSet rangeTotalCondition = 
                new FormatConditionRangeSet(FormatConditionRangeSetPredefinedType.ColorsPaleRedGreenBlue);
            grandTotalRule.Condition = rangeTotalCondition;
            grandTotalRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.SpecificLevel;
            grandTotalRule.Level.Row = pivot.Rows[0];

            PivotItemFormatRule topCategoryRule = new PivotItemFormatRule(pivot.Values[0]);
            FormatConditionTopBottom topCondition = new FormatConditionTopBottom();
            topCondition.TopBottom = DashboardFormatConditionTopBottomType.Top;
            topCondition.RankType = DashboardFormatConditionValueType.Number;
            topCondition.Rank = 3;
            topCondition.StyleSettings = new IconSettings(FormatConditionIconType.RatingFullGrayStar);
            topCategoryRule.Condition = topCondition;
            topCategoryRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.SpecificLevel;
            topCategoryRule.Level.Row = pivot.Rows[0];
            topCategoryRule.DataItemApplyTo = pivot.Rows[0];

            pivot.FormatRules.AddRange(firstLevelRule, lastLevelRule, grandTotalRule, topCategoryRule);
            dashboardViewer1.Dashboard = dashboard;
        }
    }
}

Form1.vb

Imports DevExpress.XtraEditors
Imports DevExpress.DashboardCommon

Namespace Pivot_ConditionalFormatting
    Partial Public Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()
            Dim dashboard As New Dashboard()
            dashboard.LoadFromXml("..\..\Data\Dashboard.xml")
            Dim pivot As PivotDashboardItem = CType(dashboard.Items("pivotDashboardItem1"), PivotDashboardItem)

            Dim firstLevelRule As New PivotItemFormatRule(pivot.Values(0))
            Dim greaterThanCondition As New FormatConditionValue()
            greaterThanCondition.Condition = DashboardFormatCondition.Greater
            greaterThanCondition.Value1 = 30000
            greaterThanCondition.StyleSettings = New AppearanceSettings(FormatConditionAppearanceType.Green)
            firstLevelRule.Condition = greaterThanCondition
            firstLevelRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.FirstLevel

            Dim lastLevelRule As New PivotItemFormatRule(pivot.Values(0))
            Dim rangeCondition As New FormatConditionRangeGradient(FormatConditionRangeGradientPredefinedType.WhiteGreen)
            lastLevelRule.Condition = rangeCondition
            lastLevelRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.LastLevel

            Dim grandTotalRule As New PivotItemFormatRule(pivot.Values(0))
            Dim rangeTotalCondition As New FormatConditionRangeSet(FormatConditionRangeSetPredefinedType.ColorsPaleRedGreenBlue)
            grandTotalRule.Condition = rangeTotalCondition
            grandTotalRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.SpecificLevel
            grandTotalRule.Level.Row = pivot.Rows(0)

            Dim topCategoryRule As New PivotItemFormatRule(pivot.Values(0))
            Dim topCondition As New FormatConditionTopBottom()
            topCondition.TopBottom = DashboardFormatConditionTopBottomType.Top
            topCondition.RankType = DashboardFormatConditionValueType.Number
            topCondition.Rank = 3
            topCondition.StyleSettings = New IconSettings(FormatConditionIconType.RatingFullGrayStar)
            topCategoryRule.Condition = topCondition
            topCategoryRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.SpecificLevel
            topCategoryRule.Level.Row = pivot.Rows(0)
            topCategoryRule.DataItemApplyTo = pivot.Rows(0)

            pivot.FormatRules.AddRange(firstLevelRule, lastLevelRule, grandTotalRule, topCategoryRule)
            dashboardViewer1.Dashboard = dashboard
        End Sub
    End Class
End Namespace