☝️Small business or a startup? See if you qualify for our special offer.

+

Get Free Trial

Conditional formatting

Conditional formatting is used to format cells on the grid based on specific conditions. In the report, each conditional formatting rule is described by the ConditionalFormatObject.

To see different live examples of how to use conditional formatting, visit our Examples page.

About conditional formatting rules

A conditional formatting rule consists of the following:

formula — a logical expression that forms the condition.
format — styles for cells that satisfy the condition. Available styles: font size, font family, font color, and background color; in addition, font style, font weight, text alignment, and text indentation are supported when configuring conditional formatting in the report or via API.

Optionally, you can specify to which cells the condition should be applied:

A specific measure.
Totals and subtotals only, or regular cells only.
A specific field member.
Cells in a specific row or column.

See the full list of available ConditionalFormatObject properties.

Managing conditional formatting

Add a conditional formatting rule

Via UI

Step 1. Select Format > Conditional formatting on the Toolbar. As a result, the Conditional formatting pop-up window will appear.

Step 2. In the pop-up window, select the + icon to create a new conditional formatting rule.

Step 3. Choose which values you want to format and configure formatting styles. For example:

Example of adding conditional formatting via UI

Step 4. Click APPLY to save your configuration.

In the report

To predefine a conditional formatting rule in the report, specify a ConditionalFormatObject with the necessary configurations inside the report.conditions property. For example:

report: {
  conditions: [
    {
      id: "1", 
      formula: "#value > 50000",
      format: {
        color: "#FFF",
        backgroundColor: "#00897B", // Teal
        fontFamily: "Arial",
        fontSize: "12px"
      }
    }
  ],
  // Other properties
}

Using API

To add conditional formatting at runtime, call the addCondition() method and then redraw the component using refresh(). For example:

let condition = {
  id: "1",
  formula: "#value > 400000",
  format: {
    backgroundColor: "#C5CAE9", // Lavender
  }
};

pivot.addCondition(condition);
pivot.refresh();

To see all conditions defined in the report, use getAllConditions(). You can also access a condition by its id with getCondition().

Edit a conditional formatting rule

Via UI

Step 1. Select Format > Conditional formatting on the Toolbar. As a result, the Conditional formatting pop-up window will appear.

Step 2. In the pop-up window, make the necessary changes to the formatting rule.

Step 3. Click APPLY to save your configuration.

Using API

Step 1. Get an existing conditional formatting rule by its id using the getCondition() method:

const id = "1";
const condition = pivot.getCondition(id);

Step 2. Make the necessary changes to the condition. For example:

condition.format.backgroundColor = "#F57C00"; // Orange

See the full list of ConditionalFormatObject properties.

Step 3. Apply the updated condition using the addCondition() method and then redraw the component using refresh():

pivot.addCondition(condition);
pivot.refresh();

Remove a conditional formatting rule

Via UI

Step 1. Select Format > Conditional formatting on the Toolbar. As a result, the Conditional formatting pop-up window will appear.

Step 2. In the pop-up window, remove the needed formatting rule by clicking the × icon next to it.

Step 3. Click APPLY to save your configuration.

Using API

Remove a conditional formatting rule by its id using the removeCondition() method and then redraw the component by calling refresh():

const id = "1";
pivot.removeCondition(id);
pivot.refresh();

Remove all conditional formatting rules using the removeAllConditions() method and then redraw the component by calling refresh():

pivot.removeAllConditions();
pivot.refresh();

How styles are applied if a cell satisfies several conditions

If a cell satisfies several conditional formatting rules, styles from the rule defined last will have the highest priority in case of a style conflict.

At the same time, styles from different conditional formatting rules can be combined. For example, there are two conditions: the first condition applies background color and the second one applies text color. Both conditions affect cells in the range from 8,000 to 15,000:

conditions: [
  {
    id: "1",
    formula: "8000 < #value",
    format: {
      backgroundColor: "#FFB74D", // Orange
    }
  },
  {
    id: "2",
    formula: "#value < 15000",
    format: {
      color: "#8E24AA", // Violet
      fontWeight: "bold"
    }
  }
]

As a result, cells that satisfy both conditions will have an orange background and violet text.

Formatting all measures

Via UI

To format all measures, choose the All values item in the drop-down menu with measures:

Example of formatting all measures via UI

In code

To format all measures, specify only the formula and format properties of the ConditionalFormatObject. For example:

{
  id: "1",
  formula: "#value < 400000",
  format: {
    backgroundColor: "#0000FF", // Blue
    color: "#FFFFFF",
    fontFamily: "Arial",
    fontSize: "12px"
  }
}

