Menu
Download Free Trial
  1. API reference
  2. Welcome
    1. Component overview
    2. Quickstart
    3. System requirements
    4. Troubleshooting
    5. Installation troubleshooting
    6. Managing license keys
  3. Connecting to Data Source
    1. JSON
      1. Connecting to JSON
      2. Data types in JSON
    2. CSV
      1. Connecting to CSV
      2. Data types in CSV
    3. SQL database
      1. Connecting to SQL database
      2. Connecting to database with .NET
      3. Connecting to database with .NET Core
      4. Connecting to database with Java
      5. Connecting to database with PHP
    4. Microsoft Analysis Services
      1. Connecting to Microsoft Analysis Services
      2. Getting started with Accelerator
      3. Installing Accelerator as a Windows Service
      4. Configuring username/password protection
      5. Configuring secure HTTPS connection
      6. Troubleshooting
    5. Pentaho Mondrian
      1. Connecting to Pentaho Mondrian
      2. Getting started with Accelerator
      3. Configuring Mondrian roles
      4. Сonfiguring username/password protection
      5. Сonfiguring secure HTTPS connection
      6. Troubleshooting
    6. icCube
  4. Configuring report
    1. What is a report
    2. Data source
    3. Slice
    4. Options
    5. Number formatting
    6. Conditional formatting
    7. Set report to the component
    8. Get report from the component
    9. Date and time formatting
    10. Configuring global options
    11. Export and print
    12. Calculated values
    13. Custom sorting
  5. Integration with frameworks
    1. Available tutorials
    2. Integration with jQuery
    3. Integration with AngularJS
    4. Integration with Angular 2
    5. Integration with Angular 4
    6. Integration with React
    7. Integration with RequireJS
    8. Integration with TypeScript
    9. Integration with ASP.NET
    10. Integration with JSP
    11. Integration with PhoneGap
  6. Integration with charts
    1. Integration with Highcharts
    2. Integration with FusionCharts
    3. Integration with Google Charts
    4. Integration with any charting library
  7. Customizing
    1. Customizing toolbar
    2. Customizing appearance
    3. Localizing component
  8. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.3 to 2.4
    4. Migration guide from 2.2 to 2.3
  9. Older Versions
    1. Documentation 2.3
    2. Documentation 2.2
    3. API reference - Flex
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 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 – condition that can contain AND, OR, ==, !=, >, <, >=, <=, +, -, *, /, isNaN(), !isNaN(). #value is used to address the cell value in condition. Example: '#value > 2'.
  • format – style object that will be applied to a cell if the condition for the cell value is met. Please note that for export to Excel and PDF colors should be set as hex color codes.
  • formatCSS (optional) – String. Represents ready to use CSS string of format style object. format style object has properties with names that differ from CSS. The component transforms format to formatCSS.
  • id (optional) – id of the conditional formatting rule. If id property is not set, the id for the rule will be set inside Pivot component.
  • row (optional) – row index to which the condition should be applied.
  • column (optional) – column index to which the condition should be applied.
  • measure (optional) – measure unique name to which the condition should be applied.
  • hierarchy (optional) – hierarchy unique name to which the condition should be applied.
  • member (optional) – member unique name to which the condition should be applied.
  • isTotal (optional) – Boolean. If it is not defined, the condition will be applied to all cells. If it is true, the condition will be applied to totals and subtotals cells only. If it is false, the condition will be applied to regular cells only.

Style object format

format is a style object that can have the following properties:

"format": {
    "backgroundColor": "#FFFFFF",
    "color": "#0000FF",
    "fontFamily": "Arial",
    "fontSize": "12px"
}

You can specify only necessary properties.

In case you want to export the pivot table to Excel and PDF don’t forget to set colors as hex color codes.

Conditional formatting for all values

You need to specify properties formula and format 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": "#value < 400000",
            "format": {
                "backgroundColor": "#FFFFFF",
                "color": "#0000FF",
                "fontFamily": "Arial",
                "fontSize": "12px"
            }
        }
    ]
}

Open the example on JSFiddle.

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": "#value < 400000",
            "measure": "Price",
            "isTotal": false,
            "format": {
                "backgroundColor": "#FFFFFF",
                "color": "#0000FF",
                "fontFamily": "Arial",
                "fontSize": "12px"
            }
        }
    ],
    "slice": {
        "rows": [ {"uniqueName": "Category"} ],
        "measures": [
            {"uniqueName": "Price"},
            {"uniqueName": "Quantity"}
        ]
    }
}

Open the example on JSFiddle.

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.

Сonditional formatting via API

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