| | Formatting rules when building expressions |
| Description | Sage Report Designer uses a language similar to Structured Query Language (SQL) to build expressions and filters within reports. |
| Resolution | When creating expressions, operators are used to define the comparison between two variables. | + | Add | Adds one numeric or date value to another | AUDIT_SPLIT.NET_AMOUNT + AUDIT_SPLIT.TAX_AMOUNT
Adds the transaction net amount to the tax amount to produce the gross amount. | | - | Subtract | Deducts one numeric or date value from another | SALES_LEDGER.CREDIT_LIMIT - SALES_LEDGER.BALANCE
Subtracts the customer's balance from their credit limit to show how much credit remains. | | > | Greater than | Specifies that the value required is higher than the value in the filter. | SALES_LEDGER.BALANCE > 100 Locates and selects all customer accounts where the balance is 100.00000001 or higher. | | < | Less than | Specifies that the value required is less than the value in the filter. | SALES_LEDGER.BALANCE < 100 Locates and selects all customer accounts where the balance is 99.9999999 or less, including negative figures. | | >= | Greater than or equal to | Specifies that the value required is either the same as, or higher than the stated value. | SALES_LEDGER.BALANCE >= 100 Locates and selects only customer accounts with a balance of 100 or higher. | | <= | Less than or equal to | Specifies that the value required is either the same as, or less than the stated value. | SALES_LEDGER.BALANCE <= 0 Locates and selects only customer accounts with a balance of zero or less. | | = | Equal to | Specifies that the whole field is equal to a value. | SALES_LEDGER.BALANCE = 0 Locates and selects only customer accounts that have a zero balance. | | <> | Not equal to | Specifies that the value required in the field is not equal to the stated value. | SALES_LEDGER.BALANCE <> 0 Locates and selects only customer accounts that do not have a zero balance, this includes those with a negative balance. | | LIKE | Like | The like operator is used with alphabetic fields. | SALES_LEDGER.NAME LIKE "ABC" - only sales ledger accounts which exactly match ABC will be picked up. A text value must be surrounded by speech marks. A numeric value does not need to be surrounded. | | NOT LIKE | Not like | The not like operator is used with alphabetic fields. | SALES_LEDGER.NAME NOT LIKE "ABC" - only sales ledger accounts which exactly match ABC are excluded. | Linking operator Linking operators are used to link together two parts of a statement in a certain way. | AND | The AND statement is used to link variables that are not alike, but where both values need to be matched before the item is selected. | SALES_LEDGER.DEPT_NUMBER = 1 AND SALES_LEDGER.BALANCE<>0 Locates and selects only those accounts who are in Department 1 and have a balance not equal to zero. | | OR | The OR statement is used to select variables where the status of the variable can be either of the stated selections. | AUDIT_HEADER.TYPE = "SI" OR AUDIT_HEADER.TYPE = "SC" Locates and selects transactions where the transaction type is either Sales Invoice or a Sales Credit. The AND statement would not work here as a transaction type cannot be both SI and SC at the same time. | Other operators When creating expressions, operators are used to define the comparison between two variables. | () | Brackets | Specifies the order in which filters are applied | SALES_LEDGER.BALANCE > 0 AND (AUDIT_HEADER.TYPE LIKE "SI" OR AUDIT_HEADER.TYPE LIKE "SC") The brackets seperate the OR statement from the AND statement. | | % | Percentage wildcard | Specifies an item of text which can be followed, or preceded, by any other symbols. | SALES_LEDGER.ACCOUNT_REF LIKE "AA%" Locates and selects any customer reference that starts with AA, regardless of the remainder of the text. AA1, AA2 and so on, through to AAZZZZZZ | | _ | Underscore wildcard | Specifies a single character in a string being replaced by any other symbol. | SALES_LEDGER.ACCOUNT_REF LIKE "A_A" Locates and selects any three character customer reference, where the first and third characters are A, regardless of the second character. A1A, A2A and so on, through to AZA | | "" | Blanks | References a blank field. | SALES_LEDGER.E_MAIL Like "" Returns any customer accounts with nothing entered into the email field. | | = NULL | Equal to | Used with Date fields, where the field is automatically updated by the Program, or is not required to be entered, and specifies that the field is blank. | SALES_LEDGER.LAST_INV_DATE = NULL Locates and selects all accounts that have not yet had an invoice posted to them. | | <> NULL | Not equal to | Used with Date fields, where the field is automatically updated by the program, or is not required to be entered, and specifies that the field is not blank. | SALES_LEDGER.LAST_INV_DATE <> NULL. Locates and selects all sales accounts who have had an invoiced posted to them. | | IN | Like | Used where several sets of data are required and they are not in a logical range or sequence. | SALES_LEDGER.ACCOUNT_REF IN ("AAA","JJJ","LLL","MMM","YYY","888") Locates and selects the Sales Ledger accounts AAA, JJJ, LLL, MMM, YYY and 888 only. | | NOT IN | Not like | Used where certain sets of data that are not in a logical range or sequence, need to be excluded. | SALES_LEDGER.ACCOUNT_REF NOT IN ("AAA","JJJ","KKK","MMM","YYY","888") Will exclude Sales Ledger accounts AAA, JJJ, KKK, MMM, YYY and 888. | [BCB:19:UK - Sales message :ECB] |
|