Complex Expressions

Sometimes you will need to use more complex expressions to achieve the desired results.  For instance, you may need to add a field to your report and format it. This can be accomplished in one report expression by combining report functions.

When writing complex expressions, remember these rules:

1. Complex expressions can be up to 255 characters in length (including spaces).
2. Every function MUST have an opening and a closing parenthesis.
3. Text elements must be enclosed in quotations ("") in report expressions.
4. Fields in your expression must be the same type, or they must be converted to the same type (e.g. number fields must be converted to text).

Examples

The following is an example of one function nested in another. It uses the ADDCRSE() function to add the Course End Date field to the report, then uses the NICEDATE() function to format the date range into character format.

NICEDATE(cobegdate,ADDCRSE(cocrse,”coenddate”))

This is an example of several functions nested together in an IIF() statement. If the last name field is empty in a registration (if you enrolled the firm instead of an individual for billing purposes), it adds the Firm name field to the report and display it, otherwise it display the registrant’s name formatted with the NAMER() function (allows you several name format options).

IIF(EMPTY(nmname3),ADDNAME(rgid,"nmfirm"),NAMER(nmid,2))

This is an example of combining text elements and fields to create a paragraph in a confirmation letter.  It uses the STR function to convert the number fields to text (i.e. due and paid) and the LTRIM function to trim extra spaces from the left of the number fields.  Then the text and fields are combined using the plus sign concantenator to form the paragraph (see Adding Report Elements topic for more information about adding multiplle elements to an expression box).

"The total cost for this course is $"+LTRIM(STR(due,8,2))+".  We show that to date you have paid a total of $"+LTRIM(STR(paid,8,2))+". This leaves you a balance due of $"+LTRIM(STR(due-paid,8,2))

Comparison Operators

The following Comparison Operators can be used in complex report expressions:

Numeric Operators

• = - Equal to specified expression (e.g. rgcrsefee=125.00 returns a registration if the registration fee is equal to 125.00).
• => - Equal to or Greater than specified expression (e.g. rgcrsefee => 125.00 returns a registration if the registration fee is greater than or equal to 125.00).
• > - Greater than specified expression (e.g. rgcrsefee > 125.00 returns a registration if the registration fee is greater than 125.00).
• <= - Lesser than or Equal to specified expression (e.g. rgcrsefee <= 125.00 returns a registration if the registration fee is lesser than or equal to 125.00).
• < - Lesser than specified expression (e.g. rgcrsefee < 125.00 returns a registration if the registration fee is lesser than 125.00).
• <> - Not equal to specified expression (e.g. rgcrsefee <> 125.00 returns a registration if the registration fee is not equal to 125.00).

Arithmetic Operators

• • • Addition
• - - Subtraction
• - - Multiplication
• / - Division

Character Operators

• = - Begins with specified expression (e.g. nmfirm="ACEware" returns names if the Firm field begins with ACEware).
• $ - Contained within specified expression (e.g. "aceware.com" $ nmemail returns names if aceware.com is in the email address field).
• == - Exactly matches specified expression (e.g. nmfirm="ACEware Systems, Inc." returns names if the firm field exactly matches ACEware Systems, Inc.).
• <> - Not equal to specified expression (e.g. pytype <> '8' return a payment if the payment type is not 8 [Void]).

Notes

• Character strings must be enclosed in quotation marks.
• Character string comparisons are case-sensitive. If you want to return records no matter the case, you can use the UPPER() report function to upper-case the field contents for the comparison, e.g.

upper(nmfirm)="ACEWARE" returns names if the firm field begins with ACEWARE, no matter how it's actually entered in the field (ACEware, Aceware, aceware).

Logical Operators

• ! - Not equal to the specified expression (e.g. !rgcancel returns a registration if it is not canceled).

Note:  this operator can also be used with certain report functions.  For instance, the EMPTY() function returns a value if the specified field is empty.  !EMPTY() will return a value if the specified field is not empty.