Added Conditional Formatting (Text Color and Background Color) in Tables

Why is it Useful?

CMiC has added conditional formatting (text color and background color) in tables. You can now change the text color or background color based on conditions. For example, for values less than zero, you can color the cell red. Conditional text colors can also be applied to Excel/PDF exports.

NOTE: This feature was released in Patch 15-1 under Issue 21.42008.

How Does it Work?

The Conditional Formatting property is located in the Plotting Fields tab of the Table's Visualization Properties, as shown in the screenshot above. Currently, the Conditional Formatting property is turned off.

When the Conditional Formatting property is turned on for the selected column, in this case for the Invoice Amt column, a Format Expression link appears. Clicking on the link opens the Format Expression pop-up window, where format expressions are entered. The format expression supports comparison and logical operators.

Clicking the Help icon () displays a list of helpful examples for the switch function, which has been enhanced for format expressions only.

After the expression is entered in the Format Expression pop-up window and the window is closed, the conditional formatting is applied to the selected column.

Supported Comparison and Logical Operators

The format expression supports the following comparison operators:

Comparison Operators	Description	Examples
==	Equal to	[Account Type] == “Equity”
!=	Not equal to	[Account Type] != “Equity”
>	Greater than	[Invoice Date] > date(2017,12,17)
<	Less than	[Account Type] < “Equity”
>=	Greater than or equal to	[Invoice Amount] >= 1000
<=	Less than or equal to	[Invoice Amount] <= 1000

The format expression also supports the following logical operators:

Logical Operators	Description	Examples
&&	The double ampersand performs a logical AND operation. It combines two expressions and returns true only if both expressions are true.	([Account Type] == “Equity”) && ([Invoice Amount] <= 1000
||	The double pipe performs a logical OR operation. It combines two expressions and returns true if one of the expressions is true.	([Account Type] == “Equity”) || ([Invoice Amount] <= 1000)

Switch Function

NOTE: The switch function is enhanced (for format expressions only).

The switch function converts an <expression>’s values against a list of values and returns one of multiple results.

Syntax:

switch(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

<expression can be a <field reference>, true, false

<value> can be an expression: a field reference, a number, a string, a URL, a date, a function (including another switch function call), combinations of arithmetic/comparison/logical expressions.

<result> can be a field reference, a number, a string, URL or a date.

<else> can be a field reference, a number, a string, URL or a date.

The data type of the <expression> must be the same as the comparison <value>’s data type.