If-Then-Else Formulas
One of the complaints that s sometimes heard in the competitive community of database report writers is, Crystal Reports is too complex ”it s made for programmers. While this complaint may or may not ring true, there is no doubt that elements of common programming languages can be found in the Crystal Reports formula languages. The first of these programming-oriented features is If-Then-Else logic in formulas. The If-Then-Else combination is the cornerstone of much computer programming code, so once you learn If-Then-Else concepts, you ll be on your way to performing really sophisticated report customization.
If-Then-Else formulas perform a test on a database field, another formula, or some combination of them. Your test can be as simple or as complex as you need it to be ”perhaps just checking to see if a sales figure exceeds the $1,000 bonus threshold. Or, you may want to check the number of days a product took to ship, in conjunction with the carrier who shipped the product and the sales level of the customer, to determine if a shipment met your company s shipping goals. If the test passes (returns true ), the formula will return a certain result. If the test fails (returns false ), a different result will be returned.
If-Then-Else formulas are created with the following syntax:
If <test> Then <result if true> Else <result if false>
The test portion of an If-Then-Else formula must use comparison operators found in the Operator Tree box (or a Boolean formula, discussed later in the chapter). You ll find a Comparisons section of the box that, when opened, shows operators that test for equal, less than, greater than, and other combinations of conditions. These can be used in conjunction with And, Or, and Not Boolean operators to combine multiple conditional tests together. Here s a simple If-Then-Else formula that will return a string based on an order amount:
If {Orders.Order Amount} > 5000 Then "Bonus Order"
Else "Regular Order" The Order Amount database field is tested to see if its value is greater than 5,000. If the test is true, the formula returns the Bonus Order string. Otherwise, the formula returns the Regular Order string.
Boolean operators can also be used to combine multiple comparisons together. You can use And, Or, and Not Boolean operators. The preceding formula has been slightly enhanced in the following formula, using a Boolean operator to combine two comparisons:
If {Orders.Order Amount} > 5000 And Month({Orders.Order Date}) = 12
Then
"Holiday Bonus Order"
Else
"Regular Order" Here, the order amount has to exceed 5,000 and the order must have been placed in December for the formula to return Holiday Bonus Order. Orders over 5,000 in other months will still be regular orders. If you change the And to an Or in the preceding formula, then all orders in December will be bonus orders, regardless of amount. Orders over 5,000 will also be considered bonus orders the rest of the year.
Data Types in If-Then-Else Formulas
While creating If-Then-Else formulas, you must pay special attention to the data types that you re using in the formula. In the If test of the formula, make sure you use similar data types in each individual comparison operation. For example, if you want to test whether Customer.Country is USA, the test will be
If {Customer.Country} = "USA" Since Customer.Country is a string field, you need to compare it to a string literal, enclosed in quotation marks or apostrophes (just quotation marks in Basic syntax). If the field you are testing is numeric, you need to use a number constant, as in the Orders.Order Amount sample shown previously. If you mismatch data types, such as these:
If {Orders.Order Amount} > "5000" you ll receive an error.
If you use multiple comparisons separated by Boolean operators, each comparison can have a different data type. For example, if you want to combine the two tests mentioned previously, your formula would start out as follows :
If {Customer.Country} = "USA" And {Orders.Order Amount} > 5000 In this case, the different data types in the If part of the formula are fine, as long as each side of each comparison is of the same data type.
For example, you may have an existing formula on your report, @Ship Days, that calculates the number of days it took to ship an order. But since @Ship Days is a numeric formula, it will display a zero on your report if the order was placed and shipped on the same day. Therefore, you would write the following If-Then-Else formula to show the words Same Day on the report if @Ship Days is zero, or to show just the contents of the @Ship Days formula if it is not zero:
If {@Ship Days} = 0 Then
"Same Day"
Else
{@Ship Days} But if you use the Check button in the Formula Editor to check the syntax of this formula, you ll receive an error:
The problem is that Crystal Reports doesn t know what data type to assign to the formula. If the test returns true, the formula will return a string for the words Same Day . However, if the test returns false, the formula will return @Ship Days, which is a number. Crystal Reports must assign a data type to a formula when it s first created ”it can t wait until the report is running. Therefore, even though the If part of a formula can contain various data type tests, the Then and Else parts must contain the same data types.
Remember the function that converts other data types to strings? The following will solve this problem:
If {@Ship Days} = 0 Then
"Same Day"
Else
ToText({@Ship Days},0) This result is better, because it doesn t show zero as the number of ship days. But we want to take it a step further to make the report more readable. Look at the enhanced version of this formula:
If {@Ship Days} = 0 Then
"Shipped Same Day"
Else
"Shipped in " + ToText({@Ship Days},0) + " days" This looks better on the report, particularly if the report isn t a straight columnar report. You might want to put this in a group header for an order, before the individual line items for the order show up in the details section. But there s just one more problem. What will this formula return if it took only one day to ship the order?
Shipped in 1 days
While this probably won t mean your dismissal from the report development team, why not go one easy step further to make the report look even better? Try the following:
If {@Ship Days} = 0 Then
"Shipped Same Day"
Else
If {@Ship Days} = 1 Then
"Shipped in 1 day"
Else
"Shipped in " + ToText({@Ship Days},0) + " days" This is an example of a compound or nested If-Then-Else statement. Notice that you re not limited to one If, Then, or Else clause in a formula. You can make another If statement the result of the first Then, or the result of the first Else, and on and on. There is no specific limit to how many levels you can nest these, but obviously the formula becomes hard to follow after only one or two levels. Also, don t forget that the Else clause isn t required, so when you nest these, you won t have to always have a matching Else clause for every If.
Multiple Actions with One If-Then-Else Statement
You ll notice in all the preceding examples that only one action occurred as the result of the Then and Else parts of the statement. While this is okay for many types of formulas, sometimes you may want several things to happen, particularly when you need to set the contents of several variables as the results of a single Then or Else clause ( variables are discussed later in this chapter). In this situation, you can simply repeat the If-Then-Else test several times, with a different result for each Then and Else clause. Just be sure to separate each If-Then-Else statement from the next with a semicolon. For example, to set the contents of several variables in one formula, you might use the following:
NumberVar GroupBonus;
NumberVar GroupFollowUpCount;
NumberVar ReportBonus;
NumberVar ReportFollowUpcount;
StringVar GoodCustomer;
If {Orders.Order Amount} > 5000 Then
GroupBonus := GroupBonus + 1
Else
GroupFollowUpCount := GroupFollowUpCount + 1 ;
If {Orders.Order Amount} > 5000 Then
ReportBonus := ReportBonus + 1
Else
ReportFollowUpCount := ReportFollowUpCount + 1 ;
If {Orders.Order Amount} > 5000 Then
GoodCustomer := {Customer.Customer Name}
All of these If statements will be evaluated and the resulting variables will be set. The formula will display the result of the last action in the If statement on the report. In this example, if the Order Amount is over $5,000, all the bonus variables will be incremented by one and the GoodCustomer variable will be assigned the customer name. But because the GoodCustomer variable assignment is the last action that executes, the customer name is what the formula actually displays on the report. If the Order Amount is less than $5,000, then the two FollowUpCount variables will be incremented by one. But because the last statement still tries to set the GoodCustomer variable and fails (and there s no Else clause), the GoodCustomer variable won t be assigned a value and the formula will return an empty string.
If you look at the previous example, you see quite a bit of duplicate typing (the If test is repeated three times). This duplication can be eliminated by creating just one If-Then-Else statement, but supplying several actions to the Then and Else clauses, separating the action statements with a semicolon and surrounding them all with parentheses. Here s the same formula created using this shortened approach:
NumberVar GroupBonus;
NumberVar GroupFollowUpCount;
NumberVar ReportBonus;
NumberVar ReportFollowUpcount;
StringVar GoodCustomer;
If {Orders.Order Amount} > 5000 Then
(GroupBonus := GroupBonus + 1 ;
ReportBonus := ReportBonus +1 ;
GoodCustomer := {Customer.Customer Name})
Else
(GroupFollowUpCount := GroupFollowUpCount + 1 ;
ReportFollowUpCount := ReportFollowUpCount + 1;
GoodCustomer := "")
| Caution | In the preceding example, you ll need to make sure to include the statement to assign the GoodCustomer variable an empty string in the Else clause. Otherwise, you ll receive the A String Is Required Here error. This is because the Then and Else clauses must still return the same data type. Without the GoodCustomer assignment in the Else clause, the Then clause will be returning a string value (a string variable is being assigned), and the Else clause will be returning a numeric value (a numeric variable is being assigned). |
Helpful Built-in Functions for If-Then-Else Formulas
If you look in the Function Tree box of the Formula Editor, you ll notice a Print State category. Opening this category shows a variety of built-in functions that you can use in If-Then-Else (and other) formulas to enhance your reporting flexibility. For example, all the Special Fields discussed in Chapter 2, such as Page Number, Total Page Count, Print Date and Time, Record Number, Group Number, and others, are available. There are other special functions that you can use to test for a null database value in the current, next, or last record; to check whether the current database record is the first or last; or to check whether the formula appears in a repeated group header. By using these special built-in functions, you can create formulas that make your reports more intuitive and easier to read.
 |
