Sage 200 Report Designer - Report Designer Functions

Examples:

• You can round a customer account balance up to the nearest whole number, for example, the following function displays a customer account balance of £20.11 as £21.00:
  
  Ceiling(SLCustomerAccounts.AccountBalance)

Example:

• To combine the word Tel: and the customer's telephone number into one field, making it easier to align and move, use the following function:
  
  Concat("Tel: ",SLCustomerAccounts.ContactTelephoneNumber)
  
  
• You can also use Concat function to get the currency symbol to print directly in front of a numeric value. For example, to get the currency symbol to print directly before the SOPInvoiceCredits.InvoicedGrossValue, use the following function:
  
  Concat(SYSCurrencies.Symbol, FormatString("{0:N2}", (SOPInvoiceCredits.InvoicedGrossValue)))

• If the statement is true it shows a value of 1, if it's false it shows 0.
• You can use this with If statements.
• This function is case sensitive.

Example:

• If a customer's post code contains the letters NE, the following function displays 1. If not, it displays 0:
  
  Contains(SLCustomerLocations.C_AddressLine5, "NE")
  
  
• If the customer's post code contains the letters NE, the following function displays the post code. If not, it displays nothing:
  
  Contains(SLCustomerLocations.C_AddressLine5, "NE") ? (SLCustomerLocations.C_AddressLine5) : ""

• If added to a details section it shows a running count.
• If added to a footer section it shows the total count for the group or the report.

Example:

To count the number of customer records on a report:

Count(SLCustomerAccounts.CustomerAccountNumber)

• If added to a details section it shows a running count.
• If added to a footer section it shows the total count for the group or the report.
• This function is not case sensitive.

Example:

To count the number of customer records on a report that contain an A in the account reference:

Countif(SLCustomerAccounts.CustomerAccountNumber like "%A%")

Example:

On the Simple Stock Transaction History report, to show the number of sources/destinations the item has been associated with:
Countunique(TransactionHistories.SourceAreaReference)

• You can use this in Email Options > Subject to show a numeric value, as numeric variables in the email subject line generate the message:
• 'You must enter an expression that returns a system string value.'

Example:

• To convert an invoice number to an alphanumeric string:
  
  CString(SOPInvoiceCredits.DocumentNo)
  
  
• To show the text Sales Invoice and the invoice number on an email subject line:
  
  "Sales Invoice" + " " + CString(SOPInvoiceCredits.DocumentNo)

• You can use this for grouping reports by date.
• Using this function, you can separate the date values with various characters, for example / or -.

Examples:

• To show an invoice date in the format dd/MM/yyyy with the time as hh:mm:ss:
  
  DateTimeToFormattedString( PLSupplierAccounts.DateOfLastTransaction, "dd/MM/yyyy hh:mm:ss")
• To show only the month part of an invoice date, for example, 01, 02, 03 and so on:
  
  DateTimeToFormattedString( PLSupplierAccounts.DateOfLastTransaction, "MM")
• To show the month and year of an invoice date, for example, September 2011:
  
  DateTimeToFormattedString(PLSupplierAccounts.DateOfLastTransaction, "MMMM yyyy")
  
  
• To show the company name and invoice date on an email subject line:
  
  SYSCompanies.CompanyName + " " + DateTimeToFormattedString(SOPOrderReturns.DocumentDate, "dd-MM-yyyy")

Example:

If a customer's account was opened on 14/02/2013, the following function shows 14:

Dayfromdate(SLCustomerAccounts.AccountOpened)

Displays whether or not a string ends with a specified character.

• If the statement is true it shows a value of 1, if it's false it shows 0.
• You can use this with If statements.
• This function is case sensitive.

Examples:

• If the customer's account reference ends with R, the following function displays 1. If not, it displays 0:
  
  Endswith(SLCustomerAccounts.CustomerAccountNumber, "R")
• If the customer's account reference ends with R, the following function displays the account reference. If not, it displays nothing:
  
  Endswith(SLCustomerAccounts.CustomerAccountNumber, "R") ? (SLCustomerAccounts.CustomerAccountNumber) : ""

Examples:

• The following function displays a customer account balance of £75.85 as £75.00:
  
  Floor(SLCustomerAccounts.AccountBalance)

Prints a string, for example, a piece of text, with one or more predefined expressions inserted into the string using placeholders.

The function is written as:

