You can use conditional formatting to apply specified formatting to cells in a grid. For example, suppose you define the criterion “current cell value = 0” and then define a format to change the color of the cell font to blue if the criterion is met. If the data returned for this cell has a value of 0, then the font color is changed to blue.
Conditional formatting takes precedence over all other formatting and replaces formatting previously set at the grid, row or column, or cell level. For example, if you use conditional formatting to change cell colors, yet want to retain a specific number of decimal places applied with regular formatting, you must specify both formats using conditional formatting. |
When you select multiple cells, the default formatting is derived from the upper left cell of the selected group of cells. |
After you define the conditions and add the formats, you return to the report.
The current grid is displayed. Each cell with conditional formatting contains a colored dash, shown in cell D2 below, as an indication that a conditional format is applied to this cell. This is illustrated in below.
You can apply conditional formatting to enhance your reports. For example, you can underline any values greater than 1000. You can also set up more complex conditional formatting. For example, if the account type equals expense and the current cell value is greater than 1000, apply bold to the cell. In addition, if the value of a Cola cell is less than 1000, then set the background color of the cell to lime green. You can accomplish this type of formatting by specifying additional conditions and formats, as in the following example:
You can specify conditionally formatting for related content on any grid object cell. For example, if the Market member name is “East” link to “ReportA”, if the Market member is “West”, link to “ReportB”. On the Format Cells dialog, you can specify the related content links. See Replacing Text.
From the Conditional Format dialog box, select Account Type from the Condition 1: If drop-down list box.
Then, select Expense from the Comparison Options drop-down list box. Click Format Cells to apply bold formatting to the selected cell, leaving all other font properties unchanged.
Next, add a second condition to the selected cell. Click the Add Condition 2 button, setting the Condition 2: If statement to Current Cell Value. Set the operator value to > (greater than), leaving the comparison value set to Number. Set the numeric value to 1000, as illustrated below:
Next, add another Format by clicking the Add Format 2 button. Establish three conditions as follows to set the background color to lime green when a Cola cell has a value < 1000:
After you establish the conditions, you can select the check box for Allow Parentheses in the Options box.
The following scenario performs the following operation:
For this example, if a cell has the label Cola associated with it using the Alias:Default and the cell value < 1000, the background is set to lime green. If a Member Name contains Cola and the cell value is < 1000, the background is set to lime green.
The manner in which you use conditional formatting can impact performance depending on the size of the report. Performance is also contingent on the criteria used and the frequency of use (every cell). Each or all of these factors combined can affect performance. Data value comparisons are the fastest. Criteria such as data value, Member Name, and Member Alias/Description are faster because they are part of the metadata or data query. Avoid criteria such as Generation, Level, Account Type, and Attribute Value whenever possible, as performance is slower because those criteria are not part of the regular metadata or data query. |