Conditional Formatting Formulas

The Highlighting Expert is a simple and quick way to format fields, because you don t have to know the formula language to use it. However, the trade-off is in flexibility. As your reports become more sophisticated, sometimes the Highlighting Expert won t provide all the flexibility you need. For example, you may need to apply formatting other than just color and borders. Or, you may need to perform a more complex test than can be done with the comparisons that are in the expert. For these situations, you need to use conditional formatting formulas. Conditional formatting formulas use the Formula Editor to create one or more conditions to determine how the object appears.

Absolute Versus Conditional Formatting

Before you learn how to set formatting conditionally, it s important to have a fundamental grasp of absolute formatting, which simply refers to applying normal formatting to objects with the Format Editor. This type of formatting, described earlier in the chapter, makes use of the Formatting toolbar or the Format Editor to apply the same formatting to all occurrences of the field. If you right-click an object and choose Format Field from the pop-up menu, the Format Editor will appear. You can then click the Font tab to change the font face, style, size , or color. If you change the color of the font to Red, all occurrences of the object on the report will be red. If you click the Border tab and select the Drop Shadow check box, all occurrences of the object will have a drop shadow. This is the process of absolute formatting.

The first rule to follow when it comes to conditional formatting is remembering that you must use the Format Editor. While you can perform absolute formatting with either the Formatting toolbar or the Format Editor, you can set up conditional formatting only with the Format Editor ”the Formatting toolbar won t work.

As you approach conditional formatting, it s important to distinguish between two types of Format Editor formatting properties: multiple-choice properties and on-off properties. On the Font tab, Font and Color are good examples of multiple-choice properties. You can click a drop-down list and choose from any one of several fonts or colors. An example of an on-off property is Drop Shadow on the Border tab, which just has a check box: it can only be turned on or off. Whether a formatting property is multiple choice or on-off determines the type of formula you ll use to set it conditionally. Multiple-choice properties are conditionally formatted with If-Then-Else or Select Case formulas, while on-off properties are conditionally formatted with Boolean formulas.

Tip  

You need to be familiar with the Crystal Reports formula language to use conditional formatting effectively. To refresh your memory, look for information on If-Then-Else and Boolean formulas, as well as Select Case in Chapter 5.

To set formatting conditionally, click the Conditional Formula button that appears on the Format Editor next to the property that you want to format.

This will display the Format Formula Editor inside the Formula Workshop ( essentially the same Formula Editor discussed in Chapter 5, but with a new title), shown in Figure 9-2. Notice that you can set conditional formatting with either Crystal or Basic syntax by making your choice from the Syntax drop-down list. If you are formatting a multiple-choice property, all the available options for the property appear at the top of the Function Tree box. If, for example, you are conditionally formatting the Color property, you ll see all the available colors listed. If you re formatting a border, you ll see the different available line styles.

Figure 9-2: The Format Formula Editor

