Setting the Foundation

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

The core concept in formula programming is that you write an equation that performs a calculation using data available through the report. Sometimes a formula involves math, sometimes it involves dates, and other times it involves processing text data. For instance, you can have the first name field and the last name field print on your report with combined labels of either “Name” or “Last Name, First Name” or “Last Name, First Name Initial” by processing text data with a formula.

A formula calculates a value and returns that value as a result to the report at the end of its processing. Some values are used to directly display values in a report, while other calculated values may be used as interim results needed in turn for other formulas. Formulas add meaning to a report that wasn’t a part of the natively stored data.

Components of a Formula

An example of a formula is a calculation to convert a Fahrenheit temperature to a Celsius temperature. Imagine that your data source contains the field of Celsius temperatures by day for the past month and that you’ve been asked to provide the average temperature for the month in Fahrenheit. Although the data does not exist in the data source, you can write a formula to calculate the value. There are two basic equations needed to calculate this result:

Fahrenheit = (Celsius * 9 / 5) + 32 Average = TotalTemperatures / NumberOfValues

Both formulas represent a calculation that will return a result. The first is a calculation performed for each new day; the second is a summary calculation that takes place after all values for the days of the month have been calculated. This example of direct single-record calculations and summary calculations is typical in report writing. Each part of a formula is identified by a component name; Table 4.1 identifies and describes the key components of a formula.

Table 4.1: Identifying Formula Components

Component Name	Formula Reference	Description
Variable	Fahrenheit, Celsius, Average, TotalValue, NumberOfValues	A variable is a placeholder whose value can change; a variable references a storage location in a computer’s memory.
Constant	9, 5, 32	A constant is a value that does not change; a constant also references a location in a computer’s memory.
Operator	=, *, /, +	An operator specifies the type of calculation to be performed on the values to either side of the operator. The names associated with the operators in the example are assignment operator (=), multiplication operator (*), division operator (/), and addition operator (+).

In Crystal, the “Last Name, First Initial” formula idea combines two text values from a database by concatenating them with a comma and a space (concatenate means to connect or link in a series). Code to do this would look something like the following:

"Name: " + {table.LastName} + ", " + {table.FirstName}

where "Name: " represents a constant (it never changes), {table.LastName} and {table.FirstName} represent variables (which change for each record in the database), and + is an operator (which operates on the variables on either side of it). This is a direct calculation that would execute for each record in a database; there is no summarization here. To do summarization, temporary values need to be stored in computer memory for further processing.

Computer Memory

Storing information in computer memory is analogous to storing things in post office boxes at your local post office. Inside a post office, there is generally a wall devoted to small, uniquely numbered boxes. Each of the boxes has a particular size and shape to accommodate small or large envelopes. You can store and retrieve items from a post office box by knowing the number on the box, and the storage is strictly temporary. Map your knowledge of the wall of post office boxes to computer memory.

A variable or a constant is like the number on the outside of the post office box. The post office box itself is like a memory location in a computer. Knowing the name (also known as the identifier) of a variable or a constant allows you to store and retrieve information from the memory location simply by using its name.

Most memory locations store a single value (also known as a discrete value), although some storage locations allow multiple values to be stored. Like a post office box, values in computer memory are temporary, and in terms of Crystal Reports, storage used by a report is recycled at several points and definitely when a report is closed.

A storage location in memory is also associated with a specific data type. Once associated with a data type, it can store only values of that type. You can think of the data type as the size and shape of the post office box; certain values will fit into the slot while others will not. The formula languages in Crystal Reports require that all variables and constants be associated with a data type, and, generically, they support the following types:

• Boolean values (True and False)

• Date and time values

• Numeric data

• Text (string) data

Later in this chapter we’ll look at the specifics of coding variables and associating them with data types in the two formula languages: Basic Syntax and Crystal Syntax.

Functions

Functions are code statements that are grouped together to perform a task and return a result. Crystal Reports supports both built-in functions and user-defined (custom) functions. A function is written as a generic routine that can be invoked over and over again by passing in new values each time the function is invoked; values passed to a function are known as arguments to the function. An argument is a value that exists only while the function is doing its processing; when the function ends, it returns a value to the report (or the calling function) and all computer memory associated with the function arguments goes away. A function has four parts:

• A function name

• A list of arguments (optional)

• The body of the function, which contains coded statements

• A return value

An example of a familiar function is the Sum function. The primary purpose of a function like Sum is to take several values, add them together, and return a single result. This describes the purpose of a function fairly well: Process values and return a result.

To invoke a function, you code a function call. You can invoke built-in functions or custom functions in Crystal. When one function calls another, which calls another, and so on, we say that we have a stack of function calls. Using this stack, we can trace back to all the previous code statements that were involved in generating a particular result. A function call to the function Sum might look like this:

myValue = Sum(x, y)

The identifiers myValue, x, and y are storage locations in memory. To calculate a value to store in myValue, the value of x is retrieved from its storage location, the value of y is retrieved from its storage location, the two values are added, and the result of the calculation is placed into the storage location identified by the variable myValue. X and Y are said to be arguments to the function Sum and represent the values to be used within the function.

Control Structures

Control structures in a programming language are used to directly influence the processing of information using logic. Control structures include the following:

• If-Then-Else single-selection constructs

• Multi-way selection constructs

• Loop constructs

• Keywords that interrupt normal processing

If-Then-Else constructs compare values and force a programmed action based on the result of the comparison. Multi-way selection statements (case or switch statements) compare multiple values and provide actions for each comparison. Loop constructs use a logical True or False comparison as a condition to trigger processing a set of code statements. Keywords like Break and Continue change the normal processing of a formula and force a change in the processing flow.

Arrays

An array is a storage structure that exists in memory and allows multiple values to be stored and referenced by a single identifier name. Although an array can store multiple values, all of the values must be of the same data type. As an example, a string array containing the values Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday might be known by the single name, DaysOfTheWeek. The elements in the array are all text (string) data and the array is said to have seven elements; Crystal contains a built-in function named UBound that will return the number of elements in an array. For example, UBound(DaysOfTheWeek) returns 7.

Setting and retrieving values for an array requires knowing the single name of the array as well as interacting with a particular slot or element number in the array. The DaysOfTheWeek array has seven slots, each of which can store a single value. Arrays can contain a maximum of 50 elements.

Warning 

Crystal Reports allows one-dimensional arrays only.

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

Conello © 2000-2003     Feedback