Report Designer - Common expressions for Sage 50 Accounts reports

Summary

Using Sage Report Designer you can add expressions to reports and other documents to calculate values. This article details some of the most common expressions in Sage 50 Accounts.

Resolution

TIP: We have a great help guide on creating expressions. Read more >

Customer and Supplier expressions

VAT element of the outstanding balance	AUDIT_SPLIT.FOREIGN_AGED_BALANCE - (( AUDIT_SPLIT.FOREIGN_AGED_BALANCE * AUDIT_SPLIT.FOREIGN_NET_AMOUNT) / AUDIT_SPLIT.FOREIGN_GROSS_AMOUNT )
Net amount of a paid invoice	Sum((1-(AUDIT_SPLIT.TAX_AMOUNT / AUDIT_SPLIT.GROSS_AMOUNT ) ) * AUDIT_USAGE.AMOUNT)
Agents commission on the net value of an invoice	Sum((1-(AUDIT_SPLIT.TAX_AMOUNT / AUDIT_SPLIT.GROSS_AMOUNT) ) * AUDIT_USAGE.AMOUNT) * 0.10
Proportion of the aged balance that is made up of SI transactions	SumIf(AUDIT_HEADER.AGED_BALANCE, AUDIT_HEADER.TYPE Like "SI")
First name from the customer contact name	Substring(SALES_LEDGER.CONTACT_NAME,0, IndexOf(SALES_LEDGER.CONTACT_NAME, " "))
Surname from the customer contact name	Substring(SALES_LEDGER.CONTACT_NAME, IndexOf(SALES_LEDGER.CONTACT_NAME," "), (Length(SALES_LEDGER.CONTACT_NAME) - IndexOf(SALES_LEDGER.CONTACT_NAME," ")))

Invoicing expressions

Show the invoice payment due date based on a fixed number of days.	INVOICE.INVOICE_DATE + Days(30)
Show the invoice payment due date based on the terms from the customer record	INVOICE.INVOICE_DATE + Days(SALES_LEDGER.PAYMENT_DUE_DAYS)
Unit price after discount	INVOICE_ITEM.FOREIGN_NET_AMOUNT / INVOICE_ITEM.QUANTITY
Gross unit price	INVOICE_ITEM.FOREIGN_GROSS_AMOUNT / INVOICE_ITEM.QUANTITY
Discount percentage if the product sales price is reduced on the invoice	((STOCK.SALES_PRICE - INVOICE_ITEM.UNIT_PRICE) / STOCK.SALES_PRICE) * 100
Add country code prefix to VAT registration numbers	SALES_LEDGER.COUNTRY_CODE + " " + SALES_LEDGER.VAT_REG_NUMBER

Product expressions

Show the % markup of the cost value	100 - (STOCK.LAST_PURCHASE_PRICE / STOCK.SALES_PRICE * 100) or (1 - (STOCK.LAST_PURCHASE_PRICE / STOCK.SALES_PRICE)) * 100
Free stock	STOCK.QTY_IN_STOCK - STOCK.QTY_ALLOCATED
Profit margin	((STOCK.SALES_PRICE - STOCK.LAST_PURCHASE_PRICE) / STOCK.SALES_PRICE) * 100

Profit expressions

Show the % markup of the cost value	100 - (STOCK.LAST_PURCHASE_PRICE / STOCK.SALES_PRICE * 100) or (1 - (STOCK.LAST_PURCHASE_PRICE / STOCK.SALES_PRICE)) * 100
Free stock	STOCK.QTY_IN_STOCK - STOCK.QTY_ALLOCATED

Purchase order expressions

Unit price after discount	POP_ITEM.FOREIGN_NET_AMOUNT / POP_ITEM.QTY_ORDER
Gross unit price	POP_ITEM.FOREIGN_GROSS_AMOUNT / POP_ITEM.QTY_ORDERED

Sales order expressions

Unit price after discount	SOP_ITEM.FOREIGN_NET_AMOUNT / SOP_ITEM.QTY_ORDER
Gross unit price	SOP_ITEM.FOREIGN_GROSS_AMOUNT / SOP_ITEM.QUANTITY

Upgrade your licence

Growing business? Add more companies, users, or employees to your licence with ease. Leave your details and we’ll be in touch.