You can't use a custom function directly in a report; you must wrap the function in a formula. Therefore, the next order of business is to create a formula that applies your general concatenation function: specifically, concatenating the first and last names of customer contacts:

In the Workshop tree on the left, expand the Report Custom Functions node. If you followed the running example in this chapter, notice that ConcatWith1Space is listed under it. Note also that ContactFullName is listed under Formula Fields even though you haven't added functionality to it yet. The formula exists; it just doesn't do anything yet.

Figure 10-5. Use Formula Editor to create a formula.

Like with the Custom Function Editor, you get useful things to include in your formula: The Functions tree contains standard functions, and the Operators tree contains operators. You can drag these functions and operators down to the appropriate spot in the formula you're building, or you can type them by hand. Often, it's easier to type them than to drag them. Formula Workshop also has a Report Fields tree, which Custom Function Editor doesn't have. You can drag fields from the Field tree into the appropriate spot in the formula you're building.

If you've been following along, you don't need these handy tools just now — although they're good to know about — because you already did most of the work of building this formula when you created the ConcatWith1Space custom function. In that case, all you need to do next is the following:

The next step is to add the contact's full name to the report. You do that with the help of the Formula Expert.

Currently, the Top Five USA Customers report lists the customer name, state, and order total for the five U.S. customers who have purchased the most merchandise from Xtreme Mountain Bikes. Suppose that at each of these customer sites, you want to insert the full name of the contact person between the Customer Name and the State columns:

Next, add the full name of the contact to your report:

Figure 10-7. The report with a column created by formula rather than by database field.

SQL Expression Editor is another incarnation of Formula Workshop. It's very similar to Formula Editor, but you use it to build SQL expressions from tables, functions, and operators. With an SQL expression, you can issue commands to the database that underlies your report — but you can retrieve no more than one database record at a time. With an SQL statement, you can retrieve multiple records in a single operation. Chapter 23 covers using SQL statements. To really handle SQL properly, though, you have to know more about it than this book has space to cover. For a thorough treatment, read SQL For Dummies (by yours truly, also from Wiley).

The SQL Expression Editor view of Formula Workshop looks exactly like the Formula Editor view of Formula Workshop except for the name in the title bar. You operate on it the same way, too. The only difference is that the expression that you build must adhere to legal SQL syntax. The SQL Expression Editor is an advanced feature; you probably won't use it until you have gained considerable experience with both Crystal Reports and with SQL.

Crystal Reports offers three kinds of selection formulas:

You can use formatting formulas to change various aspects of the format of a report. In this section, I show you how to change the heading color of the Customer Report, Grouped by State or District (USA) report (used in the previous section):

You can add or change the formatting of any aspect of a report in the same way. Do a little looking around in the Report Fields, Functions, and Operators panes of Format Formula Editor to get an idea of what's available.

Some data types are more complex than others. The simplest data types are number, currency, string, date, time, datetime, and Boolean. Range types and array types are more complex. Look at the simple types first.

42
-273
3.1415927
93000000.

$19.95
-$4000000000.
$64000

"I Left My Heart in San Francisco"
"$19.95"
'You can put "quoted text" within a string.'
'You can even include an apostrophe in a string''s text'

#July 20, 1969#
#20 Jul 1969 4:18 pm#
#7/20/1969 16:18:00#
#7/20/1969#
#4:18 pm#

Crystal Reports enables you to restrict the values of data elements to a specified range for all data types except Boolean. For example:

Arrays are ordered lists of values that are all the same type. In Crystal Reports, an array can contain data of a simple type or of a range type. Array elements are enclosed in square brackets, as in this example:

[2, 3, 5, 7, 11, 13]

This array contains the first six prime numbers. Or try planets:

["Mercury", "Venus", "Earth", "Mars"]

is an array containing the string values of the names of the terrestrial planets in our solar system.

You can subscript an array by specifying the index in square brackets after the array. (A subscript specifies a particular element of an array.) For example, the following use of brackets

[2, 3, 5, 7, 11, 13] [3]

specifies 5, the third element in the array.

You can also specify a range of elements, as follows:

["Mercury", "Venus", "Earth", "Mars"] [3 To 4]

This creates a new array, ["Earth", "Mars"].

Before you can use a variable, you must declare it to make Crystal Reports aware of it. When you declare a variable, you must specify three things: its name, its scope, and its data type. The name could be something simple, such as x or y. It could also be something more descriptive, such as topic.

When you declare a variable's data type, stick Var on the end of the type, as in StringVar or NumberVar. Scope may be local, global, or shared. If a variable is declared locally, it is valid only in the formula in which it is declared. If a variable is declared globally, it's available to all the formulas in a report that declare it (except for subreports).

A shared variable is available to all formulas in a report that declare it, including subreports. Subreports are covered in Chapter 11.

After you declare a variable, you can assign it a value. Here's an example:

//Declare topic1 to be a global variable of String type that
//specifies a book topic.
Global StringVar topic1;
topic1 := "Crystal Reports";

You can also declare a variable and assign it a value in a single statement, as follows:

