Formatting can also be done using conditions. Like in almost any current programming language, in CPM conditions can be defined by using a modification of the IF … THEN … ELSE syntax. The expression can be read like this: IF the first value is true, THEN the option specified here will be executed ELSE the option specified here will be executed. A simple every-day life example would be: IF it is raining THEN I will stay inside ELSE I will go for a walk. How conditions can be used for formatting will be explained in the following sections.
IF (test; thenvalue; otherwisevalue)
In CPM conditions are defined using a shortened form of the IF … THEN … ELSE expression as given above. Using this function, you can for example specify that if a value exceeds a certain test value then it should be shown in red and otherwise in green. This gives you a powerful option for making your analyses intuitively understandable and for immediately being able to identify critical values. You can even define formatting in finer granularity by adding another conditional expression to either the “thenvalue” or “otherwisevalue” parameter. More information about this option can be found in Nested Functions. An example for simple conditional formatting can be seen in Figure 3.22.
Figure 3.22: Conditional Formatting
Conditional formatting will almost always be used in OLAP Tables or for formatting the colors of process edges and the values shown on top of them.
In the “test” parameter you will define the condition your formatting depends on. Here, you will usually use the $value variable and compare it to another value by using one of the following logical expressions: = (equals), > (greater than), < (smaller than), >= (greater or equal to) or <= (smaller or equal to). Please note that string values given on the right side of the logical operator must be given in single quotation marks.
The “then_value” parameter specifies the option to choose if the condition in the “test” parameter is tested as true. Here you will normally give a color value by using the color picker or inserting standard colors like “black”, “red”, “blue”, “yellow” and “green” via text input. However, you can also define another condition by again using the IF function (as explained in Nested Functions). All color values, no matter if given as text or as hex code must be given in single quotation marks.
In the “otherwisevalue” parameter you will give the option to choose if the condition in the “test” parameter is tested as false. Here the same options as for the “thenvalue” parameter apply. You can either insert a color value or specify another IF function as explained in Nested Functions). All color values, no matter if given as text or as hex code must be given in single quotation marks.
Example: Assume we chose the purchase order value for each order in a OLAP Table column and want to display it either in red or green depending on it being higher or lower than 100.000. For this purpose, we will insert the following IF function into the “Font Color” field given in the configuration options for the OLAP Table: =IF($value > 5000; ‘red’; ‘green’)