Avg(variable)

General

Returns the average of a series of values.

You can set this within Properties > Function, but you can also use it as a function in expressions.

For example:

• To show the average of all customer account balances on a report:

  Avg(SALES_LEDGER.BALANCE)

Ceiling(variable)

Value

Rounds up to the next whole number.

For example:

• To round the customer balance up to the next whole number:

  Ceiling(SALES_LEDGER.BALANCE) 

  A balance of 500.59 displays 501 and a balance of 500.01 also displays 501. A negative balance of -500.59 returns -500.

• Use this function within calculations. For example, you have a 12 item pallet. Divide the item quantity by 12 for the number of pallets needed to contain a sales order quantity. Then, use the ceiling function to round up the result. 

          Ceiling(SOP_ITEM.QUANTITY/12)

This shows the number of pallets needed.

Concat(variable1, variable2)

String

To combine two or more fields into one value.

Combine text or variables or a combination of the two.

For example:

• To show the text and variable together rather than worrying about spacing: 

  Concat("Tel:",SALES_LEDGER.TELEPHONE) 

  or 

  Concat("Dept: ",SALES_LEDGER.DEPT_NUMBER," - ",SALES_LEDGER.DEPT_NAME)

• Use the Concat function to get the currency symbol to print directly in front of a numeric value when using Foreign Trader. For example, to get the currency symbol to print directly before the INVOICE.FOREIGN_INVOICE_GROSS, use the following function:

  Concat(CURRENCY.SYMBOL, FormatString("{0:N2}",(INVOICE.FOREIGN_INVOICE_GROSS)))

  Set the properties of this expression to signed. If not, when running the layout, the following message appears: 'Sage.expressions.DefaultLibraries.Accounts.Unsigned(system.string) is not defined.'

Contains(Variable, what it contains)

String

To determine whether certain characters are contained within a field.

• 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

• Used for text fields only. To use a numeric field, you must use Cstring to convert to text

For example:

• To see whether the post code contains NE to pull out North East-based companies: 

  Contains(SALES_LEDGER.ADDRESS_5,"NE") 

• You can then use this in an if statement, for example: 

  Contains(SALES_LEDGER.ADDRESS_5,"NE") ? "North East" : "Other"

  If the post code contains NE this shows North East, if not it shows Other.

Count(variable)

General

Counts the number of instances of a field on a report.

• Set this in Properties then Function. You can also use it as a function in expressions

• 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

For example:

To find the total number of customers on a report, add the following expression to the Report Footer:

Count(SALES_LEDGER.ACCOUNT_REF)

• Click Properties, then Reset Section, and check the setting is Report Footer

CountIf(condition)

General

The software counts the number of instances on a report if it meets the specified conditions.

• 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 isn't case-sensitive

For example:

To count the number of customer records with a balance greater than £1000.00, add the following expression to the Report Footer:

CountIf(SALES_LEDGER.BALANCE > 1000.00)

• Click Properties, then Reset Section, and check the setting is Report Footer

CountUnique(variable)

General

The software counts the number of unique instances of a specified variable.

For example:

To show the number of unique products bought by a customer on an invoice, add the following expression to the INVOICE.ACCOUNT_REF footer:

CountUnique(INVOICE_ITEM.STOCK_CODE)

• Click Properties, then Reset Section, and check the setting is 

  INVOICE.ACCOUNT_REF footer

CreateDateTime(year,month,day)

Date

Creates a date out of its three component parts.

This has limited uses and you can use it with YearFromDate, MonthFromDate, DayFromDate and DaysInMonth functions.

For example:

• To show the start of the month you entered a transaction in: 

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),01) 

  This takes the Year and Month from the transaction date and then adds 01 as the day.

• To show the end of the month you entered a transaction in: 

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE), DaysInMonth(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE)))

  This takes the year and month from the transaction date. Then to calculate the last day of the month it uses the DaysInMonth function.

• To show the end of the next month: 

  MonthFromDate(AUDIT_HEADER.DATE) < 12 ? CreateDateTime(YearFromDate(AUDIT_HEADER.DATE), MonthFromDate(AUDIT_HEADER.DATE)+1, DaysInMonth (YearFromDate(AUDIT_HEADER.DATE), MonthFromDate(AUDIT_HEADER.DATE) +1)) : CreateDateTime(YearFromDate(AUDIT_HEADER.DATE)+1, 1, 31) 

  First, this works out if the month from the transaction is December. If it isn't, the software takes the year from the transaction date. Then takes the month plus one and uses DaysInMonth to return the month's last day. If it's a December transaction date, it takes the year from the date and adds one. It uses 01 as the month and 31 as the day to return the last day of the following January.

Credit(variable)

Sign

Shows only credit values.

Set this within Properties then Function. You can also use it as a function in expressions.

For example:

Within a footer section, use this function to show either the total of all credits, or the overall total only. This is only if it's a credit value. The way this function words depends on this setting:

• Go to Properties and check if Apply Sign After Group is True or False

For example, on the Nominal Activity report the credit total is a total of all credits. Whereas the history balance is a total of all transactions expressed as a debit or credit.

The credit total and the credit part of the history balance have the same credit function applied.

The difference is:

• The total is Apply Sign After Group = False
• The history balance is Apply Sign After Group = True

CString(variable)

String

Converts a numeric field to an alphanumeric string or text.

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

For example:

• To convert an invoice number to an alphanumeric string:

  CString(INVOICE.INVOICE_NUMBER)

• To show the text Sales Invoice and the invoice number on an email subject line:

  "Sales Invoice" + " " + CString(INVOICE.INVOICE_NUMBER)

DateFromPeriod(period)

Date

Returns the first day of the month that the period number relates to.

Uses the numbers 1to 12 as the different months of the company's financial year.

For example:

• To show the start of the financial year:

  DateFromPeriod(1) 

• To show the end of the financial year: 

  CreateDateTime(YearFromDate(DateFromPeriod(12)),MonthFromDate(DateFromPeriod(12)),

  DaysInMonth(YearFromDate(DateFromPeriod(12)), MonthFromDate(DateFromPeriod(12))))

DateTimeToFormattedString

(date, format)

Date

Converts DateTime into a text string which you can format.

You can use this function to return only part of a date or to control how an entire date appears.

A common use of this function in Sage Accounts is to show dates in the subject lines of emails.

You can use this for grouping reports by date.

• You can use this with If statements on column-based reports

• In the format section, the month option is always in uppercase but year and date lowercase

• Popular formats are: 

  - dd/MM/yy which displays as 13/06/25

  - MMMM yyyy which displays as June 2025

  - dddd dd MMMM yyyy which displays as Thursday 13 June 2025

For example:

• To show an invoice date in the format dd/MM/yyyy:

  DateTimeToFormattedString(INVOICE.INVOICE_DATE, "dd/MM/yyyy")

• To show only the month part of an invoice date, for example, 01, 02, 03 and so on:

  DateTimeToFormattedString(INVOICE.INVOICE_DATE, "MM") 

• To return the date as year and month only to group a report by month: 

  DateTimeToFormattedString(AUDIT_SPLIT.DATE,"yyyy,MM")

• To show the company name and invoice date on an email subject line:

  COMPANY.NAME + " " + DateTimeToFormattedString(INVOICE.INVOICE_DATE, "dd-MM-yyyy")

• If a date variable or expression shows a time stamp, for example, 10/02/25 00:00:00, use the DateTimeToFormattedString function to remove the time stamp:

  DateTimeToFormattedString(Expression,"dd/MM/yy") 

• To show the long date including the st, th or rd you can use the following expression: 

  (DateTimeToFormattedString(REPORT.DATE,"dddd") + " " + CString(DayFromDate(REPORT.DATE))) +

  ((DayFromDate(REPORT.DATE) % 10 = 1 and DayFromDate(REPORT.DATE) <> 11) ? "st" : 

  (DayFromDate(REPORT.DATE) % 10 = 2 and DayFromDate(REPORT.DATE) <> 12) ? "nd" : 

  (DayFromDate(REPORT.DATE) % 10 = 3 and DayFromDate(REPORT.DATE) <> 13) ? "rd" : "th")

  + FormatString(" {0:MMMM yyyy}", REPORT.DATE)

DayFromDate(date)

Date

Returns the day from a date.

Use this function as part of a CreateDateTime expression.

For example:

• You opened the customer's account on 14/02/2001, the following function shows 14: 

  DayFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED) 

• To return the day from a transaction date: 

  DayFromDate(AUDIT_HEADER.DATE) 

  It's most often used as part of the CreateDateTime function: 

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),

  DayFromDate(AUDIT_HEADER.DATE))

DaysInMonth(year,month)

Date

Returns the number of days in the specified month and year.

Used as part of a CreateDateTime expression and often with YearFromDate and MonthFromDate.

For example:

• To show the number of days in any given February, including accounting for leap years:

  DaysInMonth(2013,02) 

• Find the end of a month you posted a transaction in:

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),

  DaysInMonth(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE))) 

  This takes the year and month from the transaction date. It then uses the DaysInMonth function to find the total number of days in that month. It then returns the date as the last day of that month.

Debit(variable)

Sign

This shows only debit values.

Set this within Properties then Function. You can also use it as a function in expressions.

For example:

Use this function in the footer section to show either the total of all debits, or the overall total only if it's a debit value. The way this function behaves depends on if Apply Sign After Group shows True or False, in Properties.

For example, on the Nominal Activity report the debit total is a total of all the debits. Whereas, the history balance is a total of all the transactions expressed as a debit or credit.

The debit total and the debit part of the history balance have the same debit function applied.

The difference is:

• The total is Apply Sign After Group = False
• The history balance is Apply Sign After Group = True

EndsWith(variable 1, what it ends with)

String

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

• 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

• Used for text fields only. To use a numeric field, use Cstring to convert to text

For example:

• If the product code ends with an A, the following function displays 1. If not, it displays 0:

  EndsWith(STOCK.STOCK_CODE,"A")

  You can then use this in an if statement, for example:

  EndsWith(STOCK.STOCK_CODE,"A") ? "Yes" : "No"

• If the customer's account reference ends with R, the following function displays 1. If not, it displays 0:

  EndsWith(SALES_LEDGER.ACCOUNT_REF, "R")

• If the customer's account reference ends with R, the following function displays the account reference. If not, it displays nothing:

  EndsWith(SALES_LEDGER.ACCOUNT_REF, "R") ?(SALES_LEDGER.ACCOUNT_REF) : ""

Floor(variable)

Value

Rounds down to the next whole number.

You can use this to display pounds and pence separately.

For example:

• To round the customer balance down to the next whole number: 

  Floor(SALES_LEDGER.BALANCE) 

  A balance of 500.59 returns 500 and a balance of 500.01 returns 500, but a negative balance of -500.59 returns -501.

• To show the pounds element of a value: 

  Floor(Unsigned(INVOICE.FOREIGN_INVOICE_GROSS)) 

  To show the pence element of a value: 

  INVOICE.FOREIGN_INVOICE_GROSS - Floor(Unsigned(INVOICE.FOREIGN_INVOICE_GROSS))

FormatString(text,placeholder1,text, placeholder2,variable1,variable2)

String

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

Write the function 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. You can format individually, for example:

• {0:f2} - The value shown by placeholder zero is two decimal places

• {1:c2} - The value shown by placeholder one is  a currency with two decimal places

For example:

• To show a customer's balance and remaining credit as one string:

  FormatString("Customer {0} has a balance of {1} and {2} credit remaining.", SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.BALANCE,(SALES_LEDGER.CREDIT_LIMIT- SALES_LEDGER.BALANCE))

  This shows Customer A1 has a balance of 1000.00 and 200.00 credit remaining.

  FormatString("Customer {0} has a balance of {1:c2} and {2:c2} credit remaining.", SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.BALANCE,(SALES_LEDGER.CREDIT_LIMIT- SALES_LEDGER.BALANCE))

  This shows Customer A1 has a balance of £1000.00 and £200.00 credit remaining

• To show an invoice item quantity, unit price and net total as one string. But, with different decimal places and showing the £ symbol:

  Formatstring("{0:f0} items costing {1:c4} each, totalling {2:c2}", INVOICE_ITEM.QUANTITY, INVOICE_ITEM.UNIT_PRICE, INVOICE_ITEM.NET_AMOUNT)

  This shows as 8 items costing £7.590 each, totalling £60.72

If statement

General

To display certain values depending on the condition specified.

• You can be use this with other functions

• You can nest If statements to build up more complex criteria

• The ? command is equivalent to Then

• The : command is equivalent to Else

For example:

• When viewing prior year values, sales nominal codes show negative values. The following function states that if the nominal code is within the sales nominal code 4000, then reverse the signing. Otherwise display the value as normal:

NOMINAL_LEDGER.ACCOUNT_REF IN ("4000") ? Reversed (NOMINAL_LEDGER.PRIOR_YR_MTH1) : NOMINAL_LEDGER.PRIOR_YR_MTH1

• The following nested If statements calculate the VAT element of a product sales price, based on the product record tax code:

  STOCK.TAX_CODE Like "T1" ? STOCK.SALES_PRICE * 0.175 : STOCK.TAX_CODE Like "T2" ? 0 : STOCK.TAX_CODE Like "T3" ? STOCK.SALES_PRICE * 0.05 : 0

For further information about constructing If statement expressions, follow our article.

IndexOf(variable, text to find)

String

Reports the position of the first occurrence of the specified characters in a string.

• You can combine this with the Substring function. This returns the value of a field up to a certain character

• Used for text fields only. To use a numeric field, you must use Cstring to convert to text

For example:

• To find the position of the first space in a customer's contact name:

  IndexOf(SALES_LEDGER.CONTACT_NAME," ") - returns the position of the first space

• You can then combine this function with the substring function to return the name before the space:

  Substring(SALES_LEDGER.CONTACT_NAME,0,IndexOf(SALES_LEDGER.CONTACT_NAME," "))

• You can use the same to return everything after the space, for example, to show only the customer's surname:

  Substring(SALES_LEDGER.CONTACT_NAME, (IndexOf(SALES_LEDGER.CONTACT_NAME," "))) 

  When you're returning the remainder of the string, you don't need to specify the number of characters to return.

Insert(string to insert into, what to insert, start point)

String

Inserts a specified string at a specified index position in another string.

Used for text fields only. To use a numeric field, you must use Cstring to convert to text.

The start point value uses an index starting with zero, for example to insert at the very beginning, the start point is 0. To insert after the first character, the start point is 1 and so on.

For example:

• To insert the letters UK after the first character of each stock code:

  Insert(STOCK.STOCK_CODE,"UK",1)

  So a stock code of A123 shows as AUK123.

IsLeapYear(year)

Date

This indicates whether the specified year is a leap year.

• 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

• Most commonly used in expressions to account for leap years in prior year date calculations

For example:

• Use the following expression in reports where you enter a date range as criteria. The report then calculates the dates to represent the same date range for exactly one year prior: 

  CreateDateTime(YearFromDate(CRITERIA.TRAN_DATE_TO)-1, MonthFromDate(CRITERIA.TRAN_DATE_TO),IsLeapYear(YearFromDate(CRITERIA.TRAN_DATE_TO)) = 1 and MonthFromDate(CRITERIA.TRAN_DATE_TO) = 2 and DayFromDate(CRITERIA.TRAN_DATE_TO) = 29 ? 28 :IsLeapYear(YearFromDate(CRITERIA.TRAN_DATE_TO)-1) = 1 and MonthFromDate(CRITERIA.TRAN_DATE_TO) = 2 and DayFromDate(CRITERIA.TRAN_DATE_TO) = 28 ? 29 : DayFromDate(CRITERIA.TRAN_DATE_TO)) 

  The section in bold calculates what the day shows as and translates as:

  If you run the report and the date on the report is a leap year, and the month is February, and the day was the 29th, then return the day as 28th. If you run the report with the year - 1 is a leap year, and the month is February, and the day was 28th then instead show as 29th. Otherwise it will show the day you entered.

LastIndexOf(variable, text to find)

String

Reports the index position of the last occurrence of the specified string.

Used for text fields only. To use a numeric field you, must use Cstring to convert to text.

For example:

• To extract the surname from a contact name that includes a title and forename, use an expression that finds the last space in the name:

  LastIndexOf(SALES_LEDGER.CONTACT_NAME," ") 

• You can combine this with the substring function to return the name after the last space:

  Substring(SALES_LEDGER.CONTACT_NAME,(LastIndexOf(SALES_LEDGER.CONTACT_NAME," "))) 

  When you're returning the remainder of the string, you don't need to specify the number of characters to return.

Length( )

String

Displays how many characters exist in a string.

Used for text fields only. To use a numeric field, you must use Cstring to convert to text.

For example:

To display the length of a customer's account reference:

Length(SALES_LEDGER.ACCOUNT_REF)

Max(val1, val2)

General

Returns the largest result in one field or compares several different fields and returns the largest.

When returning the larger instance of one field, you can set this:

• Go to Properties then Function

You can also use it as a function in expressions.

For example:

• To return the largest instance of one field:

  Max(SALES_LEDGER.BALANCE) 

  Adding this to the Report Footer shows the largest balance of all the customers on the report. 

• To compare two different fields: 

  Max(NOMINAL_LEDGER.BALANCE_MTH1,NOMINAL_LEDGER.BALANCE_MTH2) 

  You can extend this to compare as many fields as you like, separate each field by a comma.

Min(val1, val2)

General

Returns the smallest result in one field or compares several different fields and returns the smallest.

• 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

• When returning the smallest instance of one field, you can set this:

• Go to Properties then Function

• You can also use it as a function in expressions

For example:

• To return the smallest instance of one field:
  
  Min(SALES_LEDGER.BALANCE) 
  
  Adding this to the Report Footer shows the smallest balance of all the customers on the report. 
  
  
• To compare two different fields: 
  
  Min(NOMINAL_LEDGER.BALANCE_MTH1,NOMINAL_LEDGER.BALANCE_MTH2) 
  
  You can extend this to compare as many fields as you like, separate each field by a comma.

For example:

• If you opened the customer's account on 14/02/2001, the following function shows 2: 
  
  MonthFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)
  
  
• It's most often used as part of the CreateDateTime function: 
  
  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),
  MonthFromDate(AUDIT_HEADER.DATE),DayFromDate(AUDIT_HEADER.DATE))

For example:

• To return all transaction types except for SI: 
  
  Not(AUDIT_SPLIT.TYPE Like "SI")

• As it's not possible to use the REPORT.DATE variable in an expression, you can use the Now() function instead
• Shows the PC date and not the Sage software date
• Always leave it blank between the brackets 

For example:

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

          Now( ) + 30

To ensure a field contains a specific number of characters, this function inserts extra characters to the left of the value.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

The total number of characters value is the total string length required and not the number of padding characters.

To always display the invoice number as a 6-digit number on an invoice layout, use the following function:

PadLeft(CString(INVOICE.INVOICE_NUMBER),"0",6) 

Using the above function, an invoice number of 1 appears as 000001 and an invoice number of 123 appears as 000123.

To always show stock codes with 10 characters on a product report, use the following function:

PadLeft(STOCK.STOCK_CODE,"0",10)

To ensure a field contains a specific number of characters, this function inserts extra characters to the right of the value.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

The total number of characters value is the total string length required and not the number of padding characters.

To always show stock codes with 10 characters on a product report, use the following function:

PadRight(STOCK.STOCK_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.'

If a document contains an expression that uses the ParseDateTime() function on a text field, it runs for records that have a date value in the relevant field.

To convert the first line in the Notes section of the order tab on an invoice to a date field in a report:

ParseDateTime(INVOICE.NOTES_1)

To add 30 days to the date entered in the first line of the notes:

ParseDateTime(INVOICE.NOTES_1) + 30

For example:

On a report showing each invoice item line and displaying the invoice number, the invoice number repeats for each of its item lines. To show an asterisk against the first item line of each invoice reference:

AUDIT_SPLIT.INV_REF = Previous(AUDIT_SPLIT.INV_REF) ? AUDIT_SPLIT.INV_REF : AUDIT_SPLIT.INV_REF + " *"

Always enter the starting position, where the first character is 0, and the number of characters to remove.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

For example:

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

• Where a customer's account reference is ABC123, the following function removes the third and fourth characters and displays AB23:
  
  Remove(SALES_LEDGER.ACCOUNT_REF,2,2) 
  
  
• To remove the last three characters of the customer's account reference:
  
  Remove(SALES_LEDGER.ACCOUNT_REF,Length(SALES_LEDGER.ACCOUNT_REF)-3,3)

This function is case-sensitive.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

For example:

• Most often used to remove a certain character from a string by replacing it with nothing, for example: 
  
  Replace(SALES_LEDGER.ACCOUNT_REF,"-","")
  
  This removes each - from the customer account references.
  
  
• If the invoice order number is 1231, the following function displays the order number as A23A: 
  
  Replace(INVOICE.ORDER_NUMBER, "1", "A")

• If the transaction paid flag is Y, the following function displays the value as Yes:
  
  Replace(AUDIT_HEADER.PAID_FLAG, "Y", "Yes") 
  
  
• You can use the following expression to show a comma as the decimal separator for a foreign currency:
  
  Replace( FormatString("{0:f2}", AUDIT_HEADER.FOREIGN_GROSS_AMOUNT), "." , "," )
  
  The FormatString specifier ‘f2’ gives a string fixed to two decimal places. This 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(AUDIT_HEADER.FOREIGN_GROSS_AMOUNT)), "." , "," )

Replace( FormatString("{0:f2}", Debit(AUDIT_HEADER.FOREIGN_GROSS_AMOUNT)), "." , "," )

Replace( FormatString("{0:f2}", Credit(AUDIT_HEADER.FOREIGN_GROSS_AMOUNT)), "." , "," )

For example:

To reverse the sign of a value based on transaction type:

AUDIT_SPLIT.TYPE Like "SI" ? Reversed(AUDIT_SPLIT.NET_AMOUNT) : AUDIT_SPLIT.NET_AMOUNT

For example:

To round the customer balance to the nearest whole number: 

Round(SALES_LEDGER.BALANCE) 

So a balance of 500.58 shows as 501, but a balance of 500.36 shows 500.

Rounds a value to the specified number of decimal places.

You can use this to avoid slight rounding differences between Sage Accounts and Report Designer.

You can also set this in Properties > Formatting > Numeric.

For example:

• To round a transaction net amount to zero decimal places:
  
  RoundDP(AUDIT_HEADER.NET_AMOUNT,0)

• To round a transaction net amount to two decimal places: 
  
  RoundDP(AUDIT_HEADER.NET_AMOUNT,2)

For example:

Most often used in conditional formatting, for example, to show negative values in red on a statement, set the conditional filter to:

Sign(AUDIT_HEADER.FOREIGN_NET_AMOUNT) = -1 

And set the conditionals properties to Text Style > Red.

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.

Used for text fields only. To use a numeric field, you must use Cstring to convert to text.

For example:

• If the customer's account reference starts with R, the following function displays 1. If not, it displays 0:
  
  StartsWith(SALES_LEDGER.ACCOUNT_REF, "R")

