Sage 200 Report Designer - How to use operators

How the basic operators work

When creating filters, operators are used to define the comparison between two variables.

NOTE: To filter on negative values you should enclose the value in speech marks, for example, SLCustomerAccounts.AccountBalance < "-100".

>	The greater than operator is used with alphabetic or numeric fields, and specifies that the value required is higher than the value in the filter. Example SLCustomerAccounts.AccountBalance > 100 - locates and selects all customer accounts where the balance is 100.00000001 or higher.
<	The less than operator is used with alphabetic or numeric fields, and specifies that the value required is less than the value in the filter. Example SLCustomerAccounts.AccountBalance < 100 - locates and selects all customer accounts where the balance is 99.9999999 or less, including negative figures.
>=	The greater than or equal to operator is used with alphabetic or numeric fields, and specifies that the value required is either the same as, or higher than the stated value. Example SLCustomerAccounts.AccountBalance >= 100 - locates and selects only customer accounts with a balance of 100 or higher.
<=	The less than or equal to operator is used with alphabetic or numeric fields, and specifies that the value required is either the same as, or less than the stated value. Example SLCustomerAccounts.AccountBalance <= 0 - locates and selects only customer accounts with a balance of zero or less.
=	The equal to operator is used with either alphabetic or numeric fields, and specifies that the whole field is equal to a value. For example: SLCustomerAccounts.AccountBalance = 0 - locates and selects only customer accounts that have a zero balance.
<>	The not equal to operator is used with alphabetic or numeric fields, and specifies that the value required in the field is not equal to the stated value. Example SLCustomerAccounts.AccountBalance <> 0 - locates and selects only customer accounts that do not have a zero balance, this includes those with a negative balance.
!=	An alternative not equal to operator, used with alphabetic or numeric fields. This also specifies that the value required in the field is not equal to the stated value. Example SLCustomerAccounts.AccountBalance != 0 - locates and selects only customer accounts that do not have a zero balance, this includes those with a negative balance.
NOTE: Regardless of the decimal precision set on the object, the decimal precision is always set to 8 places when using any of the following operators: >,<, <>,<= or >=.
LIKE	The like operator is used with alphabetic fields. Example SLCustomerAccounts.CustomerAccountName LIKE "ABC" - only sales ledger accounts which exactly match ABC will be picked up. NOTE: A text value must be surrounded by speech marks. A numeric value does not need to be surrounded.
NOT LIKE	The not like operator is used with alphabetic fields. Example SLCustomerAccounts.CustomerAccountName NOT LIKE "ABC" - only sales ledger accounts which exactly match ABC are excluded. NOTE: A text value must be surrounded by speech marks. A numeric value does not need to be surrounded.

How the linking operators work

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.

Example

SLCustomerAccounts.CountryCode= "FR" AND
SLCustomerAccounts.AccountBalance <>0 - locates and selects only those accounts whose country is listed as France and have a balance not equal to zero.

The OR statement is used to select variables where the status of the variable can be either of the stated selections.

Example

SLPostedCustomerTrans.TransactionTypeShortName = "SI" OR SLPostedCustomerTrans.TransactionTypeShortName = "SC" - locates and selects transactions where the transaction type is either Sales Invoice or a Sales Credit.( For example on the Sales Ledger Transactions Report).
The AND statement would not work here as a transaction type cannot be both SI and SC at the same time.

( )

Brackets can be used to link one or more filters together - For further information about this, please refer to the following section. If brackets are used in a filter, the portion of the filter in the brackets are computed first.

How the rules about brackets and reverse logic are applied

To ensure that your data is filtered correctly there are certain rules that must be obeyed.

Brackets

Sometimes you may need to build more complex filters for your data. For example, you may want to select Sales Invoice or Sales Credit transactions, but only from accounts with a balance greater than zero.

If you use the following statement:

SLCustomerAccounts.AccountBalance > 0 AND SLPostedCustomerTrans.TransactionTypeShortName LIKE "SI" OR SLPostedCustomerTrans.TransactionTypeShortName LIKE "SC"

when the report is run with the filter applied, an error message appears. This is because the program cannot work out which part of the filter to apply first.

The correct statement to select this information is:

SLCustomerAccounts.AccountBalance > 0 AND (SLPostedCustomerTrans.TransactionTypeShortName LIKE "SI" OR SLPostedCustomerTrans.TransactionTypeShortName LIKE "SC")

The brackets now separate the OR statement from the AND statement.

Reverse Logic

When you are working with negative operators you sometimes need to apply reverse logic.

For example, if you are building a filter on a variable such as Type, where the transaction can only be equal to one type at any time, you could use either OR or AND.

First Statement
SLPostedCustomerTrans.TransactionTypeShortName = "SI" OR
SLPostedCustomerTrans.TransactionTypeShortName = "SC"

Second Statement - using reverse logic
SLPostedCustomerTrans.TransactionTypeShortName <> "OI" AND
SLPostedCustomerTrans.TransactionTypeShortName <> "SR" AND
SLPostedCustomerTrans.TransactionTypeShortName <> "OC" AND
SLPostedCustomerTrans.TransactionTypeShortName <> "SP"

Both filters locates and selects only SI and SC transactions.

NOTE: Brackets are not needed in either of the above filters, unless you add further specifications. If you want to add more to the filter, you should surround both of the above filters with one set of brackets.

How the advanced operators work

As well as the basic operators, there are a number of advanced operators available. The advanced operators give you even greater control of your data.

= NULL	The = NULL operator is 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. For example: SLCustomerAccounts.DateOfLastTransaction <> NULL - locates and selects all accounts that have not yet had an invoice posted to them.
<> NULL	The <> NULL operator is 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. Example SLCustomerAccounts.DateOfLastTransaction <> NULL - locates and selects all sales accounts who have had an invoiced posted to them.
IN	The IN operator is used where several sets of data are required and they are not in a logical range or sequence. Example SLCustomerAccounts.CustomerAccountNumber IN ("BET001","FUT001","KIT001","MOL001") - locates and selects the Sales Ledger accounts BET001, FUT001, KIT001 and MOL001only.
NOT IN	The NOT IN operator is used where certain sets of data that are not in a logical range or sequence, need to be excluded. Example SLCustomerAccounts.CustomerAccountNumber NOT IN ("BET001","FUT001","KIT001","MOL001") - will exclude Sales Ledger accounts BET001, FUT001, KIT001 and MOL001.

To add simple calculations

In the Expression Editor you can also perform a simple calculation then compare this to a variable or another simple calculation.

Example

SLCustomerAccounts.AccountBalance > (SLCustomerAccounts.CreditLimit+ 10) - locates and selects all those customer accounts where the Balance is £10 or more over the Credit Limit.

To use dates

SLCustomerAccounts.AccountOpened >= #01/04/2013# - locates and selects any accounts set up on or after 1st April 2008.

To filter on a blank date use the following filter format:

SLCustomerAccounts.DateOfLastTransaction = NULL

To show or exclude blanks

In filters you can search for or exclude records with blank fields.

To show blanks you should filter for Like "", for example:

SLCustomerAccounts.ContactEmailAddress Like ""

To exclude blanks you should filter for Not Like "", for example:

SLCustomerAccounts.ContactEmailAddress Not Like ""

How the basic operators work

Example

Example

Example

Example

Example

Example

Example

Example

How the linking operators work

Example

Example

How the rules about brackets and reverse logic are applied

Brackets

Reverse Logic

How the advanced operators work

Example

Example

Example

To add simple calculations

Example

To use dates

To show or exclude blanks

Sage Business Partners can now log new cases online!

Sage 200 Services