Sage 200 Report Designer - How to build expressions
Description

This article explains how to build an expression within Sage 200's Report Designer module.

NOTE: This article has been prepared and issued to you as a goodwill gesture only and Sage accepts no liability or responsibility for its use. For further support please refer to your Business Partner.

Cause
Resolution
You can use the Expression function within Sage Report Designer to build calculations based on existing values in your data. For example, you may want to add or multiply two individual columns on a report. The Expression function can be used on reports, layouts, letters and labels.
These are the steps that explain how to add an expression to your document.

To add an expression

  1. Toolbox menu > Expression.
  2. Click the area of the report where you want the new expression to appear. 
  3. Expression Editor Window > enter the expression you require > OK.

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 27963.

Note: For information about using functions within expressions, please refer to article 30830.

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, " ")))

To show the invoice value as a % of the overall account balance rounded to the nearest integer

Ceiling(( SLPostedCustomerTrans.BaseGrossValue / SLCustomerAccounts.AccountBalance) * 100)

To show the total weight on a despatch note

StockItems.Weight * SOPOrderReturnLines.DespatchReceiptQuantity

Adding a country code prefix to VAT registration numbers

On your invoice or credit note layout, if you want to add your customer's country code in front of their VAT registration number, you can do this using the following expression:

Your country code and VAT registration number:

SLCustomerAccounts.CountryCode + " " + SLCustomerAccounts.TaxRegistrationNumber

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)))


[BCB:19:UK - Sales message :ECB]


Steps to duplicate
Related Solutions