Conditional formatting

Conditional formatting is used to format a cell or a range of cells based on specified criteria. In one report, you can create as many conditions as you need and each condition can apply different formatting rules. Multiple conditional formatting rules for the report will be applied one by one in the order that they were created.

Conditional formatting rules may be added to all pivot table cells, to the cell specifying row and column indexes, to totals and subtotals only, to regular cells only, or to the cells of the selected measure, hierarchy, and hierarchy's member.

Conditions can be defined within a report. When you save the report all the conditional formatting will also be saved and loaded when the report is retrieved.

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

Conditional format properties

With conditional formatting you can define the following: a logical expression for the rules of the condition (the formula property); style objects for cells that pass the condition (the format property); and the cells to which the condition is applied. Style objects are composed of font size, font color, font family, and background color.

See the full list of available ConditionalFormatObject properties.

Formatting all values

You need to specify the formula and format properties to apply the conditional rule to all values. You can define a format the following way:

report: {
  conditions: [
    {
      formula: "#value < 400000",
      format: {
        backgroundColor: "#FFFFFF",
        color: "#0000FF", // Blue
        fontFamily: "Arial",
        fontSize: "12px"
      }
    }
  ],
  // Other properties
}

Live example

Formatting specific values

A formatting rule can be applied to a specific measure, hierarchy, hierarchy member, column, or row. Additionally, you can apply it only to regular cells or to totals and subtotals. For example, if you are visualizing financial data, you may want to apply conditional formatting only to regular cells with prices. See an example below:

report: {
  conditions: [
    {
      formula: "#value < 10000",
      measure: "Quantity",
      aggregation: "sum",
      format: {
        backgroundColor: "#7CB342", // Green
        color: "#FFFFFF"
      }
    }
  ],
  // Other properties
}

Live example

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",
      formula: "min('Quantity') >= 100",
      format: {
        fontSize: "14px",
        backgroundColor: "#FF79CD", // Pink
        color: "#FFFFFF"
      }
    }
  ],
  // Other properties
}

Live example

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

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

Live example

Go to Format > Conditional formatting in the Toolbar to change/define conditional formatting rules for values at runtime.

This conditional formatting will be applied to the specified values and will be saved within the report.

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.
New values for the default styles can be defined in the toolbar.defaults.defaultConditionalFormat object inside the beforetoolbarcreated handler:

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

Live example

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

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

Live example