Conditional FormattingUsing Data to Drive the Look of a Report

Up to now, the focus has been on static formatting. The next step is to apply formatting based on the data that is being returned from a field or even applying formatting on one field based on the value of another.

Conditional formatting relies on formulas. Because the formula language is extensive, complex statements can be created. This chapter will introduce fairly simple examples to illustrate.

The simplest way to add conditional formatting is to use the Highlighting Expert. This feature enables us to apply font face and font color changes to database fields based on their values.

Tip

Almost every formatting option can be conditional. To determine which ones are conditional, look at the x+2 button next to the option in the Formatting Editor dialogs.

If a formatting option has already been set to a conditional format, the button appears with red text. Otherwise, it appears as blue text.

Applying Formatting from Another Field

1.	In Design view, right-click the Contact Last Name field, choose Format Field, and then navigate to the Font tab and click the formula button to the right of the Color drop-down. This opens the formula editor, driving your font color choice.
2.	Enter the following formula: SELECT {Customer.Last Year's Sales} CASE 0 TO 1000: crRed CASE 1001 TO 100000: crYellow CASE IS > 100001: crGreen; Note Here you specified a color as the result of the case expression because the formula controlled color. In cases where the formatting option is Boolean (that is, either you turn the feature on or off) there is an assumed IF statement, and all you have to do is enter the condition (for example, {table.field} > 100). Entering a full IF statement in cases like this causes an error.
3.	Refresh the report by pressing F5 while you're in the Preview tab. Now you see red, yellow, and green last names depending on the amount of last year's sales for that person (see Figure 9.9).
4.	Save the report as chap9_4.rpt.