1. API reference
  2. Welcome
    1. Component overview
    2. Quick start
    3. System requirements
    4. Troubleshooting
    5. 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. Connecting to CSV using Compressor
      3. Data types in CSV
    3. Database
      1. Connecting to SQL databases
      2. Connecting to other databases
      3. Connecting to database with Node.js
      4. Connecting to database with .NET
      5. Connecting to database with .NET Core
      6. Connecting to database with Java
      7. 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. Referring Accelerator as a DLL
      5. Accelerator security
      6. Configuring authentication process
      7. Configuring secure HTTPS connection
      8. 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 AngularJS (v1.x)
    3. Integration with Angular
    4. Integration with React
    5. Integration with Webpack
    6. Integration with ASP.NET
    7. Integration with jQuery
    8. Integration with JSP
    9. Integration with TypeScript
    10. Integration with RequireJS
    11. Integration with PhoneGap
  6. Integration with charts
    1. Integration with Highcharts
    2. Integration with Google Charts
    3. Integration with FusionCharts
    4. Integration with any charting library
  7. Customizing
    1. Customizing toolbar
    2. Customizing appearance
    3. Customizing context menu
    4. Localizing component
  8. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.5 to 2.6
    4. Migration guide from 2.4 to 2.5
    5. Migration guide from 2.3 to 2.4
    6. Migration guide from 2.2 to 2.3
    7. Documentation for older versions
Table of contents

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.

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

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.

Here is a list of all available properties for conditions:

  • formula – a condition that can contain the following logical operators: AND, OR, ==, !=, >, <, >=, <=, +, -, *, /, isNaN(), !isNaN(). #value is used as a reference to the cell value in the condition. Example: '#value > 2'.
  • format – the style object that will be applied to a cell if the condition for the cell value is met. Note: when exporting to Excel and PDF, colors should be set to hex color codes.
  • formatCSS (optional, read-only) – String. Represents a ready to use CSS string of the format style object. The format style object has properties with names that differ from CSS. The component transforms format to formatCSS.
  • id (optional) – the id of the conditional formatting rule. If the id property is not set, the id for the rule will be set inside the pivot component.
  • row (optional) – the row index to which the condition should be applied.
  • column (optional) – the column index to which the condition should be applied.
  • measure (optional) – the unique measure name to which the condition should be applied.
  • hierarchy (optional) – the unique hierarchy name to which the condition should be applied.
  • member (optional) – the unique member 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 set to true, the condition will be applied to total and subtotal cells only. If it is set to 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 the necessary properties.

If you want to export the pivot table to Excel and PDF, colors should be set to hex color codes.

Conditional formatting for 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:

{
    "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

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:

{
    "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 the Toolbar

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

conditionalformatting

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

Сonditional formatting via API

The API call addCondition() is used to add or change a conditional formatting rule at runtime. You can change conditions along with other report parts using the API call setReport().