FormatString("string before placeholder zero {0} string after placeholder zero {1} string after placeholder one etc.", placeholder expression 0, placeholder expression 1)

The placeholders appear as {0}, {1} and so on. They can be individually formatted, for example:

• {0:f2} - The value shown by placeholder zero is formatted to two decimal places.
• {1:c2} - The value shown by placeholder one is formatted as a currency with two decimal places.

Examples:

• To show a customer's balance and remaining credit as one string on the sales Ledger List of Accounts :
  
  FormatString("Customer{0} has a balance of {1} and {2} credit remaining.", SLCustomerAccounts.CustomerAccountNumber, SLCustomerAccounts.AccountBalance, (SLCustomerAccounts.CreditLimit - SLCustomerAccounts.AccountBalance))

This would show for example, Customer ABB001 has a balance of 16,003.00 and 250.00 credit remaining.

FormatString("Customer{0} has a balance of {1:c2} and {2:c2} credit remaining.", SLCustomerAccounts.CustomerAccountNumber, SLCustomerAccounts.AccountBalance, (SLCustomerAccounts.CreditLimit - SLCustomerAccounts.AccountBalance))

This example would show Customer ABB001 has a balance of £16,003.00 and £250.00 credit remaining.

• This can be used in conjunction with other functions.
• You can nest If statements to build up more complicated arguments.
• The ? command is equivalent to Then.
• The : command is equivalent to Else.

Example:

• When viewing the sales ledger transactions report, indicate whether there has been recent activity on the account based on a date of your choosing:
  
  SLCustomerAccounts.DateOfLastTransaction > #2013-07-01# ? "Recent Activity" : "No recent activity"
• The following nested If statements shows different wording depending on the gross value of the transaction:
  
  (SLPostedCustomerTrans.BaseGrossValue >= 0 and SLPostedCustomerTrans.BaseGrossValue <= 999) ? "LOW" : (SLPostedCustomerTrans.BaseGrossValue >= 1000 and SLPostedCustomerTrans.BaseGrossValue <= 9999.99) ? "HIGH" : "OTHER"

For further information about constructing If statement expressions, please refer to article .

Examples:

• To find the position of the first space in a customer's contact name:
  
  Indexof(SLCustomerAccounts.ContactName, " ")- returns the position of the first space
• You can then combine this function with the substring function to return the first name held in the SLCustomerAccounts.ContactName as follows:
  
  Substring(SLCustomerAccounts.ContactName,0,Indexof(SLCustomerAccounts.ContactName, " "))
• Alternatively, to show only the surname of a customer's contact name:
  
  Substring(SLCustomerAccounts.ContactName,(Indexof(SLCustomerAccounts.ContactName, " ")))

Example:

To display how many characters appear in a customer's account name:

Length(SLCustomerAccounts.CustomerAccountName)

• If added to the details section it shows the maximum value at that point on the report.
• If added to a footer it shows the maximum value for the group or the report.

Example:

To show the highest gross amount on a sales day book report:

Max(SLPostedCustomerTrans.BaseGrossValue)

• If added to the details section it shows the minimum value at that point on the report.
• If added to a footer it shows the minimum value for the group or the report.

Example:

To show the lowest gross amount on a sales day book report:

Min(SLPostedCustomerTrans.BaseGrossValue)

Example:

If a customer's account was opened on 14/02/2013, the following function shows 2:

Monthfromdate(SLCustomerAccounts.AccountOpened)

• As it is not possible to use the REPORT.DATE variable in an expression, the Now() function can be used instead.
• Shows the PC date and not the Sage software date.

Example:

• To show the PC date on a report:
  
  Now( )
  
  
• To show a date 30 days from now:

  Now( ) + 30

To ensure a field contains a certain number of characters, this function inserts additional characters to the left of the value. The function is written as:

PadLeft(the value to pad, the character used to pad the field, the number of characters the field should contain)

Example:

• On a product report, to always show stock codes with 10 characters, use the following function:
  
  PadLeft(StockItems.Code, "0", 10)

To ensure a field contains a certain number of characters, this function inserts additional characters to the right of the value. The function is written as:

PadRight(the value to pad, the character used to pad the field, the number of characters the field should contain)

Example:

• On a product report, to always show stock codes with 10 characters, use the following function:
  
  PadRight(StockItems.Code, "0", 10)

