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.
A conditional formatting rule consists of the following:
Optionally, you can specify to which cells the condition should be applied:
See the full list of available ConditionalFormatObject properties.
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:
Step 4. Click APPLY to save your configuration.
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
}
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().
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.
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();
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.
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();
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.
Note When working with conditional formatting via the Conditional formatting pop-up window, styles from the condition created first will be applied to a cell that satisfies several conditions.
To format all measures, choose the All values item in the drop-down menu with measures:
To format a specific measure, choose it in the drop-down menu with measures. For example:
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"
}
}
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.
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.
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"
}
}
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.
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
}
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.
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: "pivot-container",
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.fontSizestoolbar.defaults.fontstoolbar.defaults.conditionsFor example:
beforetoolbarcreated: function(toolbar) {
toolbar.defaults.fonts = [
"Verdana", "Courier New", "Palatino Linotype"
];
// Other customizations
}