Defining a Formula

Objectives

After completing this lesson, you will be able to:

  • Manipulate data using formulas

Formulas Overview

Create simple formulas to perform calculations. 

A formula is used to manipulate data in your report. For example, a formula can perform a calculation or change the field’s formatting. You can think of a formula as a small piece of computer programming code that processes and prints its result on your report, just like a database field. It will print the result wherever you place the formula field.

You can write formulas that are as simple or complex as you need them to be. Understanding the formula language helps you develop powerful and flexible formulas.

Typical uses for a formula include:

  • Calculating a percentage of a number

  • Extracting a single character from a string field

  • Combining a text string with a number or date field

  • Finding the difference between two date fields

  • Performing conditional logic

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.

Formula Components

ComponentExample
Fields{Customer.CustomerLastName}, {Customer.LastYearSales}
Numbers1, 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.
FunctionsTo 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 functionscdFirstDayofMonth, 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 valuesAverage (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:

Special Characters used in Crystal Syntax

CharacterDescription
//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 caseUpper 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 returnsCarriage 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.

Log in to track your progress & complete quizzes