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:

Price * Qty 

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:

IN-LIST (Item-Num, 20, 21, 22) 

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:

Order-Total * Ship-Charge 

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:

Balance * .08 

Report Builder recognizes four types of constants:

Table 6–2 describes each type of constant.

Table 6–2: Report Builder Constant Types 
Constant
Description
Numeric
A number that can contain a decimal point and can be preceded by a plus or minus sign. You can specify up to 15 decimal places. For example, 3.1415927 can be used as a constant in expressions that require the value of pi to seven decimal places.
Character
Any character or string of characters enclosed in double quotes or single quotes. For example, the expression “Dear ” + Contact - “,” or ‘Dear ’ + Contact - ‘,’ returns a value like “Dear Gloria Shepley,”. The expression contains the character constant “Dear”. Report Builder treats character string constants as case insensitive.
Logical
True or false value. The valid logical constants are true, false, yes, no. You can use uppercase or lowercase. For example, the following expression returns a true value if the value in the Balance field is greater than 200, and a false value if it is less than or equal to 200:

IIF(Balance > 200, true, false)
Date
Date value. You must use slashes (/) as delimiters and specify the month, day, then year. For example, mm/dd/yy or mm/dd/yyyy. If you specify a two-digit year, Report Builder assumes the date is in the twentieth century.

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.

Table 6–3: Arithmetic Operators
Type
Operator
Description
Arithmetic
+
Addition
 
Subtraction
/
Division
*
Multiplication
MODULO
Remainder after division
Date
+
Addition of days to a date. For example:
Order-Date + 30
 
Subtraction of days from a date or subtraction of one date from another. For example:
Ship-Date - Order-Date
Character
+
Concatenation (joining two character strings into one). For example:
First-name + Last-name
 
Concatenation after removing trailing spaces from the string before the operator. For example:
First-name - " " + Last-name
Relational
BEGINS
Returns a logical value indicating whether the expressions begin with the same pattern.
MATCHES
Returns a logical value indicating whether the expressions have the same pattern.
= or EQ
Equal to
<> or NE
Not equal to
< or LT
Less than
<= or LE
Less than or equal to
> or GT
Greater than
>= or GE
Greater than or equal to
Logical
NOT
Logical not
AND
Logical and
OR
Logical or

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.

Table 6–4: Operator Precedence
Precedence
Operator
Description
Sixth (highest)
Unary -
Treats following value as a negative.
Unary +
Treats following value as a positive.
Fifth
MODULO
Returns the remainder after division.
/
Division
*
Multiplication
Fourth
-
Subtraction
+
Addition
+
Concatenation
Third
MATCHES
Indicates that the expression has the same pattern.
BEGINS
Indicates that the expressions begin with the same pattern.
= or EQ
Equal to
<> or NE
Not equal to
> or GT
Greater than
>= or GE
Greater than or equal to
< or LT
Less than
<= or LE
Less than or equal to
Second
NOT
Logical NOT
AND
Logical AND
First (lowest)
OR
Logical OR

Report Builder evaluates expressions using the following rules:

For example, Report Builder evaluates the following expression as false because the * and + operations are performed first:

3 * 4 < 8 OR 4 > 3 + 2 

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:

Balance > 100 AND DAYS > 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:

DATE ("7/1/93") < TODAY + 30 

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:

SPELL-MONTH(TODAY()) + -," + STRING(YEAR(TODAY()), "9999") 

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