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:
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.
| Component | Example |
|---|---|
| Fields | , |
| 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. |
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.
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:
When creating formulas:
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.
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. |
When writing formulas in Crystal Reports, it is important to know the data types of the fields you are working with:
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.