• If the customer's account reference starts with R, the following function displays the account reference. If not, it displays nothing:
  
  StartsWith(SALES_LEDGER.ACCOUNT_REF, "R") ? (SALES_LEDGER.ACCOUNT_REF) : ""
  
  
• To see whether a product code starts with an A:
  
  StartsWith(STOCK.STOCK_CODE,"A")
  
  
• The following If statement shows Yes or No depending on whether the product code starts with an A:
  
  StartsWith(STOCK.STOCK_CODE,"A") ? "Yes" : "No"

All records must have a number entered in the appropriate format, there are any non-numeric values, blank fields or whole numbers the following message appears:

'Input string is not in correct format.'

For example:

• To convert a text field into a numeric field: 
  
  StringToFloat(INVOICE.NOTES_1) 
  
  So you can use it in a filter or expression: 
  
  StringToFloat(INVOICE.NOTES_1) > 100
  
  
• To multiply the quantity in stock by a number entered in Product Record > Location:
  
  StringToFloat(STOCK.LOCATION) * STOCK.QTY_IN_STOCK

• To multiply the values entered in the transaction references by 10:
  
  StringToFloat(AUDIT_HEADER.INV_REF) * 10

All records must have a number entered in the appropriate format, there are any non-numeric values, blank fields or decimal numbers the following message appears:

'Input string was not in correct format.'

For example:

• To convert a text field into a numeric field: 
  
  StringToInteger(INVOICE.NOTES_1) 
  
  So you can use it in a filter or expression: 
  
  StringToInteger(INVOICE.NOTES_1) > 100.50
  
  
• To multiply the quantity in stock by a number entered in Product Record > Location:
  
  StringToInteger(STOCK.LOCATION) * STOCK.QTY_IN_STOCK

• To multiply the quantity in stock by a number entered in Product Record > Unit of Sale:
  
  StringToInteger(STOCK.UNIT_OF_SALE) * STOCK.QTY_IN_STOCK

• To multiply the values entered in the transaction references by 10:
  
  StringToInteger(AUDIT_HEADER.INV_REF) * 10

Displays part of a string from a specified starting point.

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

You can use this 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.

When you're returning the remainder of a string, you don't need to enter the number of characters to return.

For example:

• If an invoice order number is ABC123, the following function displays BC123:
  
  Substring(INVOICE.CUST_ORDER_NUMBER ,1)
  
  1 refers to the starting point in the string.

• If an invoice order number is ABC123, the following function displays BC1:
  
  Substring(INVOICE.CUST_ORDER_NUMBER,1,3)
  
  1 refers to the starting point in the string and 3 refers to the number of characters to display.

• You can use a combination of the StringToInteger( ) and Substring( ) functions to sort a report by tax code. The following function converts a tax code into a numeric value, for example, T1 converts to numeric 1:
  
  StringToInteger(Substring(AUDIT_SPLIT.TAX_CODE, 1))

• To calculate the nominal code name length and then display the last 4 characters:
  
  Substring(NOMINAL_LEDGER.NAME, Length(NOMINAL_LEDGER.NAME) - 4)
  
  
• To calculate the sales ledger analysis 1 length, and then display the last 3 characters:
  
  Substring(SALES_LEDGER.ANALYSIS_1,(Length(SALES_LEDGER.ANALYSIS_1) - 3)) 
  
  
• You can use a combination of Substring( ) and Length( ) to ensure a value contains a specific number of characters. The following function adds zeros to the left of a purchase order number, ensuring that the number is always 5 digits long:
  
  Substring("00000" + PURCHASE_ORDER.ORDER_NUMBER, Length(cstring(PURCHASE_ORDER.ORDER_NUMBER))) 
  
  To change the length of the number that appears, increase or decrease the number of zeros in the function.
  
  You can also achieve this using the PadLeft( ) function.
  
  
• The following function shows the first digit of a numeric order number:
  
  Substring(CString(SALES_ORDER.ORDER_NUMBER),1,1)
  
  
• To return the first three characters from the stock code: 
  
  Substring(STOCK.STOCK_CODE,0,3) 
  
  