Global StringVar topic2 := "SQL";

You can now use the variable in a formula.

The If-Then-Else control structure is useful when you want to do one thing if a condition is true and another thing if the condition is false. Suppose you want to give a 5-percent discount to customers who order more than $10,000 worth of products in a single order. Before printing their invoice, you could have Crystal Reports make the calculation for you as follows:

//Give 5% discount for orders > $10,000
If {Orders.Order Amount} > 10000.
Then
     {Orders.Order Amount} * 0.95
Else
     {Orders.Order Amount};

If the condition is satisfied, Order Amount is multiplied by 0.95, giving a 5-percent discount. Otherwise, Order Amount is unchanged. The change to Order Amount applies only to this report. The data in the database is not affected.

Use the Select Case control structure when you have more than two alternatives to choose from and you want to do a different thing in each case. Suppose the 5-percent discount you offered your customers last month resulted in a huge increase in sales, so you decide to expand the offer this month. Using a Select Case statement does the job:

//Give volume-based discounts
Select {Orders.Order Amount}
     Case 15000. To 1000000.:
         {Orders.Order Amount} * 0.93
     Case 12000. To 14999.99:
         {Orders.Order Amount} * 0.94
     Case 10000. To 11999.99:
         {Orders.Order Amount} * 0.95
     Default:
         {Orders.Order Amount};

If an order is between $15,000 and $1,000,000, a 7-percent discount is applied. Lesser discounts are applied for smaller orders. Below $10,000, no discount is applied. If an order comes through for more than $1,000,000, there must be a mistake, so no discount is applied. The Default clause is optional. If you omit it, the value of the selection condition is not changed. (It isn't changed in the preceding example either, but you avoid confusion by making it explicit.)

Like the If-Then-Else structure and the Select Case structure, using a For loop alters the flow of execution, but it alters it in a different way. Whereas using If-Then-Else and the Select Case constructs cause execution to take one path of execution rather than another, using a For loop causes execution to pass through a single piece of code multiple times.

A For loop is the best tool to use when you want to execute a section of code a predetermined number of times. Suppose you have a character field named Size in a table named Product, and you want to know how many instances of the letter x it contains. You can find out with a formula containing a For loop:

Local NumberVar Index;
Local NumberVar Xcount := 0;
Local NumberVar StringLength := Length ({Product.Size});

//loop through the characters in Size and count x's
For Index := 1 to StringLength Step 1 Do
(If ({Product.Size} [Index] = "x") Then
     (Xcount := Xcount + 1;)
 Else (Xcount := Xcount;)
);
Xcount

In the preceding example, Product.Size is treated as a string array, and Index is the subscript that points to each character in the array in turn. Execution steps through the Size field, one character at a time, counting the occurrences of x as it goes. If x occurs three times in the Size field, Xcount holds a 3. The last line in the formula returns the value of Xcount.

While Do loop

Whereas a For loop is designed for situations in which you know (or can compute) how many iterations of the loop you want to execute, using a While Do loop is ideal when you don't know the number of iterations. A While Do loop depends on the Boolean truth value of a condition. As long as the condition remains true, execution continues to loop. When the condition turns false, the current iteration of the loop is completed, and looping terminates. If the condition is initially false, the loop is not executed at all.

Suppose that in the preceding example, you wanted to know the character position of the first x rather than the total number of instances of x in the string. Because you don't know how far into the string the first x occurs (if at all), using a While Do loop is appropriate:

Local NumberVar Index := 1;
Local NumberVar Xpos := 0;
Local NumberVar StringLength := Length ({Product.Size});

//Find location of first x in Product.Size
While Index <= StringLength And Xpos = 0 Do
(If ({Product.Size} [Index] = "x") Then
     (Xpos := Index;)
 Else (Xpos := Xpos;)
Index := Index + 1;
);
Xpos

Note that if Index were initially greater than StringLength, the loop would be skipped.

A Do While loop is similar to the While Do loop, but whereas a While Do loop doesn't execute if the condition is not initially satisfied, a Do While loop is always guaranteed to execute at least once, regardless of whether the condition is satisfied. Sometimes you want the behavior of While Do, and other times you want the behavior of Do While. Crystal Reports gives you both.

With a Do While loop, you can accomplish the same character location task that was illustrated in the While Do loop example. The code is just a little bit different:

Local NumberVar Index := 1;
Local NumberVar Xpos := 0;
Local NumberVar StringLength := Length ({Product.Size});

//Find location of first x in Product.Size
Do
(If ({Product.Size} [Index] = "x") Then
     (Xpos := Index;)
 Else (Xpos := Xpos;)
Index := Index + 1;
While Index <= StringLength And Xpos = 0
);
Xpos

In this case, the loop is executed once, and the first character of Product.Size is checked to see whether it's an "x". This occurs even if the condition is not satisfied because execution doesn't reach the condition until after the loop has been executed once. Thus, if (by some mischance) the value of Index was greater than StringLength, an "x" located beyond the end of the Product.Size string would cause Xpos to take on a nonzero value. This could be misleading and cascade into a significant error.