Use an If-Then-Else or Select Case formula to determine the formatting of the object. Your formula can be as simple or as complex as you need. For example, you may have a formula to set font color that is as simple as the following:

 If {Customer.Last Year's Sales} > 5000 Then Blue Else Black 

or a formula to set a bottom border as complex as this:

 If {Orders.Order Amount} > 5000 And {Orders.Ship Via} = "Fedex" Then 
 DoubleLine 
 Else 
 If {Orders.Order Amount} > 1000 And {@Ship Days} < 3 Then 
 SingleLine 
 Else 
 NoLine 

Tip  

In most cases, you may type formatting values, such as color or line type, into the formula directly (as in the previous examples). You may also double-click on values shown in the Function Tree box of the Formula Editor. If you double-click, the prefix cr will appear in front of the formatting value in the formula. With few exceptions, either the value name by itself or the value name preceded by cr is acceptable.

You can use any type of simple or compound If-Then-Else formula, or a Select Case formula, as long as the results of every Then, Else, or Case are one of the available formatting properties in the Function Tree box.

When you have finished with the formula, you can use the Check button to check for correct syntax of the formula, or save the formula and close the Format Formula Editor with the Save and Close button. The Format Editor will remain on the screen. Notice that the Conditional Formula button changes from blue to red, and the pencil character inside the button points at a different angle. This indicates that a conditional formula is set for this property.

To change the existing formula, click the Conditional Formula button again and change the formula that appears in the Format Formula Editor. To delete conditional formatting and return to absolute formatting (or no formatting at all), just highlight and delete the whole conditional formula. Then, click the Save and Close button. You ll notice that the Conditional Formula button has returned to a blue color with the pencil pointed in its original direction.

Note  

While most conditional formulas must use a built-in formatting function for the Then, Else, or Case clauses of your formula, the Size property is a little different from other properties. In this case, the result of your conditional formula must be a number, which will indicate the font size to be used.

If you re formatting an on-off property, the general procedure for conditional formatting is the same. But when you click the Conditional Formula button next to the property, you won t see any additional functions in the Function Tree box of the Format Formula Editor, because you can t use an If-Then-Else or Select Case formula to format this property. Because the property can have only one of two states, on or off, you must format it with a Boolean formula that can return only one of two results: true or false.

To add a drop shadow to Customer Name fields of customers who have last year s sales greater than $100,000, start by right-clicking the Customer Name field. Choose Format Field from the pop-up menu, choose the Border tab, and click the Conditional Formula button next to the Drop Shadow property. When the Format Formula Editor appears, type in the following Boolean formula:

 {Customer.Last Year's Sales} > 100000 

The Boolean formula will evaluate to only one of two states: true or false. If the formula returns true, the formatting property will be turned on and the field will have a drop shadow around it. If the formula returns false, the property will be turned off and the field won t have a drop shadow.

You may be curious about how conditional formatting and absolute formatting interrelate. Consider the following scenario. You choose an absolute color of Red on the Font tab of the Format Editor and click OK. Of course, every occurrence of the field will be red. You then return to the Format Editor and, without changing the absolute formatting, click the Conditional Formula button next to the Color property and add the following formula:

 If {Customer.Last Year's Sales} > 50000 Then Blue 

Note the missing Else clause. Remember that Crystal Reports does not require an Else clause in an If-Then-Else formula. In a regular formula, if the If test fails and there s no Else clause, the formula returns an empty string, zero, or other default value based on the data type of the formula. But what color will the font take on here if there s no Else clause and absolute formatting is set to red?

Contrary to what might seem logical, when the If test fails in this case, the font will show up in black type, despite the absolute formatting of red. This is by design ” if conditional formatting is applied, absolute formatting is ignored. If the conditional formula fails (and there s no condition to catch the failure, like an Else clause), the Windows Control Panel default color or format for that type of object will be used. Be careful with this if you don t use Else clauses, especially if you re formatting background colors. A font color of black isn t necessarily problematic , but a background color of black will often cause your report to look like someone plastered electrical tape all over it!

The exception to this rule, and a way to combine absolute and conditional formatting, is to use the DefaultAttribute function, located in the Formatting Functions category of the Function Tree box in the Format Formula Editor. If you use this function with the Then, Else, or Case clause, the formula will use the setting from the absolute formatting property. Hence,

 If {Customer.Last Year's Sales} > 50000 Then Blue 
 Else DefaultAttribute 

will show sales figures over $50,000 in blue and others in red (provided that the absolute color chosen in the Format Editor is red). If you change the absolute color, then figures over $50,000 will still show up in blue, but the rest will take on whatever color you specified as absolute.

Tip  

If you ve applied conditional formatting to a field that s also being formatted with the Highlighting Expert, the Highlighting Expert will take precedence. Only if it doesn t change the formatting of a field will conditional formatting be visible.

Creative Use of the Suppress Property

If you search through the Format Editor, you ll notice that virtually all formatting properties can be set conditionally. One of the most flexible is the Suppress property on the Common tab. You may consider that absolutely setting the Suppress property is of limited usefulness . (Why even bother putting the object on the report if you re just going to suppress it?) There are some good reasons for suppressing the object; for example, a formula that sets a variable to zero in a group header has to be physically placed in the header to work properly, but you don t want zeros showing up at the top of every group .

There are many more situations in which conditionally suppressing an object can be useful. Here are some examples, and the corresponding Boolean formulas you will apply to the Suppress property:

• Placing the word continued in a repeated group header In Chapter 3, the repeated group header was introduced. If you select this option in the Group Options dialog box, a group header section will repeat at the top of a page if a group continues from the previous page. Indicating that this group continues from the previous page adds readability to your report. Place a text object that contains the word continued, or something similar, in the group header close to the Group Name field. You must now suppress it if it is not in a repeated group header. Conditionally suppress the text object with the following Boolean formula:

   Not InRepeatedGroupHeader 

  Tip  

  When you conditionally suppress an object, you use a Boolean formula; when your formula returns true, the object will be suppressed. So, you may have to think backward when conditionally suppressing.

• Showing a bonus message only for certain records You may want a report to indicate that a certain record (for example, a certain order or a certain salesperson) has exceeded a predefined goal amount. Simply create a text object that displays something like Congratulations! You ve exceeded the sales goal. Again, you have to think about when you don t want the text object to appear, not when you do. Assuming a $10,000 sales goal, conditionally suppress the text object with the following Boolean formula:

   {AccountRep.Sales} <= 10000 

• Showing a different heading on page 2 and later You may want to have a larger report title, perhaps including the company logo and a large font, on page 1 of the report. However, every other page needs to contain a smaller title without the logo, and perhaps include the word continued. If you put the large title in the report header, you ll only see it at the beginning of the report. Putting the smaller title in the page header, on the other hand, will result in it showing up on every page, including page 1. Create the text object that contains the smaller title and place it in the page header along with any column headings or other objects you want to appear on every page, but conditionally suppress the text object containing the title with the following Boolean formula (PageNumber is a built-in function from the Print State category of the Formula Editor Function Tree box that returns the current page number):

   PageNumber = 1 

  Tip  

  You can also create string formulas that provide roughly the same functionality as these examples and place them in appropriate report sections. However, to minimize potential formula clutter, it sometimes may be preferable to just create text objects and conditionally suppress them.