Coding Formulas | Mastering Crystal Reports 9

Chapter 4 - Adding Business Logic with the Formula Workshop
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

The purpose of creating a formula or writing code is to solve a problem. Now that you’ve seen where the code goes in Crystal Reports and you have some background on variables, constants, and operators, let’s take a look at how to program in the two formula languages built into Crystal Reports: Crystal Syntax and Basic Syntax.

Choosing a Language

Your choice of language will be determined by a few factors:

Your personal preference
Your experience with other programming languages

The existence of required language constructs for the problem at hand

Either language can be used. Neither language is case sensitive. You can code one function in Crystal Syntax and code a different one in Basic Syntax. As long as you don’t try to combine the two languages within one formula, you’ll be fine. Table 4.3 shows quick list of key syntactic point differences for the two languages.

Table 4.3: Crystal Syntax versus Basic Syntax
Task	Crystal Syntax	Basic Syntax
Assignment operator	:=	=
Comment operator	//	'
End of a statement	;	Carriage Return / Linefeed (Enter Key)
Data types	stringVar x; numberVar y; dateVar z;	Dim c as String Dim x as Number Dim y as Date
Variable scope	Local numberVar x; Global numberVar x; Shared numberVar x; (Global if not specified.)	Dim x as Number Local x as Number Global y as Number Shared z as Number (Local if not specified; [Dim and Local are equivalent.])
Array declaration	stringVar Array x; Redim x[2]; x[1]:="Hello"; x[2]:="World";	Shared x() as String x = Array("Hello", "World")
Returning a value to the report	Value	Formula = value
Closing keyword on block statements	No	Yes

Warning

The Variant data type supported in the Visual Basic language is not supported in Crystal’s Basic Syntax formula language.

Control Structures

Both languages support the use of variables, constants, and operators as well as standard programming language control structures such as these:

Condition-based statements
Iteration statements

Control structures are used to force the execution of an action based on conditional values. Figure 4.28 and Figure 4.29 show the Control Structures category of the Operator Tree for Crystal Syntax and Basic Syntax.

click to expand
Figure 4.28. Control Structures for Crystal Syntax

click to expand
Figure 4.29. Control Structures for Basic Syntax

Condition-Based Statements

Conditional statements test a condition and then take an appropriate action based on whether the condition is true or false. Conditions always return True or False, where a True result triggers an action and a False result either triggers no action or an alternate action. Parentheses are used in code statements to both isolate and prioritize the execution of a statement; by default, code statements execute in a left-to-right order within a statement except when parentheses are used and the statements within them are processed first.

If-Then-Else

The basic structure of an If-Then-Else statement is

If (condition) Then (action) Else (alternate action)

Conditions, actions, and alternate actions often involve fields from the report, built-in functions, and built-in operators. This is one of the most powerful structures in a programming language and it’s one you’ll use all the time in Crystal Reports. An If-Then-Else statement can simply be expressed as an If-Then statement; the Else (alternate action) is implied to be "do nothing." You can also nest If-Then-Else statements within one another as well as test multiple conditions within the condition clause by using logical AND and logical OR operators.

If (condition) then    (If (condition) then     action   else      alternate action) else   alternate action

Crystal Syntax and Basic Syntax use slightly different syntax for the If-Then-Else statement. Crystal Syntax does not use closing block keywords like End If for its executable statements, while Basic Syntax does use them. Also, values are returned to a report only through the use of the keyword Formula in Basic Syntax, while no keyword is required in Crystal Syntax.

Basic Syntax:

If {Resorts.StateProvince} = "New York" Then   Formula = "The Big Apple" Else   If {Resorts.StateProvince}="Rhode Island" Then     Formula = "The Ocean State" End If

Crystal Syntax:

If {Resorts.StateProvince} = "New York" Then   "The Big Apple" Else   If {Resorts.StateProvince}="Rhode Island" Then     "The Ocean State"

Both Basic Syntax and Crystal Syntax also support the shorthand IIF notation for the full If-Then-Else statement:

IIF(condition, action, alternate action)

Condition Only

In several areas in Crystal Reports, you’ll have occasion to simply use the condition part of the If-Then-Else statement; the context of where the statement is located determines the action. Here are examples of using pure conditions without the Then-Else components in formula fields and selection formulas. The formatting formulas show the use of an If-Then example.

Formula Field A formula field is given a name and can then be placed into any section on a report. If you want one column of data to represent the last name separated by a comma and a space from the first name, you would create a formula similar to the following formula, making sure to use the appropriate table name and field names:

