The Highlighting Expert

While the absolute formatting options on the Format Editor will solve many reporting needs, you ll soon find that you may wish object formatting to change according to the data being displayed. This is called conditional formatting, which lets you change the appearance of objects depending on their contents or the contents of other fields, objects, or formulas. Although the possibilities of conditional formatting are limited only by your imagination and creativity, some immediate uses of conditional formatting that may come to mind are

• Showing sales figures in red if they fall below a predefined level

• Using a different font to highlight long-time customers

• Adding a border around an invoice number if it s past due

• Showing a report title that s different on the first page than on the rest of the pages

• Graphically indicating with file-folder icons whether a case file has been opened or closed

Probably the simplest conditional formatting tool with Crystal Reports is the Highlighting Expert, which lets you arrange that the appearance of a field will change when a certain condition is met. If a sales figure falls below a preset goal for the department, you can have it stand out with a white font color on a red background. Or, you can change the border on a Days Overdue formula that exceeds, say, 60 days.

To use the Highlighting Expert with a field, select the field you want to change. Start the Highlighting Expert by clicking the Highlighting button on the Expert Tools toolbar, choosing Format Highlighting Expert from the pull-down menus , or right-clicking the object and choosing Highlighting Expert from the pop-up menu. Figure 9-1 shows the Highlighting Expert.

Figure 9-1: The Highlighting Expert

The idea of the Highlighting Expert is to allow conditional formatting of a field without intricate knowledge of the Crystal Reports formula language. By using the drop-down lists in the dialog box, you choose a series of conditions (by clicking the New button multiple times), and choose specific formatting for each condition. Begin by clicking the New button to add a new condition. Then, choose the field you want to use in a comparison test from the first drop-down list in the Value Of section of the dialog box (you may choose any field on the report for this test, not just the field you are highlighting). Then, choose a comparison operator in the second drop-down list. You ll find most of the standard comparison operators you ve used in formulas or in the Select Expert, such as Less Than, Greater Than, Equal To, Not Equal To, and so forth. After making this choice, enter a constant value to compare to in the third drop-down list (you can also click the drop-down arrow and choose a value from the sample data in the list). Finally, choose any combination of font and background styles, colors, and border styles you want the field to display if the comparison is true.

For example, to format the sales figure to show up as white text on a red background if it falls below the preset sales figure of $1,000, choose this field in the first drop-down list, choose a comparison of Less Than, type 1000 , and then choose a Font Color of White and a Background of Red. You will see a sample in the Sample box in the lower right of the Highlighting Expert, as well as to the left of the now-created condition in the Item List box on the left. When you click OK, the field will show white text on a red background for any sales figures less than $1,000.

You may want to set up multiple conditions if you want more than one formatting option displayed. To expand on the previous example, suppose you want to show bonus sales (over $5,000) in blue, in addition to the existing red background for those that fall below $1,000. Just click the New button below the Item List box. You can enter a new condition and another set of formatting options. Both will apply to the field.

You may have two conditions that conflict with each other. For example, you could have a condition that formats field contents over $1,000 in red, and another that applies blue formatting for contents over $5,000. Since both conditions would satisfy the over-1000 condition, will everything over $1,000 (including anything over $5,000) be in red? It depends on the priority you assign the conditions. If the over-1000 condition is higher in the Item List box, everything over $1,000 will be in red. However, if the over-5000 condition is set higher, then it has priority ” everything over $5,000 will be in blue. Then, the second item in the list (the over-1000 item) will be tested , placing anything over $1,000 in red. To change priority, click the condition you want to move and then click the up or down Priority arrow.