Progress
Report Builder
User’s Guide
Parts of an Expression
Expressions are made up of one or more of the following components:
For example, in the following expression, Price and Qty are fields and * is the multiplication operator:
In the following expression, IN-LIST is a function that compares the item number with the list of numbers to determine whether the numbers appear in the list, Item-Num is a field, and 20, 21, and 22 are constants:
Fields
Expressions can include database fields of any type (CHARACTER, NUMERIC, LOGICAL, DATE), memo fields, aggregate fields, and other calculated fields. For example, in the following expression, the Order-Total field might be an aggregate field that sums the price of individual line items:
If several tables contain fields with the same name, you must precede the field name used in an expression with a table alias. For example, if your report uses the Customer table and the Order table, they both contain the Cust-Num field. You must precede the Cust-Num field with a table alias that identifies the table it belongs to. If Order and Customer are the table aliases, you enter either Order.Cust-Num or Customer.Cust-Num. To ensure that appropriate aliases are used, select field names from the Fields selection list instead of typing the field names.
Constants
A constant is a value you specify as part of an expression. For example, if the expression for a field that calculates interest is balance times interest rate, the figure that represents the interest rate can be a constant. In the following expression, .08 is the interest rate constant:
Report Builder recognizes four types of constants:
Table 6–2 describes each type of constant.
Operators
Operators are symbols that perform operations within an expression. This section describes the types of operators and operator precedence and expression evaluation.
Report Builder supports the same operators as the Progress 4GL with the addition of the “-” string concatenation operator. Also, the same guidelines apply to using operators in expressions that apply to the 4GL, including using spaces on either side of an operator.
There are two restrictions for using relational operators (=, <>, IN-LIST, BEGINS, MATCHES) with memo fields in calculated field expressions and filter expressions:
For a complete description of the BEGINS, MATCHES, and MODULO operators, see "Operators."
Table 6–3 lists the five main types of operators and the operators within each type.
The Insert Filter Condition dialog box lists the IN-RANGE and IN-LIST functions as operator choices. See "Report Builder Functions," for descriptions of these functions.
NOTE: In Report Builder, the not equals (<>) operator treats the UNKNOWN value differently than the Progress 4GL. If a calculated field uses the not equals operator, Report Builder evaluates the operator as TRUE if both operands are UNKNOWN, but evaluates the operator as UNKNOWN if only one of the operands is UNKNOWN. For example, Report Builder evaluates the Order-Line.Discount <> 0 expression as UNKNOWN for any records whose Order-Line.Discount value is UNKNOWN. The 4GL evaluates the same expression as TRUE.Report Builder evaluates expressions in an order of precedence. Table 6–4 lists the available operators in their order of precedence.
Report Builder evaluates expressions using the following rules:
- Report Builder performs the operations within parentheses first. For example, the result of 1 + 4 / 2 is 3, but the result of (1 + 4) / 2 is 2.5 because Report Builder performs the operation in parentheses first.
- If parentheses are nested or embedded, as in the expression 3 * ((1 + 4) / 2), Report Builder performs the operation in the most deeply embedded parentheses first. In this case, the first operation is 1 + 4, resulting in 5, the second is 5 / 2, resulting in 2.5, and the third is 3 * 2.5, resulting in 7.5.
- If an expression contains several operators that have the same precedence, Report Builder performs the operations from left to right. For example, in the expression 1 + 4 / 2 * 6 / 3, Report Builder performs the division and multiplication operations left to right before performing the addition operation. The first operation is 4/2, resulting in 2; the second operation is 2*6, resulting in 12; the third operation is 12\3, resulting in 4; the last operation is 1+4, resulting in 5.
For example, Report Builder evaluates the following expression as false because the * and + operations are performed first:
The < and > operators are in the next level of precedence, so Report Builder performs them second; the logical OR is in the lowest level of precedence and, therefore, performed last. Report Builder evaluates the expression as if it were ((12 < 8) OR (4 > 5)).
In the following expression, Report Builder evaluates the > operators first. Both conditions must be true for the expression to be true. The balance must be greater than 100 and the number of days must be greater than 30:
In the following expression, Report Builder adds TODAY+30 first and then compares the result to the date returned by the DATE( ) function. Therefore, the expression is true only when TODAY+30 results in a date that is later than 7/1/93:
Functions
Functions perform special operations, such as converting data from one data type to another, calculating the elapsed time between dates, and conditionally returning values.
A single function can serve as a calculated field expression, or several functions can be included within a single expression. For example, to include the system date in a report, you can create a calculated field called Sys-Date, whose expression is TODAY( ). When you insert this field in a report layout, Report Builder prints or displays the system date when you generate the report.
Conversely, an expression can consist of a number of functions that can be nested or embedded within each other. For example, the following expression contains four functions:
The TODAY( ) function returns the system date in the format 11/04/93. The SPELL-MONTH( ) function returns the name of the month of the date supplied by TODAY( ). The YEAR( ) function returns the four-digit year of the date supplied by TODAY( ). The STRING( ) function converts this numeric year value to a four-character string so that it can be concatenated with the name of the month string and the character constant “, ”. On 11/04/93, Report Builder returns “November, 1993" for this expression.
Report Builder provides you with over 50 built-in functions, as well as the ability to create and save your own user-defined functions. For information on creating your own functions, see User-defined Functions." For more information on the built-in Report Builder functions, see "Report Builder Functions."
Wildcard Characters
You can use an asterisk (*) or a period (.) as wildcards in the right operand for the MATCHES operator in expressions or filters. These are the same wildcard characters that MATCHES supports in the 4GL.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |