Other Crystal Reports Logic Constructs

Many advanced users (particularly those with programming backgrounds) will often find some of the procedural capabilities of high-level computer languages useful when designing reports. If you fall into this category (perhaps you are a Basic programmer), not only will the typical procedural constructs in Basic syntax open up enhanced flexibility for you, but similar logic constructs in Crystal syntax will also make your reporting life easier. Even if you re not a programmer, you ll probably soon find that these features come in handy in the more advanced reporting situations you ll encounter.

The term logic construct refers to the features of the Crystal Reports formula language that enable you to go beyond basic If-Then-Else logic. For example, it can be tedious to write long repetitive If-Then-Else formulas to perform tasks such as testing for more than a small number of conditions, picking apart strings, or cycling through multiple-value parameter fields or other arrays. Crystal Reports logic functions such as Select Case, For loops, and Do loops make these tasks much easier. These functions enable Crystal Reports formulas to move closer and closer to a full procedural language, such as Visual Basic.

Select Case Statement

Select Case is very similar to its Visual Basic counterpart . It provides a much simpler and cleaner approach to testing for multiple conditions and returning appropriate results ”those complex If-Then-Else statements can now be replaced with more readable and easy-to-maintain logic.

Look at the following compound If-Then-Else statement:

 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 a relatively simple formula that checks whether the @Ship Days formula returns a 0 or a 1, returning a different string value in each case. If neither of these conditions is true, a catchall Else clause displays another string value. While this particular example isn t particularly complicated, it could quickly become much more difficult to interpret and maintain if more than two conditions have to be tested for.

Select Case is much better suited to this type of logic. Consider the following:

 Select {@Ship Days} 
 Case 0: 
 "Shipped Same Day" 
 Case 1: 
 "Shipped in 1 day" 
 Default: 
 "Shipped in " + ToText({@Ship Days},0) + " days"

You may choose Select Case from the Control Structure category of the Formula Editor Operator Tree or simply by typing the correct syntax. Begin with the word Select followed by a database field, formula, or other expression. Then, supply multiple Case clauses, each testing the value of the Select expression (make sure the value you supply to each Case clause is the same data type as the Select expression). If you want to have the formula return the same result for several different values of the Select expression, you may separate the values after the Case clause with commas and include the To operator to supply a range of values. After the Case clause, supply a colon (don t use a semicolon ”this isn t the end of the statement) and then supply the expression you want the formula to return if the Select value equals the Case clause. Remember that all expressions that result from a Case clause must be the same data type ”you can t have one Case clause return a string and another Case clause return a number. After the Case clauses have been defined, you may supply an optional Default clause, followed by a colon , and the expression you want the formula to return if none of the Case clauses match the Select value.

For Loop

Basic programmers have always enjoyed the capability to loop through fragments of program code over and over to perform repetitive logic. This also becomes helpful in certain reporting situations (if, for example, you need to cycle through a multiple-value parameter field or iterate through a number array).

Crystal Reports includes the ubiquitous For loop in both syntaxes (except there s no Next clause in the Crystal syntax version). The For loop uses a counter variable to keep track of how many times a specified piece of logic has been cycled through. The For clause sets both the beginning and ending values of the counter variable. The optional Step clause tells the For statement how to increment the counter variable (the default is 1 if the Step clause is left out). The For statement is closed off by the word Do, followed by one or more statements enclosed in parentheses (use a semicolon to separate more than one statement within the parentheses). The statements inside the parentheses will be executed once for every increment of the counter variable.

The following formula displays all the entries a user has chosen in the multiple-value Region parameter field:

 NumberVar Counter; 
 StringVar Message := "Regions Chosen: "; 
 
 // cycle through all members of the multi-value 
 // ?Region parameter field 
 For Counter := 1 to Count({?Region}) Step 1 Do 
 ( 
 // build the Message variable, along with comma/space 
 Message := Message & {?Region}[Counter] + ", " 
 ); 
 
 // strip off last comma/space added by the loop 
 Left(Message, Length(Message) - 2)

