Crystal Reports 10: The Complete Reference
The one remaining type of formula that you may need to create is the Boolean formula, which can return just two values, true and false. You can think of a Boolean formula as just the test part of an If-Then-Else formula. When the formula is evaluated, it ultimately returns only one of the two states.
Here s a simple Boolean formula:
{@Ship Days} > 3
In this formula, the existing @Ship Days formula (a number formula) is tested to be greater than 3 (indicating a shipping exception). It either is or isn t greater than 3! If it is greater than 3, the formula returns a true value ”if it s not, the formula returns a false value.
When you then place this formula on your report, it will appear with a Boolean data type. If you have Show Field Names turned off in File Options (discussed earlier in the chapter), then you ll see the formula show up with the word True in the Design tab. If you format the field, you ll notice a Boolean tab in the Format Editor that lets you choose how you want the true/false values to appear on the report.
Although you may occasionally find Boolean formulas helpful when they re actually placed on the report, you ll probably use them much more often as a cornerstone for other formulas. For example, the Boolean formula shown previously indicates that Xtreme Mountain Bikes considers orders that took longer than three days to ship as exceptions. But, Xtreme really wants to break down the shipping exception rule according to Last Year s Sales. If the customer purchased more than $50,000 in merchandise last year, the three-day shipping exception will apply. However, if a customer purchased less, a six-day shipping exception applies.
This requires a compound Boolean formula, such as
({@Ship Days} > 3 And {Customer.Last Year's Sales} > 50000) Or {@Ship Days} > 6
which uses a combination of And and Or operators, along with the comparison operators, to create a more complex Boolean formula. What s important to remember, though, is that the ultimate result will still be either true or false. You can make a Boolean formula as complex as you want, using combinations of comparison operators along with And, Or, and Not operators, but in the end, only true or false will result.
| Tip | Notice the parentheses around the first part of this compound Boolean formula. They ensure that both @Ship Days is less than 3 and Last Year s Sales is greater than $50,000 before Or ing the @Ship Days greater than 6 test. Although this may make the formula more understandable, it is optional. Crystal Reports considers all Boolean operators (And, Or, and Not) equally in the order of precedence (discussed previously in this chapter). That is, it evaluates them equally as it travels through the formula from left to right. |
There are several benefits to creating Boolean formulas in this fashion:
-
After you create a complex Boolean formula, you can include it in other formulas as the test part of an If-Then-Else formula, as in the following:
If {@Shipping Exception} Then "*** Shipping Exception ***" Else "Shipped Within Goal"
This makes the second formula much easier to read and understand.
-
By using the Boolean formula throughout the report, you eliminate the need to retype the complex Boolean test repeatedly, thus reducing the chance of errors. Even more important, you have only one formula to change if the report requirements change down the road. For example, if you use the @Shipping Exception formula as the cornerstone for 50 other formulas in your report, and you later decide to reduce the Last Year s Sales qualification from $50,000 to $35,000, you have only one formula to change on your report, not 50. All the rest will follow the change.
-
You can use the Boolean formula in advanced record selection (covered in Chapter 8) and conditional formatting (covered in Chapter 9) to limit the report to certain records or to have certain objects on the report appear with different formatting.
Tip Crystal Reports online help is a wealth of wisdom on formula concepts and built-in functions. You ll find samples of every built-in function and many sample formulas that you can use as building blocks for your reports. Just click the Index tab within online help and type in the formula language function or statement you want help with. Then, pick the function from the list to see the help material for that function.