Creating If-Then-Else Formulas

Objective

After completing this lesson, you will be able to modify data display using If-Then-Else formulas.

Overview of Control Structures

Crystal Reports provide a list of functions in the Function Tree of the Formula Workshop. Functions are used to manipulate data. There are different types of Crystal Reports functions that includes String, Math, Type Conversion, Date and Time. Let’s start with the Function Tree before we go into more details in this lesson.

The following screenshot shows the function tree in the Formula Workshop.

A screenshot of the Formula Workshop - Formula Editor - Years Employed window in a report-design application showing a three‑column layout with a toolbar across the top, a left tree of formula and field names (including Age, Emergency Contact, Sales Rep, Year Hired, Years Employed), a narrow center Report Fields pane, and a right Functions tree expanded to Date and Time functions with the Date (dateTime) entry highlighted in blue and two yellow callout labels reading Function Tree and Date and Time Functions.

Formulas without control structures execute each expression in the formula exactly once when the formula is evaluated. The expressions are executed in a sequential fashion, from the first expression in the formula to the last.

Control structures enable you to allow some flexibility. Depending upon which control structure you choose, you can skip over some of the expressions or you can repeatedly evaluate some expressions depending on a condition.

Control structures are the primary means of expressing business logic. Typical report formulas make extensive use of them.

If-Then-Else Formula Overview

See the following scenario implementing a condition using If-Then-Else.

In a workspace, two coworkers sit side-by-side at a desk with a laptop—one leaning forward and pointing while the other asks How does If-Then-Else Formula work? in a speech bubble, a larger explanatory caption beneath them outlines the three parts (If, Then, Else).

The following screenshot shows the use of the if-then-else formula.

A screenshot of a Crystal Reports Formula Editor shows a user creating a new formula—highlighted by yellow callouts and arrows—from the report fields pane and typing logic that maps Husband and Wife to Spouse (If {Employee.Emergency Contact Relationship} in [Husband,Wife] Then Spouse Else {Employee.Emergency Contact Relationship}) while sample data tables below demonstrate the effect by listing first and last names on the left and a resulting Emergency Contact column on the right with normalized and original relationship values such as Brother‑In‑Law, Spouse, Sister, Father, Son, and Cousin.

Parts of an If-Then-Else Formula

If-Then-Else formulas have the following three parts:

If — Sets the condition or test
Then — Sets the action to be taken if the condition is met, that is, if the test returns a true value
Else — (optional) Sets the action to be taken if the condition is not met, that is, if the test returns a false value

Creating an If-Then-Else formula

Suppose that you want to display a rating beside each customer’s order figures in an Orders report depending on the level achieved: high orders or low orders. You can accomplish a task such as this by using an If-Then-Else formula.

Code Snippet

1234
If {Orders.Orders Amount} > 40000 Then
     "High orders"
Else
     "Low orders"

Hint

The data type must be the same for the Then and Else parts of the formula (text, numeric, date). For example, if the Then part of the formula prints a text string, the Else part of the formula must also print a text string.

Summary

Crystal Reports offer a range of functions for data manipulation through the Function Tree in the Formula Workshop.
Control structures in Crystal Reports allow for flexible formula execution, including conditional logic for business rules.
The If-Then-Else Formula in Crystal Reports is used for conditional logic, such as categorizing data based on conditions.

Next lesson