For each record the relevant database value must contain a date and must be in a recognised date format. If not, when running the report, the following message appears:

'String was not recognised as a valid DateTime.'

Example:

• The manufacturer field on a stock item is used for dates the item was reviewed. They want a report to find out when each item should next be reviewed. Items should be reviewed every 60 days.

ParseDateTime(StockItems.Manufacturer) + 60

Example:

The customer is running the Sales Order Profitability report and has noticed that some item codes are showing on the same sales order more than once. They want to investigate this further and to highlight them some way.

SOPOrderReturnLines.ItemCode = Previous(SOPOrderReturnLines.ItemCode) ? SOPOrderReturnLines.ItemCode + " *** " : SOPOrderReturnLines.ItemCode

Examples:

• Where a customer's account reference is ABC123, the following function removes the first 3 characters and displays 123:
  
  Remove(SLCustomerAccounts.CustomerAccountNumber, 0, 3)

Examples:

• If the invoice order number is 1231, the following function displays the order number as A23A:
  
  Replace(SLCustomerAccounts.CustomerAccountNumber, "1", "A")

• If the statement is true it shows a value of 1, if it's false it shows 0.
• You can use this with If statements.
• Cannot be used as report filter.
• This function is case sensitive.

Examples:

• If the customer's account reference starts with A, the following function displays 1. If not, it displays 0:
  
  StartsWith(SLCustomerAccounts.CustomerAccountNumber, "A")
• If the customer's account reference starts with A, the following function displays the account reference. If not, it displays nothing:
  
  StartsWith(SLCustomerAccounts.CustomerAccountNumber, "A") ? (SLCustomerAccounts.CustomerAccountNumber) : ""

If the report is run for fields containing non numeric information, the following message appears:

'Input string is not in correct format.'

Examples:

• To multiply the quantity in stock by a number entered in Product Record > Part Number:
  
  Stringtofloat(StockItems.PartNumber) * StockItems.QuantityInStock

This function only works when the report is run for records that contain numeric values in the selected field If you run the report to include records which contain no value or alphanumeric information, the following message appears:

'Input string was not in correct format.'

Examples:

• To multiply the quantity in stock by a number entered in Product Record > Part Number:
  
  StringToInteger(StockItems.PartNumber) * StockItems.QuantityInStock

Displays part of a string from a specified starting point.

The function assigns the first character in a value the number 0, each subsequent character is numbered incrementally. For example, in the text ABCDE, A is equal to 0, B is equal to 1, and so on.

• You can use this in conjunction with Cstring and StringToInteger to convert fields of one type to another.
• This function only works with alphanumeric values.
• If required, use the CString function to convert a numeric field to alphanumeric before performing the Substring function.
• You can use this function within sorts and expressions.
• You can also use this function to show the last characters in a string.

Examples:

• If a sales order number is 123456, the following function displays 23456:
  
  Substring(SOPInvoiceCredits.DocumentNo, 1)
  
  
• If a sales order number is 123456, the following function displays 234:
  
  Substring(SOPInvoiceCredits.DocumentNo, 1, 3)
  1 refers to the starting point in the string and 3 refers to the number of characters to display.
  
  
• • To calculate the nominal code length and then display the last 4 characters:
  
  Substring(NLNominalAccounts.AccountNumber, length(NLNominalAccounts.AccountNumber) -4)

To total only the values that meet the condition specified. The function is written as:

SumIf(the value to sum, the condition that is to be met)

Example:

• On an aged debtors report, to display the total for sales invoice (SC) transactions only, use the following function:
  
  Sumif(SLPostedCustomerTrans.BaseOutstandingValue, SLPostedCustomerTrans.TransactionTypeShortName like "SC")

Examples:

• To convert a customer's account reference to lower case:
  
  ToLower(SLCustomerAccounts.CustomerAccountNumber)

Examples:

• When printing invoices or credit notes, to convert the words invoice or credit note to upper case:
  
  ToUpper(SLCustomerAccounts.CustomerAccountNumber)

• In the case of negative values, the number rounds up towards zero. For example, -100.30 rounds up to -100.00.

Example:

• To round a commission value based on net amount down to the nearest whole value, use the following function:
  
  Truncate(SLPostedCustomerTrans.NetValue * 0.05)

Example:

If a customer's account was opened on 14/02/2013, the following function shows 2013:

YearFromdate(SLCustomerAccounts.AccountOpened)