Crystal Reports 10: The Complete Reference
If after giving your new formula a name , you click the Use Editor button, the Formula Editor will appear inside the Formula Workshop, as shown in Figure 5-3. The Formula Editor appears within the Formula Workshop. It may look a little foreboding at first, but don t worry ”it will soon become second nature to you as you create and edit more formulas.
| Tip | If you wish to customize the font face, size , colors, and other appearance options that the Formula Editor uses, select File Options on the pull-down menus and make your choices on the Editors tab of the dialog box that opens. |
Before you actually create a formula, familiarize yourself with the layout of the Formula Editor. Notice that the Field Tree, Function Tree, and Operator Tree boxes can be closed, resized, moved, and undocked (detached from the main window and put in their own windows ). You have a great deal of flexibility in customizing the way the Formula Editor looks. You may also undock the Formula Editor toolbar (the second toolbar at the top of the Formula Workshop) and move it to another location on the screen.
| Tip | If you mistakenly undock and then close the toolbar, you can get it back by closing and re-opening the Formula Workshop. The toolbar will then reappear. |
When you re working with the Formula Editor, you ll want to familiarize yourself with the Formula Editor toolbar (titled the Expression Editor toolbar if you undock it), because you ll need to use it on a regular basis. There are buttons to check for proper formula syntax; undo and redo editing changes; hide and show the field, function, and operator trees; and so forth. If you re unfamiliar with a toolbar button s function, point to it with your mouse and wait a second or two ”a tool tip for that button will appear. Table 5-2 shows the functions of the Formula Editor s toolbar buttons .
| Button/Key Combo | Name | Function |
|---|---|---|
| or alt-c | Check | Checks the syntax of the formula and reports any errors. |
| or ctrl-z | Undo | Undoes the latest editing or typing. |
| or ctrl-Y | Redo | Redoes the latest editing or typing. |
| or alt-b | Browse Data | Displays sample data from the database for the selected database field. This button will work only when you've highlighted a database field, not a custom function. |
| or ctrl-f | Find/Replace | Allows searching and replacing for specific characters in the current formula. |
| or ctrl-F2 | Toggle Bookmark | Places a bookmark at the current line of formula text. If a bookmark is already there, removes it. |
| or ctrl-alt-F2 | Next Bookmark | Moves the cursor to the next bookmark in the current formula. |
| or shift - F2 | Previous Bookmark | Moves the cursor to the previous bookmark in the current formula. |
| or ctrl-shift - F2 | Clear All Bookmarks | Removes all bookmarks from the current formula. |
| or alt-o | Sort Trees | Sorts the contents of the three Tree boxes alphabetically , instead of in the default logical order. |
| or alt-f | Field Tree | Displays/hides the Field Tree box. |
| or alt-u | Function Tree | Displays/hides the Function Tree box. |
| or alt-p | Operator Tree | Displays/hides the Operator Tree box. |
| or ctrl-t | Syntax | Chooses syntax (Crystal or Basic) to use for this formula only. |
| or alt-m | Comment/Uncomment | Adds comment characters (two slashes for Crystal Syntax, an apostrophe for Basic Syntax) to all formula lines that are highlighted. If the lines already are commented, this button removes the comment characters. Lines that are preceded with comment characters are ignored by the formula. |
| Tip | There are many other shortcut keys you can use while in the Formula Editor. Search Crystal Reports online Help for Key controls for Formula Editor. |
There are two general approaches to building a formula: type in the parts of the formula directly or double-click in the tree boxes. Once you become more familiar with the Crystal Reports formula language, you will probably create at least some parts of your formula by typing the formula text right into the Formula text box at the bottom of the Formula Editor. For example, simply typing an asterisk when you want to multiply numbers often is easier than clicking around in the Operator Tree box to find the multiplication operator.
Other parts of your formula, however, are best created automatically by double-clicking elements in one of the three tree boxes. For example, to include a database field as part of your formula, just find the field you want to include in the Field Tree box and double-click it. The field will be placed at the cursor position in the Formula text box, using proper formula language syntax.
Using the trees is easy. Simply find the general area of the tree that you are interested in and click the plus sign next to the category that you want to use. All the functions or operators within that category will appear. Double-click the one you want to use and it will be placed at the cursor position in the Formula text box. If you click a function that requires arguments (or parameters), such as an UpperCase function that needs to know what you want to convert to uppercase, the function name and parentheses will be placed in the formula with the cursor positioned at the location of the first argument. You can either type it in or move it to another tree (the field tree, for example) and double-click the field you want to add as the argument. After a while, you ll be able to find the functions or operators you re looking for quickly and create fairly large formulas simply by pointing and double-clicking.
Notice the syntax that Crystal Reports uses when it places objects in the Formula text box (the small formula illustrated in Figure 5-3 is a good example). If you decide to type material into the formula yourself, you ll need to adhere to proper formula language syntax. Table 5-3 identifies special characters and other syntactical requirements of the formula language.
| Crystal Syntax | Basic Syntax | Uses |
|---|---|---|
| . (period) | . (period) | Used to separate the table name from the field name when using a database field. You must always include the table name, a period, and the field name ”the field name by itself is not sufficient. |
| {} ( curly or French braces) | {} ( curly or French braces) | Used to surround database fields, other formula names , and parameter fields. The formula won't understand fields if they're not surrounded by curly braces. |
| // (two slashes) | ˜ (apostrophe) or Rem | Denotes a comment. These can be used at the beginning of a line in a formula, in which case the Formula Editor ignores the whole line. You can also place two slashes or an apostrophe anywhere in a formula line, in which case the rest of the line will be ignored. You may use the Formula Editor Comment/Uncomment toolbar button to add these characters to multiple selected lines at one time. |
| or ˜ ˜ (quotation marks or apostrophes ) | (quotation marks) | Used to surround string or text literals (fixed-string characters) in formulas. With Crystal syntax, you can use either option, as long as they're used in matched pairs. For example: If {Customer.Country} = "USA" Then "United States" Else 'International' If you are using Basic syntax, you must use quotation marks only ”an apostrophe will be interpreted as a comment. |
| ( ) (parentheses) | ( ) (parentheses) | Used to force certain parts of formulas to be evaluated first, as in the following: ({Orders.Amount} + {Orders.Amount}) * {@Tax Rate} Also used to denote arguments or "parameters" of built-in functions, as in this example: UpperCase({Customer.Customer Name}) |
| @@ ? # % | @@ ? # % | Crystal Reports automatically precedes certain fields with these characters. The @ sign precedes formulas, ? precedes parameter fields, # precedes running total fields, and % precedes SQL expression fields. When including these types of fields in a formula, make sure you surround the field with curly braces. |
| # ( pound sign) | # (pound sign) | If you don't include curly braces around a pound sign, Crystal Reports will expect a value appearing between two pound signs that can be converted to a date/time value, as in this example: #2/10/2000 1:15 pm# |
| , (comma) | , (comma) | Used to separate multiple arguments in functions. For example: ToText({Orders.Amount},0," ") Don't add a comma as a thousands separator when using a numeric constant in a formula. For example: {Orders.Amount} + 2,500 will cause a syntax error. |
| ; (semicolon) | : ( colon ) | If your formula contains multiple statements, you must separate them with a semicolon in Crystal syntax ( putting statements on separate lines without a semicolon will still cause a syntax error). With Basic syntax, you must either put each statement on a separate line or separate multiple statements on the same line with a colon. Note that in Basic syntax, some statements (such as those making up For loops ) must be placed on separate lines ”separating these statements with a colon will still cause a syntax error. |
| enter key | _ (space followed by underscore ) | In Crystal syntax, you may press enter to start a new line in your formula anywhere between a field or function and an operator (don't put new lines or spaces in the middle of field or function names). Long formulas are more readable on multiple lines. For example: If {Order.Amount} > 5000 Then "Qualifies for bonus" Else "Not eligible for bonus"
In Basic syntax, you may press enter only at the end of a complete statement. If you want a line break in the middle of a statement, you must use a line continuation sequence (a space, followed by the underscore). For example: If {Order.Amount} > 5000 Then Formula = _ "Qualifies for bonus" Else Formula = _ "Not eligible for bonus" End If |
| := (colon followed by equal sign) | = (equal sign) | Used for variable assignment, such as: NumberVar Quota := 1 In Crystal syntax, don't confuse this with the equal sign alone, which is used for comparison: If {Customer.Region} = "BC" Then "Canadian Customer"
In Basic syntax, the equal sign is used for both comparison and assignment, as in: If {Customer.Region} _ = "BC" Then Formula = "Canadian Customer" End If |
| |
The syntax you use for individual formulas can be chosen with the syntax drop-down list, located at the far-right end of the Formula Editor toolbar. When you choose the desired syntax, you'll notice that the function and operator trees will change, showing all the built-in functions and operators for the chosen syntax. When you check the formula with the Check button, the formula must conform to the syntax chosen in the drop-down list. If, for example, you create a formula that is correct for Crystal syntax and then choose Basic syntax in the drop-down list, you'll probably get an error message if you check the formula. Crystal Reports will not automatically convert from one syntax to the other when you change the syntax drop-down value in an already existing formula.
The choice of syntax is largely one of personal preference. If you are a Basic programmer who often encounters syntax errors with Crystal syntax because you instinctively use Basic, you'll probably be pleased with Basic syntax. If you've used previous versions of Crystal Reports and aren't a Basic programmer, you'll most likely want to continue to use Crystal syntax because you're familiar with it. You don't have to do this at the expense of flexibility, either ”virtually all of the Basic-like constructs in Basic syntax are also available in Crystal syntax. You can always choose which syntax to use in each formula that you create (the notable exceptions being record- and group -selection formulas ”these can use only Crystal syntax, and you aren't given a choice). You may choose the default syntax for all new formulas by choosing File Options and clicking the Reporting tab. At the bottom of the dialog box is a Formula Language drop-down list that you use to set the default for all new formulas.
The remainder of this chapter focuses largely on Crystal syntax, showing most examples in the Crystal Reports' original formula language. This choice has been made for two reasons:
-
If you are using previous versions of Crystal Reports, most of this chapter will still apply to you.
-
The Basic language is well documented in many other texts . Since this book is specific to Crystal Reports, language syntax that is specific to Crystal Reports is best documented here.
Any examples or issues that are specific to Basic syntax will be so noted.
| |
Crystal Reports Formula Auto Complete
If you develop computer programs in certain programming languages, or develop web pages with certain tools, you may be familiar with auto-completion, which will often anticipate what you are typing and complete portions of your code for you. Crystal Reports features a limited form of this technology as part of the Formula Editor.
In addition to double-clicking functions in the group tree, you may begin typing portions of your formula directly in the formula text area of the Formula Editor. If you d like to choose from a list of possible formula functions you can use, press CTRL-SPACE. A list will appear with possible functions you can use.
The list of functions you see in the drop-down list will be based on what you ve typed so far before pressing CTRL-SPACE ”if you ve typed enough letters to narrow down the list of available functions to a small group, only the few that fit will appear. If you type CTRL-SPACE with no characters typed beforehand, the whole list of Crystal Reports functions will appear in the drop-down list. And, if you have typed enough characters to narrow the available functions to just one, the drop-down list won t be displayed at all ”the complete function name will just be chosen for you.
To choose a function from the drop-down list, use the DOWN ARROW key to choose the desired function and press SPACE or ENTER, or click on the desired function with your mouse. You may also just continue to type, narrowing down the list of available functions as you go. When you get to a single unique choice, the completed function name will be typed for you.
Data Types
As you begin to work with formulas, it s very important to understand the concept of data types. Every database field has a certain data type, and every formula you create will result in a single data type. These concepts are important, because if the formula you create doesn t deal with data types properly, you ll get errors when you try to save the formula, or the formula won t give you the result you re looking for. You can t, for example, add the contents of a number field to the contents of a string field with a plus sign ”both fields have to be numbers. You can t convert a date field to uppercase characters, because only a string field can be converted to uppercase.
By default, Crystal Reports doesn t display objects in the Design tab by their data types. It shows their names instead. You may prefer to see the data-type representation instead of the field name. To do this, choose File Options from the pull-down menus and turn off the Show Field Names check box in the Field Options section of the Layout tab. Notice the difference between showing field names and showing data types:
You may want to turn off Show Field Names when you first start working with formulas. Seeing the data types can help you determine the types of operators and functions that will work with the database fields you re including in your formulas. Also, whenever you browse database fields in the Formula Editor, the data type shows up in the Browse dialog box. In the preceding illustration, the fields have the following data types:
-
Order ID A number data type, which can contain only numbers (along with a period to indicate a decimal point, and a hyphen or minus sign if it s a negative number). You can add, subtract, multiply, divide, and perform other math operations on number data types.
-
Order Amount A currency data type (available only from certain databases). This is similar to a number data type, but it avoids rounding errors that sometimes occur when performing math operations on number data types.
-
Order Date A combined date/time data type (again, supported only by certain databases). This can contain a date, a time, or a combination of both. Other databases have date-only data types, and some have time-only data types.
-
Courier Website A memo data type. The memo data type, like another type called string, allows any combination of characters to be placed in the field. However, because letters and punctuation marks can reside in the field, you normally can t perform mathematical calculations with the field.
You may encounter other data types in your databases that aren t shown in this example:
-
Boolean Represents data that can have only a true or false value.
-
BLOB Designed to contain photos, graphics, or large amounts of plain ASCII text.
Caution BLOB (Binary Large Object) fields can be placed on the report only for display. They cannot be used or manipulated inside formulas. They won t even show up in the Formula Editor Field Tree box.
You may also show data types alongside field names in the Field Explorer. Just select the Database Fields category, a table name, or an individual database field and then right-click. Choose the Show Field Type option from the resulting pop-up menu (the option will then show a check mark next to it). The data type will then appear next to each field name in any database table.
Creating a New Formula
Creating a simple math calculation is easy. Using the Orders Detail table of the sample XTREME.MDB database included with Crystal Reports, you can calculate the extended price of each order-line item with the following formula.
To create this formula, follow these steps:
-
Create a new report using the XTREME.MDB Microsoft Access sample database included with Crystal Reports (you can use the XTREME Sample Database ODBC data source if you choose). Choose the Orders Detail table from this database.
-
Select the Formula Fields category and then click the New button in the Field Explorer toolbar. Or, you may launch the Formula Workshop and create the new formula from there.
-
When asked to name the formula, call it Extended Price , and click the Use Editor button.
-
When the Formula Editor appears, double-click the Orders Detail.Unit Price report field in the Field Tree box (you may need to click the plus sign next to the database name to be able to see the Orders Detail table) to add it to the Formula text box. If the field you want to add has already been placed on the report, you will actually find it in the field tree under both the Report Fields section and the database name ”there is absolutely no difference if you choose the field from either area.
-
Click the plus sign next to Arithmetic in the Operator Tree box to see all the arithmetic operators that are available. Double-click the multiply operator. (You can save yourself some mouse clicks by typing an asterisk directly in the Formula text box, if you d like. Although you don t have to put a space before or after the asterisk, the formula is easier to read if you do.)
-
Double-click the Orders Detail.Quantity field in the Field Tree box to place it after the asterisk.
-
If you wish to add a comment explaining the use of the formula (for your own information, or perhaps for others who may be working with your report later), you may precede the comment lines with two slashes, or just type the comment lines, highlight them with your mouse, and click the Comment button in the Formula Editor toolbar.
After you finish the formula, you have several ways to save it and close the Formula Editor and the Formula Workshop. When you first start to use formulas, you ll probably want to check for correct syntax of the formula before you save it. This will ensure that Crystal Reports can at least understand the different parts of the formula and how they are supposed to be calculated or manipulated. Check the formula s syntax by clicking the Check button in the Formula Editor toolbar, or press ALT-C. If Crystal Reports can understand all parts of the formula, a dialog box will appear indicating that no errors were found. (If you ve ever written computer programs or used spreadsheet formulas before, though, you know that correct syntax doesn t guarantee that the formula will return the right answer!)
If there is a syntax error in the formula, Crystal Reports will display an error message and highlight the portion of the formula where it stopped understanding it. As you can see here, sometimes these messages may be very descriptive:
or they can be very cryptic:
even though both of these examples result from simply forgetting curly braces. You ll learn over time what most error messages indicate and how to resolve them.
| Caution | Even though you may not get any syntax errors when you first check the formula after you create it, you may still get an error when the report runs, depending on the actual data in the database. This may happen, for example, if you create a formula that divides two fields, but the divided by field returns a zero during a certain record. You ll then get a Can t Divide by Zero syntax error in the middle of the report process. If there s a chance that this type of error may occur given particular data, you ll probably want to add some type of If {field} > 0 Then logic to ensure these types of run-time syntax errors won t occur. |
After you determine that there are no syntax errors, it s time to save the formula. You may either save the formula and remain in the Formula Workshop or save the formula and close the Formula Workshop by using the appropriate buttons in the Formula Workshop toolbar. If you try to create a new formula or edit another formula without first saving the current formula, you ll be asked if you want to save the current formula before you proceed to the next formula.
If you choose to skip the syntax check and immediately save the formula, Crystal Reports will check the syntax anyway. If there is an error, you ll be given the opportunity to save the formula with the error. This makes little sense, because Crystal Reports will stop as soon as you try to run the report and display the error message in the Formula Editor. If you try to save and get a syntax error, correct the error and try to save the formula again. If there are no errors, you ll no longer get a syntax error message and the formula will be saved.
Once the formula has been saved and the Formula Workshop has closed, you will see the formula name under the Formula Fields category of the Field Explorer. You can simply drag and drop the formula on the report, just like a database field. In the case of the Extended Price formula, notice that the formula has taken on the currency data type. This occurred because a currency field was multiplied by a number field, resulting in a currency formula.
Run Time Debugging Features
As mentioned previously in the chapter, it s possible that an error may occur when the report actually runs, but not be detected when you initially create a formula. It s frustrating to create a formula, click the Check button, and receive a No Errors Found message, only to have the formula return with an error when the report runs. This situation is known as a run time error . Run time errors are often caused by formulas not anticipating the type of data that may be encountered as the report progresses. For example, the formula may perform division on a database field that could possibly return zero for certain records. Or, the formula might extract certain specific characters from a string, only to encounter an error at run time when a record contains a null value for a string, or not enough characters to satisfy the formula s requirements.
In Crystal Reports 8.5 and earlier, you would just receive the error message and the Formula Editor would appear showing the offending formula. However, there would be no indication of what part of the formula was actually causing the error. Now, however, run time errors can be more easily debugged using the call stack . If you encounter a run time error, you ll first see the error message indicating what the error is, such as Divide By Zero, followed by the Formula Workshop showing the offending formula. However, the left-hand tree of the workshop will now show the formulas and functions that led up to the error.
By clicking different entries in the call stack, you can see what the values of variables , functions, and other parts of the formula currently contain. This can be helpful in determining the cause of the error more quickly so that you can supply additional logic to avoid the error in the future.
Editing, Renaming, or Deleting an Existing Formula
After you create a formula, you may wish to change its calculation or add to its function. There are many ways for you to edit the existing formula in either the Field Explorer or Formula Workshop. To edit by using the Field Explorer, perform any of these steps:
-
Select the formula you wish to change and click the Edit button in the Field Explorer toolbar.
-
Select the formula you wish to change and press CTRL-E.
-
Right-click the formula and choose Edit from the pop-up menu.
Or, you may display the Formula Workshop by clicking the Formula Workshop toolbar button or choosing Report Formula Workshop from the pull-down menus. You may then expand the Formula Fields category and click the formula you want to edit.
Any of these options will redisplay the Formula Workshop and Formula Editor with the formula in it.
An even quicker method of editing is available after you ve placed the formula on your report. In either the Design or Preview tab, click the formula. Notice in the status bar that the formula name is preceded by the @ sign. Crystal Reports automatically adds this symbol to the beginning of all formulas you create. Now that you ve selected the formula, simply right-click and choose Edit Formula from the pop-up menu. The formula will reappear in the Formula Editor, ready for you to modify.
If you wish to rename a formula, you may do so either from the Formula Fields category of the Field Explorer or the Formula Workshop. If you are using the Field Explorer, begin by selecting the formula you want to rename. Then, you may either click the Rename button in the Field Explorer toolbar or press the F2 key. You may also right-click the formula and choose Rename from the pop-up menu. The name will become editable ”type the new name and either click outside the formula name or press ENTER. If you ve used this formula inside other formulas or elsewhere on your report, Crystal Reports will change the name there, too. To rename a formula in the Formula Workshop, use identical steps to select and rename the formula.
If you select a formula on the report and press the DEL key, you remove that particular occurrence of the formula from the report. However, the formula remains in the Field Explorer and takes up memory and storage space when you save the report. If you re sure you no longer need the formula, delete it entirely. This must be done from the Formula Fields category of the Field Explorer or the Formula Workshop. Select the formula you want to delete and press the DEL key. You can also choose the Delete button from the appropriate toolbar, or right-click the formula name and choose Delete from the pop-up menu. The formula will be removed from the dialog box.
| Caution | If you remove a formula that is in use somewhere else on the report, such as in another formula or in a hidden section, you ll be given a warning, because when you delete this formula, other formulas dependent upon it may stop working. In some cases, Crystal Reports won t even allow you to delete a formula if it is being referenced in some other formula in the report. While a message will indicate that you cannot undo a formula deletion, you may perform a single undo that returns the formula to the report. However, anything prior to this in the undo stack will no be longer available. |