Sage 200 Report Designer - Common filters and expressions

You can create various types of filter, including report, section and conditional filters. For further information about creating these filter types, please refer to the following articles:

Report Designer - To create or edit filters
Report Designer - How to apply conditional formatting to a variable
Report Designer - How to use operators

NOTE:

When entering filter values, text values must be surrounded by quotation marks. A numeric value doesn't need to be surrounded.

Customer and Supplier filters

To show only outstanding transactions on a report.

SLPostedCustomerTrans.OutstandingValue <> 0

PLPostedSupplierTrans.OutstandingValue <> 0

To exclude Customers or Suppliers with a zero balance

SLCustomerAccounts.AccountBalance <> 0

PLSupplierAccounts.AccountBalance <> 0

To pick up Customers or Suppliers where the balance has exceeded the credit limit

SLCustomerAccounts.AccountBalance <> SLCustomerAccounts.CreditLimit

PLSupplierAccounts.AccountBalance <> PLSupplierAccounts.CreditLimit

To pick up invoices over a certain value on Supplier or Customer reports, for example, Supplier or Customer invoices over £100

SLPostedCustomerTrans.NetValue > 100

PLPostedSupplierTrans.NetValue > 100

To only show Customers or Suppliers who have terms agreed on there account.

The terms agreed is a flag with 0 meaning the terms are not agreed and 1 meaning they have been.

SLCustomerAccounts.TermsAgreed = 1

PLSupplierAccounts.TermsAgreed = 1

Nominal ledger filters

To only include lines that have a reference which begins with a set text.

In this example the text always starts with ABC, however it would work for any other text. The percentage sign allows the reference to contain any text after the ABC

NLPostedNominalTrans.Reference like "ABC%"

Product filters

To exclude accounts flagged as inactive

You can use 1 of 2 available fields to achieve this. The active flag or the active text

StockItems.StockItemStatusID=1

StockItems.Status like "Inactive"

Sales order filters

To only show lines when the despatched quantity does not equal the order quantity

(SOPOrderReturnLines.LineQuantity - SOPOrderReturnLines.DespatchReceiptQuantity) <> 0

NOTE:

For information about adding expressions, please refer to article. For information about using functions within expressions, please refer to article.

TIP:

Within Report Designer you can also use If statements within expressions. These are used to return different values depending on the condition specified. For further information about If statements, please refer to article.

Customer and Supplier expressions

To show the VAT element of the outstanding balance

This will only be correct if the full amount is outstanding

SLPostedCustomerTrans.OutstandingValue - ((SLPostedCustomerTrans.OutstandingValue * SLPostedCustomerTrans.BaseNetValue) / SLPostedCustomerTrans.BaseGrossValue)

To show agents commission based on the net amount

In this example the commission is calculated at 10%.

SLPostedCustomerTrans.BaseNetValue* 0.10

To show the first name and surname of a contact name as separate fields

To show the first name from the Customer contact name:

Substring(SLCustomerAccounts.ContactName,0,IndexOf(SLCustomerAccounts.ContactName," "))

To show the surname from the Customer contact name:

Substring(SLCustomerAccounts.ContactName,IndexOf(SLCustomerAccounts.ContactName," "),

(Length(SLCustomerAccounts.ContactName)-IndexOf(SLCustomerAccounts.ContactName," ")))

To show the first name from the Customer contact name and add a comma:

Concat(Substring(SLCustomerAccounts.ContactName,0,IndexOf(SLCustomerAccounts.ContactName," ")) ,",")

The Concat( ) function is used to combine the first name of the contact name with a comma. The Substring( ) function is used to extract the first name from the SLCustomerAccounts.ContactName. The IndexOf( ) function is used to make the Substring function return all information before the first space in the contact name.

TIP:

For Supplier contact names, replace SLCustomerAccounts.ContactName with PLSupplierAccounts.ContactName.

To change the case used for the Customer contact name

To change the Customer contact name to initial capitals, regardless of whether it was entered in all capitals or all lower case:

NOTE:

This expression only works correctly with a first name and surname in the contact name box. It does not work if the contact name includes a middle name, or if it has only one name.

ToUpper(Substring(SLCustomerAccounts.ContactName, 0,1)) + ToLower(Substring(SLCustomerAccounts.ContactName, 1, (IndexOf(SLCustomerAccounts.ContactName, " ")))) + ToUpper(Substring(SLCustomerAccounts.ContactName, (IndexOf(SLCustomerAccounts.ContactName," " )+1),1)) + ToLower(Substring(SLCustomerAccounts.ContactName, (IndexOf(SLCustomerAccounts.ContactName, " ")+2)))