• Combined with IndexOf to return the text before the space in a customer contact name:
  
  Substring(SALES_LEDGER.CONTACT_NAME,0,IndexOf(SALES_LEDGER.CONTACT_NAME," "))

You can set this within Properties > Function, but you can also use it as a function in expressions.

To ensure the totals are correct, you must check that the Evaluate Section and Reset Section settings are correct on the expression.

To total the customer balances: 

Sum(SALES_LEDGER.ACCOUNT_REF)

To total only the values that meet the condition specified.

• Go to Properties then Function - check this is set to None

To ensure the totals are correct, you must check that the Evaluate Section and Reset Section settings are correct on the expression.

On an aged debtors report, to display the total for sales invoice (SI) transactions only, use the following function:

SumIf(AUDIT_HEADER.AGED_BALANCE, AUDIT_HEADER.TYPE Like "SI")

On the Project Activity report, the following function shows the net total of project transactions with the details 'Project1':

SumIf(Project_Net, PROJECT_ONLY_TRAN.DETAILS Like “Project1”)

To create a summary total at the end of an activity report to show a total per transaction type: 

SumIf(AUDIT_HEADER.NET_AMOUNT, AUDIT_HEADER.TYPE like "SI") 

This shows the total for all SI transactions on the report. You can do the same for each required transaction type.

For example:

To convert a customer's account reference to lower case:

ToLower(SALES_LEDGER.ACCOUNT_REF)

When grouping a report by a text value in Sage Accounts 2011 or below, the software can duplicate groups due to inconsistent capitalisation. For example, when grouping a report by SALES_LEDGER.ANALYSIS_1, values of HELEN, Helen and helen, all create separate groups. You can use the ToLower( ) function in the group expression to stop capitalisation causing extra groups on the report:

ToLower(SALES_LEDGER.ANALYSIS_1)

For example:

To calculate the number of months you've run a report for: 

TotalMonths(CRITERIA.TRAN_DATE_BFCF_FROM,CRITERIA.TRAN_DATE_BFCF_TO)

For example:

To calculate the number of whole months you've run a report for: 

TotalWholeMonths(CRITERIA.TRAN_DATE_BFCF_FROM,CRITERIA.TRAN_DATE_BFCF_TO)

For example:

When printing invoices or credit notes, to convert the words invoice or credit note to upper case:

ToUpper(INVOICE.INVOICE_OR_CREDIT)

For example:

If an invoice order number is 123 B 22, including the spaces, the following function displays the order number as 123B22:

Trim(INVOICE.ORDER_NUMBER)

For example:

If an order number is 1_2_3_, where _ represents a space, the following function displays the order number as 1_2_3:

TrimEnd(INVOICE.ORDER_NUMBER) 

This can be useful if, for example, spaces at the end are causing problems with an If statement.

Removes all the spaces from the beginning of a string.

When you despatch a sales order in Sage Accounts, the invoice that's created contains spaces at the beginning of the sales order number. You can use this function to remove the spaces.

For example:

If an invoice order number is _1_2_3, where _ represents a space, the following function displays the order number as 1_2_3:

TrimStart(INVOICE.ORDER_NUMBER)

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

In the case of positive values, the number rounds down towards zero. For example, 100.30 rounds down to 100.00.

For example:

• To round an invoice value down to the nearest whole number: 
  
  Truncate(INVOICE.INVOICE_GROSS)
  
  
• To round a 5% commission value down to the nearest whole number:
  
  Truncate(AUDIT_SPLIT.NET_AMOUNT * 0.05)

For example:

To show all transaction net values as positive:

Unsigned(AUDIT_HEADER.NET_AMOUNT)

For example:

To return the year from a transaction date: 

YearFromDate(AUDIT_HEADER.DATE)

It's most often used as part of the CreateDateTime function:

CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),
DayFromDate(AUDIT_HEADER.DATE))

If you opened a customer's account 14/02/2010, the following function shows 2010:

YearFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)