Menu
Free Trials
Table of contents

Conditional formatting

Conditional formatting allows highlighting cells depending on their values. You can create as many conditions with different formatting for one report as you need, there is no limit towards the number of conditions. If there are more than one conditional formatting rules for the report, they will be applied one by one in the order they have been created.

The conditional formatting 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 selected measure, hierarchy, and hierarchy’s member.

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

More details about conditional formatting are available in the following sections:

Conditional format properties

With conditional formatting, you can define a logical expression for condition rules, style objects for matched and mismatched cells, and application range for conditions. Style objects are composed of font size, font color, font family and background color.

Here is a list of all available properties for conditions:

  • formula – IF statement with 3 arguments: IF(CONDITION, TRUE STYLE, FALSE STYLE), where the false style is optional. Condition can contain AND, OR, ==, !=, >, <, >=, <=, -, +, *, /. #value is used to address the cell value in condition. Example: if(#value > 2, "trueStyle", "falseStyle").
  • trueStyle – style object that is applied to a cell if the condition for the cell value is met.
  • falseStyle (optional) – style object. If it is set it is applied to a cell if the condition for the cell value is not met.
  • row (optional) – row index to which the condition is applied.
  • column (optional) – column index to which the condition is applied.
  • measure (optional) – measure unique name to which the condition is applied.
  • hierarchy (optional) – hierarchy unique name to which the condition is applied.
  • member (optional) – hierarchy’s member unique name to which the condition is applied.
  • isTotal (optional) – Number. If it is -1 (not defined) – the condition is applied to all cells. If it is 0 (false) – the condition is applied to regular cells only. If it is 1 (true) – the condition will be applied to totals and subtotals cells only.

Style object format

trueStyle and falseStyle are both style objects, but the latter is optional. Style object can have the following properties:

"trueStyle": {
    "backgroundColor": "#FFFFFF",
    "color": "#000000",
    "fontFamily": "Arial",
    "fontSize": 12
}

Please note that you can specify only necessary properties.

Conditional formatting for all values

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

{
    "dataSource": {
        "filename": "http://www.flexmonster.com/download/data.csv"
    },
    "conditions": [
        {
            "formula": "if(#value < 400000, 'trueStyle')",
            "trueStyle": {
                "backgroundColor": "#FFFFFF",
                "color": "#000000",
                "fontFamily": "Arial",
                "fontSize": 12
            }
        }
    ]
}

Conditional formatting for specific values

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

{
    "dataSource": {
        "filename": "http://www.flexmonster.com/download/data.csv"
    },
    "conditions": [
        {
            "formula": "if(#value < 400000, 'trueStyle')",
            "measure": "Price",
            "isTotal": false,
            "trueStyle": {
                "backgroundColor": "#FFFFFF",
                "color": "#000000",
                "fontFamily": "Arial",
                "fontSize": 12
            }
        }
    ]
}

Change conditional formatting using Toolbar

Please use Format > Conditional formatting in Toolbar to change/define conditional formatting for values in run time.

conditionalformatting

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

API call addCondition() is used in Toolbar to add or change the conditional formatting rule in run time. You can change conditions among with other report parts using API call setReport().