Conditional Formatting

  • 7 minutes to read

The Pivot Grid control includes a Microsoft Excel-inspired conditional formatting feature that allows you to change the appearance of individual cells based on specific conditions. This feature helps to highlight important information, identify trends and exceptions, and compare data using the collection of format rules.

WinForms Pivot Format Rules

You can add, remove or edit format rules in different ways: using the PivotGrid Designer at design time, using API or at runtime. You can easily export the result of conditional formatting to a file in various formats (HTML, MHT, PDF, RTF, TXT, CSV, XLS or XLSX).

TIP

Demo: Format Rules module in the XtraPivotGrid MainDemo

Requires installation of WinForms Subscription. Download.

Create Format Rules

To create a new formatting rule at design time, invoke the PivotGrid Designer and go to the Format Rules page in the Appearances section.

PivotGrid_FormatRulesSection

  1. Use the Group_AddGroup button to add a new rule to the collection. To delete the rule, select it and click the Group_RemoveGroup button.
  2. Set the PivotGridFormatRule.Measure property that defines the data field to whose values the format rule is applied.
  3. Select one of the PivotGridFormatRule.Settings property values that specify which data cell/field intersections are involved in conditional formatting. You can select an intersection of row and column fields or specify the type of cells.
  4. Select the FormatRuleBase.Rule property value that specifies the type of a format rule and defines condition and appearance settings applied to data cells.

    You can select one of the following rules.

    Format rule Condition Class Short Description Fields Intersection Support Data Cells Specifying Support
    Format based on value FormatConditionRuleValue Applies a format if a column's value meets a specified condition (Equal, Less, Between, etc.). Dahsboard_GreenTick Dahsboard_GreenTick
    Format based user defined expression FormatConditionRuleExpression Applies a format if a cell value(s) meets a specific expression. Dahsboard_GreenTick Dahsboard_GreenTick
    Format only values that contain FormatConditionRuleContains Applies a format if a value(s) matches one of the constants. Dahsboard_GreenTick Dahsboard_GreenTick
    Format only values that are above or below average FormatConditionRuleAboveBelowAverage Applies a format if a cell value(s) is above or below the fields intersection data average. Dahsboard_GreenTick
    Format using two-color scales FormatConditionRule2ColorScale Applies a format using a two-color scale to display data distribution and variation using a gradation of two colors. Dahsboard_GreenTick
    Format using three-color scales FormatConditionRule3ColorScale Applies a format using a three-color scale to display data distribution and variation using a gradation of three colors. Dahsboard_GreenTick
    Format only top or bottom rank values FormatConditionRuleTopBottom Applies a format if a value is in the range of the highest or lowest field intersection data values. Dahsboard_GreenTick
    Format using Data bar FormatConditionRuleDataBar Applies a format using a data bar. The bar length changes proportionally to cell value. A longer bar corresponds to a higher value, and a shorter bar corresponds to a lower value. Dahsboard_GreenTick
    Format using icons FormatConditionRuleIconSet Applies a format using an icon set. An icon set format allows you to classify column values into several ranges separated by threshold values, and display a specific icon in a column cell according to the range to which this cell value belongs. Dahsboard_GreenTick
  5. Set the appearance of formatted cells. You can do this in two ways.

    • Use the rule's PredefinedName property that sets the name of the predefined style pattern for the current conditional formatting rule.
    • Use the rule's Appearance property that provides access to appearance settings.

The image below demonstrates a configured rule.

StyleConditionsPage_1

To check whether the format rule is correct, use the PivotGridFormatRule.IsValid property. If this property returns true, the format rule is applied to Pivot Grid data cells.

To disable the format rule, set the FormatRuleBase.Enabled property to false.

Edit Format Rules

There are two general ways of applying formatting rules to the Pivot Grid control at runtime: by using the Format Rules Menu or the Conditional Formatting Manager.

Format Rules context menu

If the PivotGridOptionsMenu.EnableFormatRulesMenu property value is set to true, end-users can invoke the Format Rules context menu and select a required rule and predefined style from the list.

WinForms Pivot Format Rules Popup Menu

IMPORTANT

To enable the multi-column item layout at the third menu level shown in the image above, add the BarManager component to the form. Otherwise, menu elements at the third menu level will be arranged in a linear list; specific menu items will be displayed without glyphs.

Conditional Formatting Rules Manager

End-users can create, sort and modify the created rules using the Conditional Formatting Rules Manager. To allow end-users to use the Conditional Formatting Rules Manager, ensure that the control's PivotGridOptionsMenu.EnableFormatRulesMenu property is set to true.

To invoke this manager at runtime, select the Manage Rules... item from the Format Rules context menu.

WinForms PivotFormat Rules Manager

  • To create a new rule, click the New Rule... button, select a rule type and specify its parameters. Depending on rule type, you can set maximum and minimum values, specify format, colors or icon style, etc.
  • To edit the existing rule, click the Edit Rule... button and change the rule parameters. Besides that, you can see the rule's format preview and specify the row, measure and column in the list of rules in the main window.
  • To delete the rule, select the rule and click the Delete Rule button.
  • To reorder rules, select the rule and click Up or Down button.

Create Format Rules in Code

The Pivot Grid control allows you to apply conditional formatting to data cells or field value cells. By default, an empty collection of style format rules is created with a new instance of the PivotGridControl. The PivotGridControl.FormatRules property provides access to a collection of PivotGridFormatRule objects that are used to define formatting settings.

To add a new format rule, create the PivotGridFormatRule object and specify the following settings.

Finally, add the created format rule to the PivotGridControl.FormatRules collection.

Conditional Formatting Limitations

See the list below for information on conditional formatting limitations.

Example: How To Apply Format Rules

The following sample applies format rules to Pivot Grid Control's data cells. The FormatConditionRuleDataBar class allows you to see a cell value relative to other cells. A longer bar corresponds to a higher value, and a shorter bar corresponds to a lower value. Cells placed at the intersection of 'Year' column and 'Sales Person' row are painted with yellow gradient.

The image below shows the result.

Example Format Rules

using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraEditors;

namespace WinFormsPivotGridFormatRules
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            salesPersonTableAdapter1.Fill(nwindDataSet1.SalesPerson);

            // Creates a new FormatRule object.
            PivotGridFormatRule newRule = new PivotGridFormatRule();

            // Sets a Measure.
            newRule.Measure = fieldExtendedPrice1;

            // Creates and specifies a new Settings object.
            newRule.Settings = new FormatRuleFieldIntersectionSettings{
                Column = fieldOrderYear1, 
                Row = fieldSalesPerson1 
            };

            // Creates a new Rule object and sets its parameters.
            newRule.Rule = new FormatConditionRuleDataBar{ 
                PredefinedName = "Yellow Gradient" 
            };

            // Adds the rule to the collection.
            pivotGridControl1.FormatRules.Add(newRule);
        }
    }
}
See Also