To show only the end of a value, for example, the last 3 characters

Substring(SLCustomerAccounts.AnalysisCode1,Length(SLCustomerAccounts.AnalysisCode1) - 3)

This calculates the length of the string, in this example the analysis 1 value, and then instructs the substring to start 3 digits from the end, that is, the length minus 3.

Invoicing expressions

To add the invoice payment due date to an invoice based on a fixed number of days, for example, 30 days

SOPInvoiceCredits.DocumentDate + Days(30)

To add the invoice payment due date based on the terms from the Customer record

SOPInvoiceCredits.DocumentDate + SLCustomerAccounts.PaymentTermsInDays

Profit expressions

To show the profit based on the cost

Profit / Cost * 100

To show the profit based on sales

Profit / Sales * 100

Miscellaneous expressions

To show a numeric value in an email subject

In the email settings of a document, to show a numeric value in the email subject you can use the CString() function, however this removes zeros after the decimal place, for example, 100.00 appears as 100 and 100.20 appears as 100.2. If required, you can use an if statement expression to show the zeros. This example uses the sales ledger balance but you can use different amount variables as required.

FormatString ("{0:N2}",SLCustomerAccounts.AccountBalance)

To show the ratio between two values as a percentage

FormatString ("{0:P2}", Value1/Value2))

To show a value with a currency symbol if there is one or else the currency code

This example uses the gross amount but you can use different amount variables as required.

FormatString("{0},{1:N2}", (SYSCurrencies.Symbol!="" ? SYSCurrencies.Symbol : SYSCurrencies.Symbol + " "), SLPostedCustomerTrans.BaseGrossValue)

NOTE:

The Sign properties of this expression must be set to signed.

To replace the decimal separator with a comma, for example, when displaying foreign currencies

This example uses the gross amount but you can use different amount variables as required.

Replace( FormatString("{0:f2}", SLPostedCustomerTrans.GoodsValueInAccountCurrency), "." , "," )

The FormatString specifier 'f2' gives a string fixed to two decimal places. You can change this if required. The above expression only works if it's set to Signed. To show the values as Unsigned, Debit or Credit, you must include this in the expression, but leave the Properties set to Signed, for example:

Replace( FormatString("{0:f2}", Unsigned(SLPostedCustomerTrans.GoodsValueInAccountCurrency)), "." , "," )
Replace( FormatString("{0:f2}", Debit(SLPostedCustomerTrans.GoodsValueInAccountCurrency)), "." , "," )
Replace( FormatString("{0:f2}", Credit(SLPostedCustomerTrans.GoodsValueInAccountCurrency)), "." , "," )

Date Expressions

NOTE:

For information about adding expressions, please refer to this article. For information about using functions within expressions, please refer to this article.

TIP:

You can use expressions to change the date formats used on your reports or to calculate other dates or values based on a date. Below are some examples:

To show the last day of the month based on a date

For example, to show the end of the month that a transaction is dated in.

CreateDateTime(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate),
DaysInMonth(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate)))

For example, if the transaction date is 12/11/12, the expression shows 30/11/12.

To show the last day of the next month based on a date

For example, an invoice due date may be the end of the month following the invoice date. To show this:

MonthFromDate(SLPostedCustomerTrans.TransactionDate) < 12 ?
CreateDateTime( YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate)+1,
DaysInMonth (YearFromDate(SLPostedCustomerTrans.TransactionDate), MonthFromDate(SLPostedCustomerTrans.TransactionDate) +1) ) :
CreateDateTime(YearFromDate(SLPostedCustomerTrans.TransactionDate)+1, 1, 31)

For example, if the invoice date is 12/11/12, the expression shows 31/12/12.

To show the first day of the next month based on a date

SLPostedCustomerTrans.TransactionDate +
((DaysInMonth(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate)))) -
(StringToInteger(DateTimeToFormattedString(SLPostedCustomerTrans.TransactionDate, "dd"))) +1

To show a specific date in the following month based on a date

For example, an invoice due date may be the 20th of the month following the invoice date. The following expression shows this due date:

For example, if the invoice date is 12/11/12, the expression shows 20/12/12.

NOTE:

If you are viewing this article from the Sage website, a list of Sage 200 Report Designer articles are now available.