Return to Newsletter Contents...

Conditional Formatting in Crystal Reports

by: Tom Hinkle, Consultant, CRCP

Working with conditional formatting

Conditional formatting is formatting that applies only under certain conditions.  For example, in a report you may only want:

• Customer balances printed in red if they are past due
• The dates to appear in Day, Month, Year format if the customer is Canadian
• Background color to appear on every other line

Crystal Reports makes it easy to apply conditional formatting.  You specify conditions using simple formulas.  When a conditional formatting formula is set up, the formula overrides any fixed settings you have made in the Format Editor.  For example, if you select the Suppress option, then set up a conditional formula for the Suppress option, the property will still apply only if the condition in the formula is met.  Crystal Reports enables you to set both on and off properties and set attribute properties conditionally.  However, each of these requires a different kind of formula.

Conditional on or off properties

A conditional on or off property tests to see if a condition has been met.  It is on if the condition is met, off if the condition is not met.  There is no middle ground.  Use Boolean formulas for this kind of formatting.

Crystal syntax example

Condition:  For example: {orders.order amount} > 5000

The program tests each value to see if it meets the condition and it returns a "yes" or "no" answer. It then applies the property to every value that returns a "yes" answer.

Conditional attribute properties

A conditional attribute property tests to see which of two or more conditions is met.  The program then applies the formatting appropriate to the condition.  For example, assume that you want values under quota printed in red and all other values printed in black.  The program tests to see whether the value is under quota or not.  If it is under quota, then it applies the red attribute; if it is not, then it applies the black attribute.

Use an If-Then-Else formula for this kind of conditional formatting.

Crystal syntax example

If Condition A Then

     crRed

Else

     crBlack

When conditional attribute properties are set up, Crystal Reports loads a selection of attributes into the Functions list in the Formula Workshop.  Double-click any of these attributes to add them to a formula.  For example, if you are setting horizontal alignment conditionally, the Functions list contains attributes such as DefaultHorAligned, LeftAligned, and Justified.  If you are setting borders conditionally, the Functions list contains attributes such as NoLine, SingleLine, and DashedLine.

Note:    Always include the Else keyword in conditional formulas; otherwise, values that don't meet the If condition may not retain their original format.  To retain the original format of values that don't meet your If condition, use the DefaultAttribute function.

Crystal syntax example

If Condition A Then

     crRed

Else

     DefaultAttribute

You can take this kind of property one step further.  You can specify a list of conditions and a property for each; you are not limited to two conditions.  For example, if you have a number field on your report that contains sales figures from countries around the world, you can specify the number attribute(s) that you want to apply to each country.  In this case, your conditions would specify that if it is from Country A, the program should apply the Country A attribute; if it is from Country B, the Country B attribute; if it is from Country C, the Country C attribute, and so on.

With more than two alternatives, use this kind of formula:

Crystal syntax example

If Condition A Then

     crRed

Else If Condition B Then

     crBlack

Else If Condition C Then

     crGreen

Else

     crBlue

Use a multi-condition If-Then-Else formula for this kind of conditional formatting.

Changing fonts conditionally

For memo or string fields that are based on conditions such as a parameter value, you can change the font, font style, size, and color for these fields using the Format Editor.

To change fonts conditionally

1. Right-click the field you want to format to bring up the shortcut menu.

2. On the shortcut menu, click Format Field.

          The Format Editor dialog box appears.

3. Click the Font tab.

4. To change any of the font options, click the appropriate Formula button, located on the right side of the dialog box.

5. In the Formula Workshop, you can specify that conditional fonts will change only when certain conditions are met.

6. Click Save and close.

          Note:    

• If there is an error in the formula, a message box appears, asking if you want to save anyway.  If you click No, a second message box is displayed, detailing the error

• If there is no error in the formula, you are returned to the Format Editor.  Note that the Formula button has changed.  This indicates that a formula has been entered for that property.  Search for "Formula Compiler Errors" in Crystal Reports online help

7. Click OK to return to your report.

Changing X position conditionally

You can change the X position (that is, the horizontal position from the left margin) for an object based on a condition. You might do this when you want objects to appear in different columns when their values meet a certain condition; for example, orders that shipped on time appear in the first column, while orders that shipped late appear in a second column.

Note:    You cannot conditionally change the X position of line or box objects.

To conditionally change the X position of an object

1. Right-click the field that you want to move conditionally, and select Size and Position.

2. Click the Conditional Formula button next to the X position value field.

3. In the Formula Workshop, enter your conditional X position formula text.

For example, to move orders that were shipped late to a second column, enter formula text such as this:

If {Orders.Ship Date} < CDateTime (2004, 01, 01, 02, 10, 11) then 4320

Note:    The number 4320 represents the new position that you want to define as the second column. The position is measured in twips; there are 1440 twips in an inch.

4. Click Save and close to return to your report.

5. Click OK to save your position setting.

Crystal Reports moves objects that meet your condition to a new position, but leaves those objects that don't meet the condition where you originally placed them.

Creating footers after the first page

You may choose to print a page footer on all pages except the first page. You can do this by formatting the Page Footer section conditionally, using an on or off property.

To create footers after the first page

1. Place the field you want displayed as a page footer in the Page Footer section of the report.

2. On the Report menu, click Section Expert.

Tip:   Another way to do this is to click the Section Expert button on the Expert Tools toolbar.

          The Section Expert dialog box appears.

3. In the Sections area, click Page Footer.

4. To open the Formula Workshop, click the Formula button, located to the right of the Suppress (No Drill-Down) check box.

5. Enter the following formula in the Format Formula Editor:

          Crystal syntax example:

          PageNumber = 1

          Basic syntax example:

          formula = PageNumber = 1

          This formula suppresses the page footer on the first page, but only on this page.

6. Click Save and close.

         Note:

• If there is an error in the formula, a message box appears, asking if you want to save anyway. If you click No, a second message box is displayed, detailing the error.
• If there is no error in the formula, you are returned to the Section Expert. Note that the Formula button has changed. This indicates that a formula has been entered for that property. Search for "Formula Compiler Errors" in Crystal Reports online help.

7. On the Standard toolbar, click Preview to preview the report and ensure that the page footer appears on all pages but the first.

         Note:    

• If you have a multi-line page footer and have inserted the lines into separate Page Footer sections, you will need to suppress each section conditionally, using the formula above.

• To create a page header that appears on all pages but the first, place the header information in the Page Header section and then suppress that section conditionally, using the same formula that was used for suppressing the Page Footer section.

Go to Top  |  Return to Newsletter Contents