Formatting a specific measure

Via UI

To format a specific measure, choose it in the drop-down menu with measures. For example:

Example of formatting a specific measure via UI

In code

To format a specific measure, specify the measure and aggregation properties of the ConditionalFormatObject. For example:

{
  id: "1",
  formula: "#value < 400000",
  measure: "Quantity",
  aggregation: "sum",
  format: {
    backgroundColor: "#FB8C00", // Orange
    color: "#FFFFFF"
  }
}

If you want to format a calculated measure, skip the aggregation property in the conditional formatting rule. For example:

{
  id: "1",
  formula: "#value < 400000",
  measure: "Revenue",
  format: {
    backgroundColor: "#FB8C00", // Orange
    color: "#FFFFFF"
  }
}

Formatting specific cells

Conditional formatting can be applied to a specific field member, column, or row. Additionally, it is possible to format only regular cells or only totals and subtotals.

Note Formatting of specific values is not available via the Conditional formatting pop-up window.

Total or regular cells

By default, conditional formatting is applied to cells of all types.

To apply conditional formatting only to total cells (both subtotals and grand totals), set the isTotal property of the ConditionalFormatObject to true. For example:

{
  id: "1",
  formula: "AND(#value > 3000, #value < 1000000)",
  isTotal: "true",
  format: {
    backgroundColor: "#B3E5FC", // Light blue
  }
}

If you want to format only regular cells, set isTotal to false:

{
  id: "1",
  formula: "AND(#value > 3000, #value < 1000000)",
  isTotal: "false",
  format: {
    backgroundColor: "#B3E5FC", // Light blue
  }
}

To restore the default behavior and apply the formatting to all cell types, delete the isTotal property.

Field member

To apply conditional formatting to a certain field member, specify the hierarchy and member properties of the ConditionalFormatObject. For example:

{
  id: "1",
  formula: "AND(#value > 3000, #value < 1700000)",
  hierarchy: "Category",
  member: "Cars",
  format: {
    backgroundColor: "#EF5350", // Red
    color: "#FFFFFF"
  }
}

Cells in a certain row or column

To apply conditional formatting to a specific row or column, specify the row or the column property of the ConditionalFormatObject. For example:

{
  id: "1",
  formula: "AND(#value > 3000, #value < 2000000)",
  row: 4,
  column: 2,
  format: {
    backgroundColor: "#FBC02D", // Dark yellow
  }
}

The row and column properties can be used separately or together.

Conditions based on another field’s value

To apply the conditional formatting based on another field’s value, specify the field’s aggregation and uniqueName in the formula property:

report: {
  conditions: [
    {
      measure: "Price",
      // Field’s unique name must be enclosed in quotation marks
      formula: "min('Quantity') >= 100",
      format: {
        fontSize: "14px",
        backgroundColor: "#FF79CD", // Pink
        color: "#FFFFFF"
      }
    }
  ],
  // Other properties
}

To apply the conditional formatting based on a calculated value, specify its uniqueName in the formula property:

report: { 
  conditions: [
    {
      measure: "Price",
      formula: "'Revenue' > 50000000",
      format: {
        fontSize: "14px",
        fontWeight: "bold",
        backgroundColor: "#FF79CD", // Pink
        color: "#FFFFFF"
      }
  ],
  // Other properties
}

Customizing the Conditional formatting pop-up window

In the Conditional formatting pop-up window, you can override default formatting styles and limit configs available in the dropdown menus using the beforetoolbarcreated handler.

Overriding default formatting styles

Starting from version 2.9.75, it is possible to customize styles that are selected by default when adding a conditional formatting rule via the Conditional formatting pop-up window. This includes font family, font size, text color, and background color.

To define new values for the default styles, use the toolbar.defaults.defaultConditionalFormat object inside the beforetoolbarcreated handler:

const pivot = new Flexmonster({
  container: "pivotContainer",
  toolbar: true,
  // ...
  beforetoolbarcreated: function(toolbar) {
    toolbar.defaults.defaultConditionalFormat = {
      fontFamily: "Verdana",
      fontSize: "14px",
      color: "#E0F2F1",
      backgroundColor: "#00897B"
    };
  }
});

To limit the list of available values in the dropdown menus, specify only the needed values in the following arrays inside the beforetoolbarcreated handler:

toolbar.defaults.fontSizes
toolbar.defaults.fonts
toolbar.defaults.conditions

For example:

beforetoolbarcreated: function(toolbar) {
  toolbar.defaults.fonts = [
    "Verdana", "Courier New", "Palatino Linotype"
  ];
  // Other customizations
}

See also