A substring allows you to grab part of a text. It may be that you only want to display the first few characters or the last characters.

In the Legacy Report Designer (pre Sage 200 2010) you would have used Left or Right

e.g.

left(PLSupplierAccountNumber, 3)

In the New Report Designer you need to use Substring.

Use Substring to restrict the number of characters

To only show the first 3 characters of the Supplier Account number, you would enter the following expression:

Substring(PLSupplierAccounts.SupplierAccountNumber,0,3)

The 0 says which character to start from. Above we want to start before the first character. The 3 is how many characters we want to return (display).

Do not display a variable unless it begins with a particular character

Substrings can be used with IF type expressions to extend their use. You may not want to display something, unless it begins witha paticular character.

In this example the variable is SupplierAccountNumber and the letter is ‘S’. If the account number doesn’t begin with ‘S’ it won’t show anything.

Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1) = “S” ?
Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1): “”

Restrict the number of characters displayed if the variable begins with a particular character

Similar to the above, we could say if the field begins with an ‘S’ then show only this character, otherwise show something else.

In this example if SupplierAccountNumber begins with the letter is ‘S’ it will display the letter S, other wise it will display the SupplierAccountName.

Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1) = “S” ?
Substring(PLSupplierAccounts.SupplierAccountNumber, 0, 1) : PLSupplierAccounts.SupplierAccountName

Advertisements