<< Click to Display Table of Contents >>
The Expression Builder is used to create expressions that are used throughout ReportPro. Each expression has a specific purpose depending on where it is used. A few common uses include extracting information from a data source, restricting the amount of information that is included in a report and controlling when an object prints. This section explains expressions in general terms and describes the pieces that make up an expression.
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. If not for expressions, it would be impossible to add two numbers and display the result on a report.
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 alphanumeric 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 that 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. The CTOD function is most often used to convert a character data type to a date. 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 that 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 that 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 that 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 a name that is assigned to them when they were created.
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 row in the database table 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 an 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 that 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.|