Defining a Formula

Objective

After completing this lesson, you will be able to manipulate data using formulas.

Formulas Overview

See the following scenario to create simple formulas to perform calculations.

In a bright, modern atrium with floor-to-ceiling windows, two colleagues sit facing each other on plastic chairs, leaning in over colored folders and papers and gesturing as they engage in a focused, collaborative discussion—one asks about creating additional calculations or data manipulation options while the other explains that the Formula Workshop in Crystal Reports can create simple calculations or manipulate string data to answer business questions.

A formula is used to manipulate data in your report.

Watch this video to learn more about formula workshop.

Formula Components

Formulas contain two critical parts: the components and the syntax. The components are the pieces that you add to create a formula while the syntax is the rules that you follow to organize the components.

A Crystal Reports Formula Workshop window shows a three‑pane editor (report fields on the left, function categories in the center, operators on the right) and a toolbar at the top, with a highlighted formula in the lower code area that concatenates the contact's first name entry and the uppercase last name ({Customer.Contact First Name}[1] + + UpperCase({Customer.Contact Last Name})), presenting a focused, technical interface for data formatting.

Formula Components

Component	Example
Fields	{Customer.CustomerLastName}, {Customer.LastYearSales}
Numbers	1, 2, 3.1416
Text	"Quantity", ".", "your text"
Operators	+ (add), / (divide), —x (negate) Operators are special symbols or words that describe an operation or an action to take place between two or more values. Operators are used in formulas. The program reads the operators in a formula and performs the actions specified.
Functions	To Text(x), ToNumber(x) Functions are built-in procedures or subroutines used to evaluate, make calculations on, or transform data. Functions perform calculations such as average, sum, and count. All functions available are listed with their arguments and are arranged by their use. When you specify a function, the program performs the set of operations built into the function without you having to specify each operation separately. In this way, a function is a kind of shorthand that makes it easier and less time consuming for you to create reports.
Custom functions	cdFirstDayofMonth, cdStatutoryHolidays Custom functions provide a way to share and reuse a formula logic. They can be stored in the BusinessObjects Enterprise Repository and then added to a report. Once in the report, custom functions can be used in the Formula Expert when creating formulas.
Control structures	"If" and "Select", "For loops, Control structures control the flow of logic in a formula. You can use them to build formula setting conditions that, if met, trigger specific consequences or repeat a sequence of actions under certain conditions.
Group field values	Average (fld, condFld), Sum (fld, condFld, "condition") Group field values summarize a group. For example, you could use group field values to find the percentage of the grand total contributed by each group.

Hint

When you select a function or operator in the appropriate tree, or when you select a function or operator that is used in the body of your formula, you can press F1 to see help specific to that function or operator.

Crystal Syntax

You write formulas according to a specific set of rules called syntax. Crystal Reports supports both Crystal syntax and Basic syntax. This course will only use Crystal syntax. The Formula Editor checks the syntax and helps identify problems so you can fix them before you add the formula to the report.

Syntax rules are used to create a correct formula. Some of the rules are:

Strings are used to hold text that must be placed between double quotation marks (") or apostrophes (').
Functions may or may not require arguments and/or parentheses.
Formulas in the report are identified with a leading @ symbol.

When creating formulas:

You have the option of using either Crystal syntax or Basic syntax.
Almost any formula written with one syntax can be written with the other.
You cannot mix and match Crystal and Basic syntax within a single formula.
You can have a mix and match of Crystal and Basic syntax formulas within the same report.

If you are already familiar with Crystal syntax, you can continue to use it and benefit from the wide range of functions, operators, and control structures.

If you are familiar with Microsoft Visual Basic or other versions of Basic, then working in the Basic syntax in Crystal Reports may be more comfortable for you. In general, Basic syntax is modeled on Visual Basic except that it has specific extensions to handle reporting.

Report processing is not slowed down by using Basic syntax. Reports using Basic syntax formulas can run on any machine that Crystal Reports runs on. Also, using Basic syntax formulas does not require distributing any additional files with your reports.

Changing the syntax from Crystal syntax to Basic syntax or going from Basic syntax to Crystal syntax changes the list of functions in the Functions window as well as the list of operators in the Operators window. However, the available report fields remain the same since the report fields are available to both syntax.

Special Characters Used in Crystal Syntax

The following table displays the special characters used in Crystal syntax:

Character	Description
//	Denotes that everything following is a comment and is ignored by the //Formula Editor. You must repeat the // for each line that you want to comment out.
( )	Denotes the arguments that follow a function. Many functions require more than one argument separated by a comma within the parentheses (also known as round brackets). They can also denote precedence, forcing parts of your formula between the ( ) to evaluate first.
{ }	Denotes fields. All types of fields, such as database, other formulas, and special, are enclosed in braces (also known as French braces or curly brackets).
[ ]	Denotes subscripts or arrays. Brackets (also known as square brackets) before a function denote [ ] an array, whereas brackets after a function indicate a subscript (returns specific characters from a string).
" "	Denotes literals. Any text between the quotes is printed as it appears between the quotes. This functionality is useful when you want punctuation, text, or special characters to appear as part of the result. Single quotation marks (' ') can also be used for this purpose.
Upper/Lower case	Upper or lower case is ignored by the Formula Editor. You can enter your functions in all caps, all lower, or any mix you prefer.
Carriage returns	Carriage returns are ignored in the Formula Editor. You can have line breaks and blank lines almost anywhere in the formula.

Data Types

When writing formulas in Crystal Reports, it is important to know the data types of the fields you are working with:

Boolean
String
Date
Time
DateTime
Number
Currency

All fields will be recognized as one of these data types. You must always be aware of the field data type because this can affect which functions can be used with the field.

Summary

A formula in reporting processes data to produce outcomes, varying from basic to sophisticated uses like calculations, text adjustments, and conditional logic.
Formulas are composed of elements such as fields, numbers, text, operators, and functions, organized according to specific syntax rules.
Crystal Reports offers both Crystal and Basic syntax for formulas, with each requiring an understanding of data types to write effectively.

Next lesson