{Owner.LastName} + ", " + {Owner.FirstName}

Selection Formula A selection formula needs only to use a condition; when the condition is met (meaning it is true), the records selected from the database meet the criteria defined by the condition. A condition clause can contain multiple conditions as long as it is connected with the logical AND operator or the logical OR operator. The following condition looks for data in the VistaNations Owners table and returns a list of resorts owned by Cate McCoy:

{Owner.LastName}=McCoy" and {Owner.FirstName}="Cate"

Formatting Formulas Formatting formulas change the way information looks or behaves in a report. Figure 4.8, shown earlier in this chapter, showed how to create a formatting formula using the Workshop Tree. If you selected the Details section and the Background Color formatting option, the Workshop Tree and Formula Editor would appear as in Figure 4.30 and allow you to type a formatting formula. In this example, we’ve coded a Crystal Syntax formula that formats the background to be the color green if the value of Country = "US" and white otherwise.

click to expand
Figure 4.30. Using a formula to format a background color

Multi-Way Selection

Both Crystal Syntax and Basic Syntax provide for a clear, concise statement for multi-way selection called the Select statement. Instead of nesting one if statement inside another, it is usually easier to write, read, and maintain code if you use a Select conditional statement instead. In the following two code examples, the value of the variable lastTwo is inspected. If its value equals 01, the value of the variable suffix is set to st, and the other statements do similar assignments. The beauty of the Select statement is that when the first true condition is encountered, the statement associated with it executes and then remaining conditions are not executed; control passes to the end of the statement. Notice the following differences about the code:

Basic Syntax closes the code block with an End Select statement, while Crystal Syntax does not.
Crystal Syntax ends the statement with a semicolon, while Basic Syntax does not.

The assignment operators for the two languages are different.

Basic Syntax:

Select Case (lastTwo)   Case "01"      Suffix = "st"   Case "02"     Suffix = "nd"   Case "03"     Suffix = "rd"   Case Else     Suffix = "th" End Select

Crystal Syntax:

Select lastTwo   Case "01" :     Suffix := "st"   Case "02":     Suffix := "nd"   Case "03":     Suffix := "rd"   Default:     Suffix := "th";

We’ll make use of this code in just a bit when we build a custom function.

Iteration (Loops)

A loop is a programming statement that moves you through a set of data one element at a time and takes an action that applies to the element. You can use a loop to do such things as create a count of records as they’re being read from the database, apply functions to a record in the database, and compare one value to another, taking an action based on the comparison. The concept of a loop is that you test a condition, and if the condition is true, an action is taken; the process of testing and taking an action continues, repeating the code, until the condition proves to be false. A loop has three parts:

Initial value of a loop control variable
Condition to test to determine if the loop should continue or stop
Increment of a loop control variable

A loop essentially allows a program to repeat pieces of the code. Figure 4.31 depicts this process.

click to expand
Figure 4.31. How a loop works

Crystal Reports provides for several types of loops in each of the formula languages. Loops take the following structures:

FOR loops Process a specified number of times.

WHILE loops Test a condition and process it if it is true.

UNTIL loops Process and then test a condition to see if it is true.

Crystal Syntax and Basic Syntax both support the spirit of these three types of loops with slightly varying syntax:

Crystal Syntax:

For I := 1 to 100 Do   (     fiveStarCount = fiveStarCount + 1;   ); While ({Resorts.FiveStarRating = "Yes"}) Do   (     fiveStarCount = fiveStarCount + 1;   ); Do    (     fiveStarCount = fiveStarCount + 1;   ) While ({Resorts.FiveStarRating = "Yes"});

Basic Syntax:

For I = 1 to 100    If {Resorts.FiveStarRating = "Yes"} then     fiveStarCount = fiveStarCount + 1 Next I  Do While ({Resorts.FiveStarRating = "Yes"})   fiveStarCount = fiveStarCount + 1 Loop Do Until ({Resorts.FiveStarRating = "Yes"})   fiveStarCount = fiveStarCount + 1 Loop Do    fiveStarCount = fiveStarCount + 1 Loop While ({Resorts.FiveStarRating = "Yes"}) Do    fiveStarCount = fiveStarCount + 1 Loop Until ({Resorts.FiveStarRating = "Yes"})

Note

The Exit Do statement immediately exits the loop at whatever point it is placed.


Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003 Feedback