First, this formula declares two variables: Counter to increment the For loop, and Message to accumulate the parameter field values (look at the next section of the chapter for information on using variables ). The For loop then cycles Counter from 1 to the number of elements in the parameter field (returned by the Count function). For each loop, Counter is used to retrieve the next element of the parameter field and accumulate it, along with a comma and a space, in Message. The final statement of the formula, which is not associated with the loop, strips off the last comma and space that were added inside the last occurrence of the loop.

Note

Although Crystal Reports now allows string formulas and variables to return up to 64K of characters (only 254 characters could be returned by strings prior to version 9), good formula logic dictates adding a test in this formula that uses the Exit For statement to exit the For loop if the Message variable may ever approach approximately 64,000 characters in length. If the loop tries to accumulate more than 64K characters in the variable, a run-time error will occur.

While Do Loop

A looping construct similar to the For loop described previously can be used to repeat statements while a certain condition is met. Whereas the For loop uses a counter variable to determine how many times the loop executes, the While Do loop evaluates a condition before each occurrence of the loop and stops if the condition is no longer true. This construct is similar to Do and While loops used in Visual Basic and other procedural languages.

The following listing is a formula that sets a variable to a phone number database field and then uses a While Do loop to look for hyphens in the variable. As long as a hyphen exists in the variable, the Do loop will execute a statement to pick out the hyphen, leaving behind only the pure numbers from the phone number. When there are no more hyphens in the variable, the While condition will fail and the statement after the closing parenthesis of the While Do loop (the variable name , which will display the number without the hyphens) will execute.

 StringVar NewPhone := {Customer.Phone}; 
 
 While Instr(NewPhone,"-") > 0 Do 
 ( 
 NewPhone := Left(NewPhone, Instr(NewPhone,"-") - 1) & 
 Right(NewPhone, Length(NewPhone) - Instr(NewPhone, "-")); 
 ); 
 
 NewPhone

Using the Join and Split Functions to Avoid Loops

While the previous code is a great For loop example, there's actually another built-in formula function that negates the need for the variable declarations, the looping logic, and the removal of the training comma/space when creating a single string containing multi-value parameter field entries. Look at the following code:

 "Regions chosen: " + Join({?Region}, ", ")

This formula uses the Join function, similar to its Visual Basic counterpart, which takes all the elements of the array supplied in the first parameter (a multi-value parameter field actually is an array), concatenates them together, and optionally separates each with the string supplied in the second parameter. Join performs the same thing as all the looping logic and variable manipulation demonstrated earlier, with one simple function.

Conversely, you may wish to take a string value or variable that contains multiple strings separated by a common delimiter (such as a slash) and create an array of string values. You could create a loop that cycles through the string one character at a time, looking for the delimiter (the slash), and performing complex logic to extract the substring and add it to an array. But the Split function, like its equivalent in Visual Basic, will perform all this logic for you automatically. Look at the following code fragment (this is not a complete formula):

 StringVar array Regions; 
 Regions := 
 Split("Northwest/Southwest/Northeast/Southeast/Midwest", "/")

The second line of code will populate the Regions array variable with five elements by looking through the string and separating the five substrings that are separated by slashes .

But, don't forget your looping capabilities just yet ”the Join and Split function work only with string values. If you have a multi-value parameter field that is designated as a number, date, or other non-string type, you'll still need to use loops to extract the individual elements. And if you want to build a non-string array, you may need to use loops as well, as Split works only with strings.

Although this is a good example of how a While Do loop can cycle while a condition is true, it s a fairly complex process for the relatively simple search and replace function that it performs. For a more streamlined formula, you can use the Crystal Reports Replace function, as in the following example:

 Replace({Customer.Phone}, "-", "")

In this case, the Replace function makes use of three parameters: the first being the string field or value that you want to modify, the second being the character or characters you want to search for, and the third being the character or characters you want to replace the search characters with.

Note

The previous logic construct examples are presented in Crystal syntax. Basic syntax logic constructs are very similar, if not identical, to their Visual Basic counterparts. Just remember that you must use at least one instance of the Formula intrinsic variable in Basic syntax to return a result to the report.