How to use a range in a formula

Extracting a range of values

A range is designed to conveniently handle entire spectrum of values, values that fall between a minimum and a maximum value.

For example, you would declare a number range variable as follows:

NumberVar Range GradeA;

A range variable is declared much like an array.

1.A variable declaration operator appropriate to the type of values stored by the range, NumberVar in this example.

2.The word Range follows the variable declaration operator.

3.The name of the range variable follows the word Range, Grade A in this example.

4.The Assignment operator follows the name and is used to assign the range of values the range variable will store.

Search for Assignment in Seagate Crystal Reports online Help.

The range is indicated using the Make Range operator with a minimum and a maximum value for the range.

Search for Make Range in Seagate Crystal Reports online Help.

Ranges have two principle uses:

●To extract a range of values from all possible values, and

●To extract a range of characters from a string value.

Consider the following example:

NumberVar Range GradeA := 90 to 100; NumberVar Range GradeB := 80 to 89; NumberVar Range GradeC := 70 to 79; NumberVar Range GradeD := 60 to 69;

Extracting a

range of characters

If {student.TEST SCORE} in GradeA Then “A”

Else If {student.TEST SCORE} in GradeB Then “B”

Else If {student.TEST SCORE} in GradeC Then “C”

Else If {student.TEST SCORE} in GradeD Then “D”

Else “F”

This formula starts by creating four range variables. Each contains a range of possible test scores. Notice that, unlike an array, no brackets are used to set off the range of values assigned to each range variable. Only the Make Range operator is used with the minimum and maximum test scores for each range.

The multiple If-Then-Else statement repeatedly evaluates the value in the {student.TEST SCORE} field to determine if it falls within a specific grade range. The formula prints letter grades in the report that are appropriate to the test scores received by each student.

See How to create multi-condition if-then-else formulas, Page 348.

The following formula demonstrates how to use ranges to extract characters from a string value:

StringVar AreaCode := {customer.FAX}[1 to 3];

If AreaCode = “604” Then “BC”

Else If AreaCode = “206” or AreaCode = “509”

or AreaCode = “360” Then “WA”

Else “”

This formula creates a variable that holds the first three characters in the string value of the {customer.FAX} field. For instance, if the value in {customer.FAX} is “6045551234”, then:

{customer.FAX}[1 to 3]

How to correct this problem

Speeding up the process

#Reference the formula (@Percent) in a group selection formula that selects only those groups for which the percentage (of subtotal to grand total) is less than 5% to find out which states individually contributed less than 5% of total sales:

{@Percent} < 5

When you click the Check or Accept button you will receive the following error message:

This formula cannot be used because it must be evaluated later.

The problem can be corrected easily. Instead of using the formula name (in this case @Percent) in the group selection formula, enter the formula itself (the formula named @Percent). Thus, instead of using the group selection formula:

{@percent} < 5

use the group selection formula:

Sum({orders.ORDER AMOUNT}, {customer.REGION}) % Sum({orders.ORDER AMOUNT}) < 5

Now when you print, only the states that contributed less than 5% will print.

To speed the process and minimize the chance for mistakes, you can copy the formula into the group selection formula using Windows' Copy and Paste commands.

Select the formula you want to use in the group selection formula.

Choose the FORMULA command from the Edit menu. The Formula Editor appears with the formula in the Formula text box.

!Copy the formula to the Clipboard using the Copy command (Ctrl-C).

" Click Accept to close the Formula Editor.

#Choose the GROUP command from the Report|Edit Selection Formula menu. The Formula Editor appears.