Skip to main content
All docs
V25.1
  • Conditional Formatting

    • 5 minutes to read

    A Pivot dashboard item applies conditional formatting to cell values. You can calculate a format rule by measures placed in the Values section and dimensions placed in the Columns or Rows sections.

    You can use hidden measures to specify a condition used to apply formatting to visible values.

    pivot-with-applied-format-rules

    Supported Format Rules

    Format rules that can be applied to different data item types are as follows:

    Data Type

    Supported Format Conditions

    numeric

    Value

    Top-Bottom

    Average

    Expression

    Icon Ranges

    Color Ranges

    Gradient Ranges

    Bar

    Bar Color Ranges

    Bar Gradient Ranges

    string

    Value with the condition type set to Equal To, Not Equal To or Text that Contains

    Expression

    date-time

    Value

    A Date Occurring for dimensions with the continuous date-time group interval

    Expression

    Icon Ranges

    Color Ranges

    Gradient Ranges

    Bar

    Bar Color Ranges

    Bar Gradient Ranges

    Refer to the Conditional Formatting Basics topic for more information about format rule’s types.

    Create and Edit a Format Rule

    You can create and edit format rules in the following ways:

    • Click the Edit Rules button on the Home ribbon tab.

    • Click the measure/dimension menu button in the Data Item’s pane and select Add Format Rule/Edit Rules.

    Refer to the following topic for information on how to create and edit format rules: Conditional Formatting in Windows Designer.

    Pivot-Specific Format Condition Settings

    You can configure and customize the format condition appearance settings.

    • Choose a predefined background color/font or click an empty square to add a new preset in the Appearance tab.

      NewRuleDialog_AppearanceTab

      Note

      The appearance of field values in Raster Skins differs from the background color set in the conditional format rule because field value cells in these skins are raster images.

    • Add a predefined icon in the Icons tab.

    The Appearance tab contains the following Pivot-specific settings:

    Option Description
    Enabled Enables/ Disables the current format rule.
    Intersection Mode Specifies the level on which to apply conditional formatting to pivot cells.
    Intersection Row/Column Dimension Applies the format rule to the specified row/column dimension, if you select the Specific Level as the intersection mode.
    Apply to Row/Column Specifies whether to apply the formatting to the entire Pivot’s row/column.

    A Pivot item allows you to specify to which field intersection a format rule is applied.

    Intersection Level Mode Description
    Auto Identifies the default level. For the Pivot dashboard item, Auto identifies the First Level.
    First Level The first level values are used to apply conditional formatting.
    Last Level The last level values are used to apply conditional formatting.
    All Levels All pivot data cells are used to apply conditional formatting.
    Specific Level The specified measures/dimensions are used to apply conditional formatting.

    The image below displays different intersection levels with the applied conditional format rule:

    winforms_pivot_intersection_level_mode

    To apply a format rule to the row or column Grand Total, change the Intersection Level Mode to Specific level and set the [Grand Total] value as the intersection row/column dimension.

    Note the following limitations:

    1. The dashboard cannot calculate conditional formatting in a Pivot for multiple levels of ranges with percentage values. In this case, the “All Levels” intersection mode is not available for a conditional formatting rule.
    2. The format condition dialog does not contain any Pivot-specific settings for a dimension from Columns and Rows.

    Create a Format Rule in Code

    Create a PivotItemFormatRule object and specify its settings to add a format rule:

    Enable the CellsItemFormatRule.ApplyToRow/PivotItemFormatRule.ApplyToColumn properties to apply formatting to the entire pivot row/column.

    You can use the DashboardItemFormatRule.Enabled property to disable the current format rule.

    The following code snippet shows how to apply the Top-Bottom (FormatConditionTopBottom) format condition to highlight the three top values:

    PivotItemFormatRule lastLevelRule = new PivotItemFormatRule(pivot.Values[0]);
    FormatConditionRangeGradient rangeCondition =
        new FormatConditionRangeGradient(FormatConditionRangeGradientPredefinedType.WhiteGreen);
    lastLevelRule.Condition = rangeCondition;
    lastLevelRule.IntersectionLevelMode = FormatConditionIntersectionLevelMode.LastLevel;
    
    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(lastLevelRule, topCategoryRule);
    

    Tip

    Refer to the Conditional Formatting section for more examples.