If the If-Then-Else logic described so far isn't enough to propel you toward (or perhaps away from) a programming career, Crystal Reports includes a bevy of other If-Then-Else possibilities to help you reconsider!
First, the If-Then-Else statements in Basic syntax differ from those Crystal syntax versions described previously. Basic syntax follows the more typical If-Then-Else-EndIf approach familiar to Basic language programmers. In particular, this makes performing multiple actions as the result of a single If-Then-Else statement more straightforward by introducing the End If clause:
If <test> Then
<statement>
<statement>
<more statements>
Else
<statement>
<statement>
<more statements>
End If
Also, you can use the Basic syntax ElseIf clause (don't forget it's one word ”no space) to allow nesting of multiple If conditions in one statement:
If <first test> Then
<statements>
ElseIf <second test> Then
<second test statements>
Else
<statements if first test fails>
End If
Yet another permutation of If-Then-Else logic exists in both Crystal and Basic syntaxes. If you've used Microsoft Office products, such as Microsoft Access, you may be familiar with the IIF Immediate If function. This shortened version of protracted If-Then-Else logic is actually a single function, similar to ToText or UpperCase (it appears in the function tree under Programming Shortcuts) that accepts three arguments. The function syntax is as follows:
IIF(<boolean expression>, <result if true>, <result if false>)
This function can simplify If-Then-Else logic for small, simple formulas, or when you want to perform a mini If-Then-Else statement as part of a larger formula. For example, consider the following string formula using traditional If-Then-Else logic:
If {Customer.Country} = "USA" Then
{Customer.Customer Name} + " requires domestic shipping charges"
Else
{Customer.Customer Name} + " requires international shipping charges" By using the Immediate If function, this can be simplified to the following:
{Customer.Customer Name} + " requires " +
IIF({Customer.Country} = "USA", "domestic", "international") +
" shipping charges" | |
Here are some examples:
-
IsNull function
If IsNull({Customer.Region}) Then
""
Else
", " + {Customer.Region}
The IsNull function is critical if you may encounter null values in database fields that you include in your formulas. By design, a Crystal Reports formula will return a null value if any part of the field contains a null value. If a numeric formula performs addition on a field containing a null value, the formula won t treat the null as a zero and return a numeric result with the rest of the addition ”the formula will return a null. If you are performing string concatenation and one of the string fields is null, the whole formula will return null, not the rest of the concatenation.
By using the If-Then-Else test with the IsNull function described previously, you can check whether the region field contains a null value. If so, the formula will return an empty string (denoted by the two sets of quotation marks). Otherwise, the formula will return a comma, a space, and then the region name. This formula can then be concatenated with city and ZIP database fields in another formula to form a city-state-ZIP line. If the region in the database is null, the other formula won t become null.
| Tip | Several factors determine whether or not the database will contain null values. In many cases, this is determined by the way the database is initially designed. If you prefer to avoid null values appearing on the report, Crystal Reports allows you to convert them to a default format (typically, a zero for number fields and an empty string for string fields). To do this for all new reports in the future, check Convert Database NULL Values to Default on the Reporting tab of the File Options pull-down menu. You ll also find the Convert Other NULL Values to Default option to deal with other non-database values (such as other formulas) that may return null values. To set these options for just the current report, check the same options after choosing File Report Options from the pull-down menus . |
-
Next function
If {Customer.Customer Name} = Next({Customer.Customer Name}) Then
{Customer.Customer Name} + " continues on next page..."
The Next function reads a field in the next record of the database. This formula compares the field in the next record to the same field in the current record. If the values are the same, you know that the same customer will appear in the first record on the next page, and you can note this with a text message. This formula would typically be placed in the page footer.
| Tip | Notice that there is no Else clause in this formula. Crystal Reports doesn t require an Else clause in an If-Then-Else formula in Crystal syntax. If you leave the Else off and the test returns false, the formula will produce an empty string. |
-
InRepeatedGroupHeader function
If InRepeatedGroupHeader Then
GroupName ({Customer.Customer Name}) + " - continued -"
Else
GroupName ({Customer.Customer Name})
If you place this formula in the group header of a group with the Repeat Group Header on Each New Page option turned on (this can be set when you create or change a group ”see Chapter 3), - continued - appears only when the group header is repeated. This also uses the GroupName function to return the group name field for a particular group. An InRepeatedGroupHeader test also comes in handy if you are resetting variables in formulas you are placing in the group header ( assuming that you ll always be encountering a new group when the group header prints). Because you don t want to reset your variables in a repeated group header, you can condition your variable assignment statement on the value of InRepeatedGroupHeader. You might use something similar to the following:
If Not InRepeatedGroupHeader Then GroupBonus := 0
EAN: N/A
Pages: 223