Crystal Reports 10: The Complete Reference

Probably the most common type of formula is a number formula, such as the Extended Price formula discussed earlier. Number formulas can be as simple as multiplying a database field by 1.1 to increase its amount by 10 percent, or as complex as calculations that include sophisticated statistical math. There is no special procedure required to declare a formula as a number formula ”the formula simply takes on that data type because of the fields and operators that you use in the formula. As the Extended Price formula, shown here, demonstrates , multiplying a number field by a currency field results in a currency formula:

{Orders Detail.Unit Price} * {Orders Detail.Quantity}

Many number formulas will use a mathematical operator, such as a plus sign, a hyphen or minus sign, an asterisk for multiplication, or a slash for division. You also need to use built-in functions that Crystal Reports supplies or custom functions that you create yourself or that others created for you. You ll find all these functions listed in the Function Tree box (you may need to add a custom function to your report from the repository first ”see Chapter 7 for more information). When you double-click a function, the function name is placed in the Formula text box with the cursor located in between the opening and closing parentheses. You can then either type in the function s arguments or parameters, or double-click other fields or formulas in the Field Tree box to add them as arguments to the function.

For example, if you have a group on the report and want to include a group subtotal in a formula, you would use the Sum function. You ll find that three Sum functions actually are available, with one, two, or three arguments. Here are some examples:

Sum({Orders.Order Amount})

returns a total of all order amounts for the entire report.

Sum({Orders.Order Amount},{Customer.Region})

returns a total of just the order amounts in the region group where the formula is evaluated. If the formula was evaluated in the Colorado group, the formula returns the order amount subtotal for Colorado only.

Sum({Orders.Order Amount},{Orders.Order Date}, "weekly")

returns the order amount subtotal for the current order date group, calculating the subtotal based on a week of orders. Note that this third argument corresponds to the time periods that are available when creating a group based on a date field. (Refresh your memory about date-field grouping by looking at Chapter 3.)

So, you could calculate each order amount s percentage of the region subtotal by using the percentage operator and the Sum function as follows :

{Orders.Order Amount} % Sum({Orders.Order Amount},{Customer.Region})

There are built-in functions to calculate all the summary-type information discussed in Chapter 3, such as average, subtotal, P th percentile, and on and on. By opening the Arithmetic category of functions in the Function Tree box, you ll also find functions to calculate remainders, determine absolute value, and round numbers .

When you use Crystal syntax, the formula simply returns the results of the last statement in the formula. If the formula consists of only one statement, such as the multiplication in the previous example, the formula returns the results of the multiplication. If the formula contains several statements separated by semicolons, the last statement determines what's returned to the report.

But if you are using Basic syntax, you must keep in mind one slight difference from any Basic-like programming languages you've used. In a Basic computer language, you typically assign and manipulate variables throughout your code. When you wish to display the value of a variable, you use a Print or ? statement or set the value of a text box or other form element to the value of the variable. Because Crystal Reports has no Print statement, you need an alternative method of displaying a value on the report. This is accomplished with the Formula variable.

The word Formula is a reserved word in Basic syntax ”you can't use it for any other purpose, such as using it as your own variable name with a Dim statement. By assigning a value to the Formula variable, you determine what the formula returns to the report. You can use the Formula variable over and over within a formula, just like any other variable (as an accumulator , for example). The last occurrence in the formula where a value is assigned to the Formula variable determines what the formula returns to the report. Consider the following Basic syntax formula:

' Calculates extended price Formula = {Orders Detail.Unit Price} * {Orders Detail.Quantity} If {Customer.Region} = "CO" Then ' add 4.25% sales tax to Colorado orders Formula = Formula * 1.0425 End If

Here, the Formula variable is used like a regular variable (it doesn't even have to be declared with a Dim statement first). It's first used just to calculate the extended price. Then, it's included in an If statement to add sales tax for Colorado orders. If the If statement is true, the existing value of the Formula variable is multiplied by 1.0425 to add 4.25 percent. If the If test fails, the last statement that assigns a value to the Formula variable (the extended price calculation) will be what's returned to the report.

Order of Precedence

You ll sometimes find situations where you re unsure of the order in which Crystal Reports evaluates a formula s operators. For example, if you wish to add sales tax to an extended price, you might use the following formula, which is supposed to add 8 percent sales tax to the extended price of an order (already calculated in the @Extended Price formula):

{@Extended Price} + {@Extended Price} * .08

The question of how Crystal Reports calculates this is crucial. Does it calculate the addition operator first and then the multiplication operator, or does it calculate the multiplication operator first and then the addition operator? The results will vary dramatically based on the calculation order. Consider an Extended Price of $100 with addition performed first:

100 + 100 = 200 200 * .08 = 16.00

or with multiplication performed first:

100 * .08 = 8.00 100 + 8.00 = 108.00

While your customer might be very pleasantly surprised by the first calculation showing up on their invoice, the second calculation is certainly the correct one. But looking at the formula, you ll notice that the multiplication operator is the second operator. Will it be evaluated second?

The answer is no, based on the order of precedence. Although it may sound like a computer concept, order of precedence is actually a concept that you should recall from your ninth grade math class. In this formula, multiplication and division are evaluated first from left to right across the formula; then, addition and subtraction are evaluated from left to right across the formula.

The order of precedence for both Crystal syntax and Basic syntax is as follows:

• Exponentiation (^)

• Negation ( “)

• Multiplication, division, left to right (*, /)

• For Crystal syntax only, percent (%) is evaluated at the same time as multiplication and division

• Integer division (\)

• Modulus (Mod)

• Addition and subtraction, left to right (+, “)

Based on this, the formula to add tax to the Extended Price shown earlier will work just fine. But what if, for some reason, you want the addition performed first, not the multiplication. Again, thinking back to ninth-grade math, you surround the part of the formula you want evaluated first with parentheses. The following formula will perform the addition before the multiplication:

({@Extended Price} + {@Extended Price}) * .08