Syntax for Selection Criteria Expressions
The easiest way to create a selection criteria expression is to use the FR Paste function on the Financial Statement Designer menu bar. See FR Paste.
However, you can also enter expressions directly into spreadsheet cells by using the guidelines listed below.
The format of the expression is:
expression ::= [(...] condition [)...] [Boolean-operator [(...] condition [)...]...]
where:
condition ::= field-name relational-operator operand
Boolean-operator ::= AND | OR
operand ::= <field-name | constant>
relational-operator ::= > | < | = | <= | >= | != | LIKE
Hence expressions would be along the lines of:
ACCTTYPE = "I" AND QTYSW = "Yes" OR ACCTDESC LIKE "%sale%"
ACCTTYPE and QTYSW (quantities switch) are fields in the account master file. All other fields in this file are listed in the next section. Selection criteria use account master fields only.
Use
brackets to force evaluation sequence
Selection criteria expressions are evaluated strictly from left to right unless brackets are put in. In other words, the above example will be evaluated as:
(ACCTTYPE = "I" AND QTYSW = "Yes") OR ACCTDESC LIKE "%sale%"
The expression will select all accounts that contain the word "sale" in their description, and it will select all income statement accounts that also store quantities. (Note that criteria is case-sensitive, so a description with “Sale” will be excluded.)
You may use brackets to change the order of evaluation. For example:
ACCTTYPE = "I" AND (QTYSW = "Yes" OR ACCTDESC LIKE "%sale%")
will select income statement accounts which store quantities or which contain the word sale in their description.
All the relational operators work with all the field types except for switches (like QTYSW), where only the = and != (not equal) operators apply.
Spaces
separate parts of the expression
You use spaces to separate field names, operators and constants in expressions. For this reason, if you are comparing a string that includes spaces, you must enclose the entire string in quotation marks.
LIKE
The LIKE operator is similar to the = operator, except the second operand can contain the wild cards "%" and "_".
-
"%" matches any group of characters.
-
"_" matches any single character.
So ACCTDESC LIKE "%sale%" is true for any accounts with descriptions that include the word "sale." This would include "Product B sales," as well as "Cost of sales."
Criteria
are text parameters
Remember that criteria are text parameters. If you place them in formulas, you must enclose them in quotation marks.
If you paste a criteria in column B, you will notice that FR Paste inserts them as formulas containing text. For example:
="(ACCTTYPE = ""I"" AND QTYSW = ""Yes"" )"
FR Paste adds an extra pair of quotation marks around text values to accommodate spaces within the text strings.
If you paste a formula with criteria, it looks like this:
=FRACCT("ACCTDESC","","(ACCTTYPE = ""I"")")
If this is a default line column specification, the middle parameter in the formula will take on the account reference specified in column A.