<< Click to Display Table of Contents >>
One of the more powerful and flexible features available in ReportPro is the ability to use expressions to extract and manipulate information. If not for expressions, it would be impossible to add two numbers and display the result on a report.
Expressions are to ReportPro what sentences are to the English language. They convey a message or command to ReportPro. An expression can consist of several different components including constants, variables, functions and operators.
Data can be thought of as the subject of an expression. The intent of most expressions is to retrieve or manipulate some type of data. To use expressions effectively, you must understand the basic types of data supported by ReportPro. The four basic data types are:
|•||Character - Defined as zero or more alpha-numeric characters. Character data types are used to express names, addresses, phone numbers, etc. In an expression, a character data type is usually stated by enclosing a sequence of characters within a pair of quotes (e.g., "John" or "(324) 342-9999". To express a character data type that does not contain any characters, use double quotes with no intervening characters (e.g., "").|
|•||Numeric - Defined as a data item on which you can perform mathematical operations (e.g., addition, multiplication and division). A numeric data type can contain the digits 0-9, a period to represent a decimal position and the plus or minus sign to indicate the sign of the number. In an expression a numeric data type is stated as 1230.982 or -9355 (quotes are not allowed).|
|•||Date - Defined as a data item which expresses a calendar date. You can manipulate dates in several ways, such as finding the number of days between two dates. A date cannot be represented directly in an expression; instead you must use a function to convert a character data type into a date. This is usually done by using the CTOD function. For example, you can represent 12/12/93 as a date by adding the sequence CTOD("12/12/1993") into the expression.|
|•||Logical - Defined as a data item which can only contain two values. A logical can only be true (.t.) or false (.f.). In an expression, a logical is represented by .t. or .f..|
A constant is data which cannot change. A constant must be one of the pre-defined data types identified above. Constants are used in conjunction with variables, functions and operators to manipulate data. The sequence "John" is a character constant. 134.88 is a numeric constant.
Variables are temporary storage areas which hold data. The data being stored by the variable must be one of the pre-defined data types discussed earlier. Unlike a constant, the data held by a variable can be modified. Variables are referenced by the name which is assigned to them in the Variables dialog.
For example, suppose we define a variable named Total. We can assign data to the variable with the expression Total := 0. The variable Total now holds a numeric data type with a value of 0. If we change the expression to Total := "0", the variable would hold a character value.
A special type of variable is the database field. A database field contains a value and is referenced by its name just like a variable, but the value of a database field cannot be modified.
A database field name consists of two or more parts. All field names consist of a table name separated by a period (".") followed by a field name. Fields from SQL queries can also require an owner name.
The value of a database field depends on which record in the database is current. Record positioning is controlled automatically by ReportPro.
A function can be considered the verb of the expression. It is the part that performs some action. Functions consist of several parts. The first part, the function name, describes what the function does. For example, the function LTrim() (short for Left Trim), tells us that it removes the spaces from the left side of a character data type.
The second part of a function is its arguments. An argument simply provides us with a way to send information to the function. Our LTrim() function has no way of knowing what we want to remove the spaces from unless we explicitly tell it. So we need to add a argument to our function. The proper use of LTrim is to express it as LTrim(" John"). Now LTrim() knows what we want to remove the spaces from.
The last part of a function is its return value. The return value is how the function reports the result of its work. The return value of a function is automatically substituted into an expression after the function has completed its work. In our LTrim(" John") example, the result of the function's work is "John". The value "John" is automatically returned to our expression. We can assign the result of the function to a variable using the expression Name := LTrim("John "). The variable, Name, now contains "John".
Along with functions, constants, and variables, operators are also a basic building block of expressions. Operators are similar to functions in that they perform some operation and return a value. Operators are expressed in a different format than functions. For example the operator :=, used in the examples above, is the assignment operator. It is used to assign a value to a variable. Another popular operator is the addition operator (+). The addition operator can be used to add two numbers together (100 + 50).
The following are examples of expressions which you can use in the Expression Builder dialog window.
|•||Customer.Name - This is the simplest of all expressions. It is a reference to a database field.|
|•||Upper(Customer.Name) - This expression is slightly more complex. It accesses the field Name from the Customer table and converts all characters to upper case.|