QPR ProcessAnalyzer Table

Table conditional formatting can be used to define table cell background colors, text colors, colored icons and databar, where all depend on the data in the table.

Overview

Conditional formatting is defined in the measure/dimension/column settings as a JSON object with following allowed properties:

• backgroundColor
  • rules: Rules are checked in the defined order and the first matching determines the color.
    • color
    • value: Value to compare against.
    • comparison: One of the following: <, >, <=, >=, ==, !=
  • scale
    • color
    • value: Value that corresponds with the color.
  • color: Fixed color that is used when no rules/scale is defined or there are no matching rules.
• textColor
• databar
  • min: Value that represents the database minimum position (databar not visible).
  • max: Value that represents the database maximum position (databar fills the whole cell).
  • color: Color of the databar. Similar syntax as for the backgroundColor can be used.
• icon
  • rules: Similar syntax as for the color rules, except instead of "color" attribute there is "icon" attribute to define the icon (all Google Material Icons are available: https://material.io/resources/icons/)
  • color: Color of the icon. Similar syntax as for the backgroundColor can be used.

Rule values and scale minimums and maximums (value, min, max) can be defined:

• fixed value, e.g. 5
• Aggregate a value from the same column, e.g. { "aggregate": "max" }
• Aggregate a value from other column, e.g. { "type":"measure", "index": 1, "aggregate": "max" }
• Refer to a cell value in other column in the same row, e.g. {"type":"measure", "index": 1 }

Available aggregations are: min, max, sum, average, median, first, last.

Examples

Background color is based on the following rules:

• if value is lower or equal to 5, background color is #FF0000
• if value is between, 5 and 10, background color is #FFFF00
• if value is greated than 10, background color is #00FF00

{
	"backgroundColor": {
		"rules": [
			{
				"color": "#FF0000",
				"value": 5,
				"comparison": "<="
			},
			{
				"color": "#FFFF00",
				"value": 10,
				"comparison": "<="
			},
			{
				"color": "#00FF00"
			}
		]
	}
}

Background color is based on a color scale where column minimum value gets color #FFFFFF and maximum value #FFD851. Color is scaled linearly between these colors.

{
	"backgroundColor": {
		"scale": [
			{
				"value": { "aggregate": "min" },
				"color": "#FFFFFF"
			},
			{
				"value": { "aggregate": "max" },
				"color": "#FFD851"
			}
		]
	}
}

Similar to previous example, except minimum and maximum values come from another column (measure number 1).

{
	"backgroundColor": {
		"scale": [
			{
				"value": { "type":"measure", "index": 1, "aggregate": "min" },
				"color": "#FFFFFF"
			},
			{
				"value": { "type":"measure", "index": 1, "aggregate": "max" },
				"color": "#FFD851"
			}
		]
	}
}

The following setting defines a fixed background color for a column.

{
	"backgroundColor": {
		"color": "#FFD851"
	}
}

Background color code is taken from column measure 1 from the same row.

{
	"backgroundColor": {
		"color": { "type":"measure", "index": 1 }
	}
}

Text color is #FF0000 if value is lower than 20, otherwise #00FF00.

{
	"textColor": {
		"rules": [
			{
				"color": "#FF0000",
				"value": 20,
				"comparison": "<"
			},
			{
				"color": "#00FF00"
			}
		]
	}
}

Creates a red-white-green color scale where the minimum value is red, the nominal value (eg. a target value) of 50 is white and maximum value is green.

{
  "backgroundColor": {
    "scale": [
      {
        "value": {
          "aggregate": "min"
        },
        "color": "#FF0000"
      },
      {
        "value": 50,
        "color": "#FFFFFF"
      },
      {
        "value": {
          "aggregate": "max"
        },
        "color": "#00FF00"
      }
    ]
  }
}

Adds an icon left of the value (icon sentiment_satisfied_alt if value is over 100 and otherwise sentiment_very_dissatisfied). Also icons have different colors.

{
	"icon": {
		"rules": [
			{
				"icon": "sentiment_satisfied_alt",
				"value": 100,
				"comparison": ">"
			}
		]
		"icon": "sentiment_very_dissatisfied"
		"color": {
			"rules": [
				{
					"color": "#36d475",
					"value": 100,
					"comparison": ">"
				},
				{
					"color": "#EF5254"
				}
			]
		}
	}
}

Adds a databar between zero and maximum value of the column. Databar color is based to a range between white and yellow.

{
	"databar": {
		"min": 0,
		"max": { "aggregate": "max" },
		"color": {
			"scale": [
				{
					"value": 0,
					"color": "#FFFFFF"
				},
				{
					"value": { "aggregate": "max" },
					"color": "#FFD851"
				}
			]
		}
	}
}