Formatting Data Conditionally

Objective

After completing this lesson, you will be able to use formulas to conditionally format data display.

Conditional Formatting

See the following scenario depicting conditional formatting using formulas.

Two colleagues stand side-by-side in a busy warehouse-style workspace, one gesturing at a laptop while the other holds documents as they engage in a focused, collaborative discussion—one speech bubble asks What is Conditional Formatting in a report? while another explains that formulas can toggle formatting, and a stylized bar-chart-and-magnifying-glass icon to the right underscores the data-analysis theme.

The following screenshot shows how you can turn on or off the Drop Shadow property using a Boolean formula.

An annotated screenshot collage of a report-design workflow that shows a three-column customer report (columns: Customer Name, Order Amount with dollar values, and Orders over 5000 flagged Yes/No) linked by orange callouts and arrows to a Format Formula Editor and Formula Workshop window containing the Boolean expression {Orders.Order Amount} > 5000, illustrating the request to apply a drop shadow to Customer Name entries whose order amount exceeds $5,000 and a final preview where those qualifying names display the drop-shadow visual.

Watch this video to see how to use the If-Then-Else formula to conditionally change the background color of a field.

While most conditional formatting can be implemented using the Highlighting Expert, there are certain limitations that can be mitigated using formatting formulas.

Limitations of the Highlighting Expert

The Highlighting Expert is quicker and easier to use than the Formula Workshop (creating your own formula condition).

The Highlighting Expert, however, isn't as flexible as the Formula Workshop. To use the formatting capabilities of Crystal Reports to their full potential, create your own conditional formatting formulas with the Formula Workshop.

On or Off Properties

An on or off property uses a checkbox. When the checkbox is turned on, the property is always applied. When the checkbox is turned off, the property is never applied.

A screenshot of a Format Editor dialog box in a Windows-style application shows the Font tab with Arial selected (Regular, size 10, Black), an Effects section where the Strikeout and Underline checkboxes are unchecked and outlined with a red rectangle, a sample preview displaying -55,555.56, and OK, Cancel, and Help buttons along the bottom.

The Drop Shadow property for a Border is an example of an on or off property. When you conditionally apply an on or off property, the program applies the attribute when the condition is met and doesn't apply the attribute when the condition isn't met. Therefore, to set an on or off property, you must create a Boolean formula that returns yes for every value to which you want to apply the attribute. Whenever the result of the formula is no, the attribute is set to off.

Attribute Properties

A Windows-style Format Editor dialog box (Border tab selected) displays line-style dropdowns for Left/Right/Top/Bottom set to None, unchecked Tight Horizontal and Drop Shadow boxes, a Color section with Border set to black and Background checked to a vivid lime-green with its color palette expanded, a wide lime-green sample bar filled with repeated black X characters, and standard OK, Cancel, and Help buttons along the bottom, conveying a functional, utilitarian interface.

A conditional attribute property tests to see which of two or more conditions are met. The program then applies the formatting appropriate to the condition. For example, assume 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's under quota, then it applies the red attribute; if it isn't, then it applies the black attribute.

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

Functions List Attributes

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're setting horizontal alignment conditionally, the Functions list contains attributes including:

  • DefaultHorAligned

  • LeftAligned

  • Justified

If you're setting borders conditionally, the Functions list contains attributes including:

  • NoLine

  • SingleLine

  • 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, then use the DefaultAttribute function.

The program tests each record to see which of two or more conditions were met and applies the formatting appropriate to the condition. All attribute property options are listed within the Format Formula Editor for your use.

The ability to conditionally format fields and objects gives you more control over the presentation of your data in the report.

Use Functions and Formulas

In this exercise, you will use functions and formulas to manipulate data.

Use Functions and Formulas

ExerciseStart Exercise

Summary

  • Highlighting Expert is quicker and simpler than Formula Workshop but offers less flexibility.
  • You can apply formatting based on Boolean conditions using Formula Workshop.
  • You can use If-Then-Else formulas to test multiple conditions and apply formatting accordingly.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn