RepDes Expres
| |
Sage 50 Report Designer Expressions
The Report designer has a lot of functions available to it, and unfortunately
the IF expression is poorly if not wrongly documented and some functions user
will be used to using in products such as Excel, OpenOffice and LibreOffice are
not there!
Not all functions are available in all versions of
Sage Accounts. To check if a function is available in your version open the
designer and in the Expression Editor
window, the Functions pane lists the available functions.
Upgrading your report
designer to the latest version is advised.
Problem and Missing Functions - Functions you may want to find
that are not there and how to do the same thing!
Missing something, want a function you can't find, then make a wish
here. If you have had enough, we can write
reports for you.
Expressions can be nested, to date I have not found a limit to the level of
nesting.
Expressions can be nested, to date I have not found a limit to the level of
nesting.
Left
| Function |
Reason for use |
Other information |
| left(value,integer ) |
Returns the number of characters requested from the left
side of a string
|
|
This function does not exist
Workaround:
The following function returns the same thing as a
left(SALES_LEDGER.ACCOUNT_REF,X) function would
substring(SALES_LEDGER.ACCOUNT_REF,0,length(SALES_LEDGER.ACCOUNT_REF)-X)
|
Right
| Function |
Reason for use |
Other information |
| right(value,integer ) |
Returns the number of characters requested from the right
side of a string
|
|
This function does not exist
Workaround:
The following function returns the same thing as a
right(SALES_LEDGER.ACCOUNT_REF,X) function would
substring(SALES_LEDGER.ACCOUNT_REF,length(SALES_LEDGER.ACCOUNT_REF)-X)
|
Mid
| Function |
Reason for use |
Other information |
| mid(value,integer1,integer2) |
Returns the number of characters requested from the string
starting at integer1 and going for integer2 characters
|
|
This function does not exist
Workaround:
The substring function returns the same thing.
mid(SALES_LEDGER.ACCOUNT_REF,X,Y) function would be
substring(SALES_LEDGER.ACCOUNT_REF,X,Y)
|
Ceiling
| Function |
Reason for use |
Other information |
| Ceiling( ) |
Determines the closest integer that is more than the specified number.
It rounds up
|
|
Example:
The following function returns a customer account balance of £20.11
as £21.00:
Ceiling(SALES_LEDGER.BALANCE)
This is almost the equivalent of Floor(SALES_LEDGER.BALANCE)+1 but
not quite... can you see the difference?
|
Concat
| Function |
Reason for use |
Other information |
| Concat( ) |
To combine two values into one, creating a new value. |
|
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: ",SALES_LEDGER.TELEPHONE)
|
| You can also use 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)))
Note: The properties of the
FormatString
expression
must be set to signed. If not, when running the layout, the
following message appears:
'Sage.expressions.DefaultLibraries.Accounts.Unsigned(system.string)
is not defined.'
|
| I don't use this expresion much, probably
because you can achieve the same thing by appending strings to each
other with the + operand eg CURRENCY.SYMBOL+FormatString("{0:N2}",(INVOICE.FOREIGN_INVOICE_GROSS))
which to my mind reads much more easily. |
|
|
|
|
Contains
| Function |
Reason for use |
Other information |
| Contains( ) |
Searches for characters in a string.
|
| If the statement is true it shows a value of 1, if it's false it
shows 0.
|
| This function is case sensitive. |
|
Example:
| If a customer's fifth address line contains the uppercase letters NE, the following
function returns 1 (TRUE). else it returns 0 (FALSE)
Contains(SALES_LEDGER.ADDRESS_5, "NE")
|
| If the customer's fifth address line contains the uppercase letters NE, the following
function returns the line. If not, it returns an empty string:
Contains(SALES_LEDGER.ADDRESS_5, "NE") ?
(SALES_LEDGER.ADDRESS_5) : "" |
|
Count
| Function |
Reason for use |
Other information |
| Count( ) |
Counts the number of instances on a report. |
| 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(SALES_LEDGER.ACCOUNT_REF )
|
CountIf
| Function |
Reason for use |
Other information |
| CountIf( ) |
Counts the number of instances on a report if the specified conditions
are satisfied. |
| 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(SALES_LEDGER.ACCOUNT_REF Like "%A%" )
|
CountUnique
| Function |
Reason for use |
Other information |
| CountUnique( ) |
Counts the number of unique instances of a specified variable. |
|
Example:
On the Product Profit by Customer - Itemised report, to show the
number of unique products purchased by a customer:
CountUnique(INVOICE_ITEM.STOCK_CODE)
|
Cstring
| Function |
Reason for use |
Other information |
| Cstring( ) |
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.'
|
Examples:
| 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) |
|
DateTimeToFormattedString
| Function |
Reason for use |
Other information |
| DateTimeToFormattedString( ) |
Controls how dates appear. |
| You can use this for grouping reports by date. |
| You can use this in conjunction with If statements on column based
reports. |
| Using this function, you can separate the date values with various
characters, for example / or -. |
| A common use of this function in Sage Accounts is to show dates in
the subject lines of emails. |
|
Examples:
| 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 show the month and year of an invoice date, for example,
September 2011:
DateTimeToFormattedString(INVOICE.INVOICE_DATE, "MMMM yyyy")
|
| To show the company name and invoice date on an email subject
line:
COMPANY.NAME + " " +
DateTimeToFormattedString(INVOICE.INVOICE_DATE, "dd-MM-yyyy") |
|
DayfromDate
| Function |
Reason for use |
Other information |
| DayfromDate( ) |
Returns the day part of a date. |
|
Example:
If a customer's account was opened on 14/02/2001, the following
function shows 14:
DayFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)
I have used this extensively in statement layouts to show conditional
text depending on the ages of the debt (and to calculate the days
overdue)
|
EndsWith
Floor
| Function |
Reason for use |
Other information |
| Floor( ) |
Determines the closest integer that is less than the specified number.
It rounds down.
|
You can use this to display pounds when displaying pounds and pence
separately.
|
Examples:
| The following function displays a customer account balance of £75.85
as £75.00:
Floor(SALES_LEDGER.BALANCE)
|
| The following function displays the pounds element of an invoice
total:
Floor(unsigned(INVOICE.FOREIGN_INVOICE_GROSS))
Tip: To display the pence element, the following expression
subtracts the Floor value from the original value:
INVOICE.FOREIGN_INVOICE_GROSS -
Floor(unsigned(INVOICE.FOREIGN_INVOICE_GROSS)) |
|
If
| Function |
Reason for use |
Other information |
| If( ) |
To display certain values depending on the condition specified. |
| 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. |
|
|
This function is badly documented: The function does exist and is
used in conditional formating espressions, however for normal if
statements you need to use the form
<Expression>?<expression to use if true>:<expression
to use if false>
The IF is implied, the ? is THEN and : is ELSE
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,
please refer to article 17511.
Note: that in this explanation of the function, they do not use the
function!
|
|
|
|
Indexof
| Function |
Reason for use |
Other information |
| Indexof( ) |
Finds the position of a particular character in a string. |
This can be combined with the Substring function to only return the
value of a field up to a certain character. |
Examples:
| 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 first name held in the SALES_LEDGER.CONTACT_NAME as
follows:
Substring(SALES_LEDGER.CONTACT_NAME,0,IndexOf(SALES_LEDGER.CONTACT_NAME,"
"))
|
| Alternatively, to show only the surname of a customer's contact
name:
Substring(SALES_LEDGER.CONTACT_NAME, (IndexOf(SALES_LEDGER.CONTACT_NAME,"
"))) |
|
Length
| Function |
Reason for use |
Other information |
| Length( ) |
Displays how many characters exist in a string. |
|
Example:
To display how many characters appear in a customer's account
reference:
Length(SALES_LEDGER.ACCOUNT_REF)
|
Max
| Function |
Reason for use |
Other information |
| Max( ) |
Displays the maximum value in a sequence. |
| 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 net amount on a transaction report:
Max(AUDIT_HEADER.NET_AMOUNT)
|
Min
| Function |
Reason for use |
Other information |
| Min( ) |
Displays the minimum value in a sequence. |
| 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 net amount on a transaction report:
Min(AUDIT_HEADER.NET_AMOUNT)
|
MonthfromDate
| Function |
Reason for use |
Other information |
| MonthfromDate( ) |
Returns the month part of a date. |
|
Example:
If a customer's account was opened on 14/02/2001, the following
function shows 2:
MonthFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)
|
Now
PadLeft
| Function |
Reason for use |
Other information |
| PadLeft( ) |
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)
|
When using this function with a numeric field, you must also use
CString to convert the numeric value to an alphanumeric string. |
Example:
| On an invoice layout, to always display the invoice number as a 6
digit number, 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.
|
| On a product report, to always show stock codes with 10
characters, use the following function:
PadLeft(STOCK.STOCK_CODE,"0",10) |
|
PadRight
| Function |
Reason for use |
Other information |
| PadRight( ) |
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)
|
When using this function with a numeric field, you must also use
CString to convert the numeric value to an alphanumeric string. |
Example:
| On a product report, to always show stock codes with 10
characters, use the following function:
PadRight(STOCK.STOCK_CODE,"0",10) |
|
ParseDateTime
| Function |
Reason for use |
Other information |
| ParseDateTime( ) |
Converts alphanumeric fields to date fields for use in expressions and
filters. |
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:
To convert the first line within Invoice List > New /Edit >
Order Details > Notes to a date field in a report:
ParseDateTime(INVOICE.NOTES_1)
|
Previous
| Function |
Reason for use |
Other information |
| Previous( ) |
To change a value based on the previous line value. |
You can use this with If statements. |
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 + " *"
|
Remove
| Function |
Reason for use |
Other information |
| Remove( ) |
Removes a specified number of characters from a stated starting point
in the string. |
You must always enter the starting position, where the first character
is 0, and the number of characters to remove. |
Examples:
| 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) |
|
Replace
| Function |
Reason for use |
Other information |
| Replace( ) |
Replaces the specified character in a string with another character.
|
This function is case sensitive. |
Examples:
| 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") |
|
RoundDP
| Function |
Reason for use |
Other information |
| RoundDP( ) |
Specifies the number of decimal places that a variable rounds to. |
You can use this to avoid slight rounding differences between Sage
Accounts and Report Designer. |
Examples:
| 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) |
|
StartsWith
| Function |
Reason for use |
Other information |
| StartsWith( ) |
Displays whether or not a string starts 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 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)
: "" |
|
StringToFloat
| Function |
Reason for use |
Other information |
| StringToFloat( ) |
Converts alphanumeric fields to numeric fields for use in expressions
and filters, when the fields contain decimal numbers.
|
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 > Location:
StringToFloat(STOCK.LOCATION) * STOCK.QTY_IN_STOCK
|
| To multiply values entered in the transaction references by 10:
StringToFloat(AUDIT_HEADER.INV_REF) * 10 |
|
StringToInteger
| Function |
Reason for use |
Other information |
| StringToInteger( ) |
Converts alphanumeric fields to numeric fields for use in expressions
and filters, when the fields contain whole numbers. |
If you run the report 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 > Location:
StringToInteger(STOCK.LOCATION) * STOCK.QTY_IN_STOCK
|
| To multiply values entered in the transaction references by 10:
StringToInteger(AUDIT_HEADER.INV_REF) * 10 |
|
Substring
| Function |
Reason for use |
Other information |
| Substring( ) |
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. |
|
Examples:
| 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)
|
| You can use a combination of Substring( ) and Length( ) to ensure
a value contains a certain 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.
Tip: 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)
|
|
|
|
|
SumIf
| Function |
Reason for use |
Other information |
| SumIf( ) |
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)
|
The Properties > Function should be set to None. |
Example:
| 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") |
|
ToLower
| Function |
Reason for use |
Other information |
| ToLower( ) |
Converts the field to lower case.
|
|
Example:
To convert a customer's account reference to lower case:
ToLower(SALES_LEDGER.ACCOUNT_REF)
|
ToUpper
| Function |
Reason for use |
Other information |
| ToUpper( ) |
Converts the field to upper case. |
|
Example:
When printing invoices or credit notes, to convert the words invoice
or credit note to upper case:
ToUpper(INVOICE.INVOICE_OR_CREDIT)
|
Trim
| Function |
Reason for use |
Other information |
| Trim( ) |
Removes any spaces from a string. |
|
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)
|
TrimEnd
| Function |
Reason for use |
Other information |
| TrimEnd( ) |
Removes all the spaces from the end of a string. |
|
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)
|
TrimStart
| Function |
Reason for use |
Other information |
| TrimStart( ) |
Removes all the spaces from the beginning of a string. |
|
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)
|
Truncate
| Function |
Reason for use |
Other information |
| Truncate( ) |
Rounds a value to the nearest whole number in the direction of zero.
|
| 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 down to the nearest whole value, use
the following function:
Truncate(AUDIT_SPLIT.NET_AMOUNT * 0.05) |
|
YearfromDate
| Function |
Reason for use |
Other information |
| YearfromDate( ) |
Returns the year part of a date. |
|
Example:
If a customer's account was opened on 14/02/2001, the following
function shows 2001:
YearFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)
|
|
