This is an excerpt from the book Crystal Reports Encyclopedia. Click to read more chapter excerpts.

Formula Fundamentals

To learn how to program with Basic syntax, you want to start with the fundamental tasks that might seem pretty mundane. There are certain aspects that you need to know even if you only plan on writing some very simple formulas. By understanding the basic structure of formula, you can build on this knowledge to write more complex logic. This section on the fundamentals of writing formulas covers Case Sensitivity, Writing Comments, Returning a Value, Using Data Fields, Declaring Variables, and Data Types.

Case Sensitivity

When a language is case sensitive, it considers identical words to be different when the letters use a different case. Basic syntax is not case sensitive. The variable FirstName is the same as the variable firstname. Although these two variables are syntactically equivalent and can be used interchangeably, it is recommended that you keep the case consistent so that your program is easier to read.

Crystal syntax IS case sensitive. When using variables in your code, you have to make sure that the case of each letter in the variable matches with how it was declared.

Returning a Value

Formulas are always used to return a value. The data types returned must be simple and can be Number, Currency, String, Boolean, Date, Time and DateTime. You cannot return complex data types such as range or array.

Formulas return a value by assigning the value to the Formula variable. Formulas must always return a value. The following code returns the value True.

Formula = True If a formula has multiple statements that return a value, all the assignments must be of the same data type. Although there is no way to specifically tell Crystal Reports the data type to return, the compiler will compare all the assignments and check them for consistency. As an example of what not to do, see the following example.

‘The following IS NOT VALID due to the different data types returned and produces an error
If Age > 65 Then
    Formula = “Retired”
Else
    Formula = Age
End If

To return a value with Crystal syntax, put the value on a line by itself. Nothing else should appear on the line. This can be as simple as assigning a value to a variable, or the result of a calculation. When a formula has multiple calculations in it, the last one in the formula is used. The following code returns True if the employee received a bonus. If not then False is returned.

If {Employee.Bonus} > 0 Then
    True
Else
    False

Referencing Report Fields

Writing formulas requires referencing all types of data from your report and the tables that the report uses. The types of data that can be referenced consist of running totals, functions, formulas, and table fields. The syntax for referencing a field is to put curly brackets around it. In addition to that, each type of field has a special character which designates its type. Some examples follow.

Formulas are referenced by putting @ in front of their name.

{@Formula}

Parameters are referenced with a ? in front of the name.

{?Parameter}

Running total fields are referenced by putting # in front of the name.

{#RunningTotal}

Table fields are referenced by separating the table name and the field name with a period between the two. Spaces are allowed.

{Customer.First Name}

Group fields use the field name with the GroupName() formula.

GroupName({Table.GroupField})

Summary fields pass the field name and the group field as parameters to the summary function.

Sum({Table.FieldName}, {Table.GroupName})

To read all my books online, click here for the Crystal Reports ebooks.