Applying Conditional Formatting to Tables, Pivot Tables, Performance Tiles, Graphs, and Trellises

How is Conditional Formatting Applied?

You apply conditional formatting by selecting one or more columns or hierarchy levels in the analysis to use, specifying the condition to meet, and then making specifications for font, cell, border, and style sheet options to apply when the condition is met. The conditional formats can include colors, fonts, images, and so on, for the data and for the cell that contains the data. Your specifications apply only to the contents of the columns or hierarchy levels in the tables and pivot tables for the analysis with which you are working.

You can add multiple conditions so that the data is displayed in one of several formats, based upon the value of the data. For example, below-quota sales can be displayed in one color, and above-quota sales can be displayed in another color. When you specify multiple conditions, all the conditions are verified and the formats are merged for the conditions that are true. In the event of a conflict when trying to merge multiple formats, the condition that is last verified as true affects the format that is displayed.

Can Conditional Formats Be Based on Another Column?

You can create conditional formats to apply to one column based on the values of a second column, for display in tables, pivot tables, and trellises. For example, you can create a conditional format to color the C50 Region column green when values of the 1 - Revenue column are greater than $6.5 million, as shown in Figure 7-1.

Figure 7-1 Conditional Formatting on the Region Column

You can create a condition using a column that is not displayed in views for the analysis, if you use the Hide option on the Column Format tab of the Column Properties dialog. If you place the column in the Excluded drop target of the Layout pane, then you cannot create a condition using that column.

Conditional formatting is applied based on the underlying value, even if you select the Show Data As options in the Layout pane to show the data as percentages or indexes.

What Factors Affect Conditional Formats?

The way that conditional formats are applied to columns depends on the factors that are described in the following list:

• "Layout, Order, and Conditional Formats"

• "Value Suppression and Conditional Formats"

Layout, Order, and Conditional Formats

The layout of the columns in the view affects the conditional formatting of the values of one column when the conditional format is based on another column. Changing the layout of the columns in the view can change the display of the conditional formats. For example, suppose that you specify a conditional format on the C50 Region column where T05 Per Name Year is 2009. If C50 Region and T05 Per Name Year are on opposite edges of the pivot table, then no conditional formatting is visible.

The order of the columns as they are displayed in the view also affects conditional formatting. The order in the view affects the "grain" at which the values are displayed. You can think of "grain" as a level of aggregation. The conditional format applies when the column to format is displayed at a finer grain or the same grain as the column on which the format is based. If the column being formatted is of a higher grain than the column on which the format is based, then the format applies only if the condition is based on a measure column. If the format is based on an attribute column and the column to format is displayed at a higher grain, then the conditional format does not apply.

For example, consider the table in Figure 7-2. A conditional format has been specified to color the C50 Region when T05 Per Name Year is 2009. Notice that no magenta coloring is visible because C50 Region is on a different edge than T05 Per Name Year. See "Value Suppression and Conditional Formats" for additional information.

Figure 7-2 Columns on Opposite Edges with No Conditional Formatting Applied

Suppose that you change the order of the columns so that T05 Per Name Year is the first column in the table. Then the T05 Per Name Year column is displayed at a higher grain, and the appropriate conditional formatting is applied to the C50 Region column. Figure 7-3 shows a pivot table with the appropriate conditional formatting.

Figure 7-3 Conditional Formatting Based on Column Order

Conditional formats can be displayed on any edge of the table, pivot table, or trellis. On the Prompts drop target (also known as the "page edge"), the conditional format applies only to the column member that is currently selected for that target. For example, suppose that you have a C50 Region column that has the members AMERICAS, APAC, and EMEA. Suppose the C50 Region column is on the Prompts drop target for a pivot table and the conditional format is set to color the region name green if 1 - Revenue is greater than $6.5 million. If AMERICAS is the only region that meets that condition, then AMERICAS is colored green only when it is selected for the Prompts drop target.

Value Suppression and Conditional Formats

For tables, the value suppression setting in the "Column Properties dialog: Column Format tab" affects conditional formatting. If you set value suppression to Repeat, then the column that you are formatting is displayed at the detail grain of the view. No aggregation is needed on the column on which the format is based for applying the conditional format.

For example, consider the table in Figure 7-4. A conditional format has been applied to color the C50 Region column magenta when T05 Per Name Year is 2009. Notice that no magenta coloring is visible, because the value suppression is set to Suppress, which does not allow for repeating column values for the members of C50 Region.

Figure 7-4 Conditional Formatting When Suppression is Enabled

If the value suppression is set to Repeat, then column members are repeated and the appropriate conditional formatting is applied. Figure 7-5 shows a table with repeat value suppression.

Figure 7-5 Conditional Formatting for Repeat Value Suppression

Applying Conditional Formatting

To apply conditional formatting:

1. Open the analysis in which you want to edit formatting and behavior in the "Analysis editor: Criteria tab".

   
   

   Note: You can also apply conditional formatting to a performance tile from the "Results tab: Performance Tile editor" by clicking the Edit Conditional Formatting link in the "Performance Tile Properties dialog". This opens the "Conditional Formatting dialog". You would then add the conditional formatting to your tile by completing steps 4 through 6 below. Note that the conditional format is not reflected in the Styles pane.

   
   

2. Add or locate the column or hierarchy level to modify. Click the Options button to the right of the column name in the Selected Columns pane, and click Column Properties or Hierarchy Level Properties. The "Column Properties dialog" is displayed.

3. Click the "Column Properties dialog: Conditional Format tab".

4. Click Add Condition, then select the column to which to apply the condition.

5. Complete the "New Condition dialog". You can click the Edit Condition button to the right of the condition name to display the "Edit Condition dialog".

   
   

   Note: When you access the "New Condition dialog" from the Conditional Format tab, the dialog shows only the options that apply to conditional formats. For example, the Operator list shows the subset of operators that are used in conditional formats, and you can apply only presentation variables.

   
   

6. Complete the "Edit Format dialog" to apply formatting for the condition.

Example of Conditional Formatting for Ranking

The following example describes how conditional formatting can be applied to results to show ranking.

Suppose an analysis includes ten ranking categories, with a value of 1 in the column indicating the worst ranking, and a value of 10 indicating the best ranking. You could create three conditional formats to show the following:

• One image to indicate a low rank for columns that contain 1, 2, or 3.

• Another image to indicate an average rank for columns that contain 4, 5, 6, or 7.

• A third image to indicate a high rank for columns that contain 8, 9, or 10.

In the Graphics dialog, selecting the Images Only image placement option would cause the columns to be displayed with only images, and not the ranking numbers, in the results.