Progress
Report Builder
User’s Guide
Creating a Calculated Field
Follow these steps to create a calculated field:
- Choose Calculations
Calculated Field. If there are calculated fields defined for the report, a Calculated Fields dialog box similar to the following appears. It lists the calculated fields already defined for the report:
![]()
If there are no calculated fields defined for the report, the New Calculation dialog box appears.
- Choose the New button to create a new calculated field. The New Calculation dialog box appears:
![]()
The status bar displays information when you select a field or function from the selection lists. When you select a field, it provides the field’s format; when you select a function, it describes the function’s arguments. For example, when you select the EXP function, Report Builder displays “EXP (base, exponent)” in the status bar.
- Specify the field name in the Calculated Field Name fill-in field. Calculated field names follow the same rules as 4GL field names. They can be up to 32 characters long and can consist of alphabetic characters (A-Z or a-z), digits (0-9), and special characters ($, &, #, %, -, and _). In addition, field names must begin with a letter (A-Z or a-z). The field name must also be unique within the report and cannot contain spaces. By using a combination of uppercase and lowercase, you can enter calculated field names that are easily distinguishable from the names of table fields.
- Enter the expression in the Expression fill-in field. Calculated field expressions can contain functions, operators, constants, database fields, memo fields, aggregate fields, and other calculated fields. You can either type these elements or select them from the selection lists and the Operator Palette. You must enter spaces around every operator in the expression. See the "Using the Selection Lists and Buttons" section for more information.
You can format the expression by pressing CTRL-TAB to indent and CTRL-RETURN to start a new line.
- Choose Verify to check the expression syntax. If there is an error in the expression (for example, if you used an unrecognized field name or operator), Report Builder displays an error message. When possible, it also positions the cursor to the place in the expression where the error occurred.
Edit the existing expression.
NOTE: When you choose OK, Report Builder verifies the expression.- Choose OK when the expression is complete and correct to create the field and return to the Calculated Fields dialog box.
- Choose Close to return to the report layout.
When you create a calculated field, Report Builder defines a format string for the field based on the field’s expression. After you insert the field on the report layout, verify that the format string is suitable for the field’s return value.
Using the Selection Lists and Buttons
The easiest way to create an expression is to select elements from the Fields and Functions selection lists, the Operator Palette, and the selection lists that display when you choose the Calc Expression or UDF Expression buttons. Because this technique reduces the amount of typing required, you can build expressions more quickly and accurately.
Table 6–5 explains how to perform basic tasks using these techniques.
Table 6–5: Using the User Interface Elements to Create an Expression
Task Description Selecting fields Double-click a field or select it from the Fields selection list and choose Insert. The selection list displays the fields from the currently joined tables and currently attached memo file (if any), as well as all calculated and aggregate fields created in the report. If the desired fields do not appear in the Fields list, select the proper database, table, or user-defined fields category in the Field Sources selection list. Selecting functions Double-click a function or select it from the Functions selection list and choose Insert. The Functions selection list displays the built-in Report Builder functions, as well as any user-defined functions you have created. Pasting function arguments Double-click a field name or select it from the selection list and choose Insert.
If the Paste Arguments toggle box is activated, when you insert a built-in function in the expression, Report Builder automatically highlights the symbols representing the function’s first argument. These symbols usually indicate the argument’s data type.
For example, if you select the SUBSTRING function and insert it into the field expression, this is how it appears:SUBSTRING( char, num, opt-num )
In this example, the symbols in parentheses represent the arguments to SUBSTRING: “char” represents a required character argument; “num” a required numeric argument, and “opt num” an optional numeric argument.
If you select a character field as the first argument, the field name replaces “char” in the expression. You can then supply the other function arguments as needed.
The Paste Arguments toggle box is activated by default. If you deactivate it, Report Builder inserts function names without supplying argument symbols. Instead, it inserts the function name followed by a set of parentheses — for example, SUBSTRING( ). Selecting operators Activate the Operator Palette toggle box to display the available operators, then select the appropriate operator. See the tables earlier in this chapter for explanations of these operators and descriptions of their use in expressions.
Report Builder automatically inserts the required spaces around the operator when you choose the button. Copying other calculated field expressions Choose the Calc Expression button. The Calculated Fields dialog box appears. Select a calculated field and choose OK. Report Builder inserts the expression of the selected field into the new calculated field’s expression. This allows you to create a new expression by simply modifying an existing one rather than starting from scratch.
When you select a calculated field in the Calculated Fields dialog box, Report Builder displays the field’s expression in the bottom section of the dialog box. Copying a UDF expression Choose the UDF Expression button. The User Defined Functions dialog box appears. Select a UDF expression and choose OK. For more information on UDFs, see User-defined Functions."
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |