Report Designer expressions and functions

Summary

How to use expressions and functions to use in Report Designer for Sage 50 Accounts and Sage 50 Payroll.

Description

An expression guides Report Designer on what information to include in a report.

A function is a predefined formula that performs calculations using specific values in a particular order.

Resolution

Expressions

When you generate a report, each expression calculates to produce a value. This value can come directly from the database, or be the result of further calculations on the information in the database.

An expression is a formula, ▼ which can include:

Numbers
Text
Variables, which are your data names and the table it belongs, such as REPORT.PAGE
Functions, used for complex mathematical processing such as string functions
Operators that can be words such as NOT or LIKE and symbols such as less than <, greater than >, or equal to =. They include wildcards such as %, which replace single or multiple characters and are used to search for values where there's more than one possible result

Build an expression

Select the document you want to amend, then click Edit. For example:
- Sage 50 Payroll: Go to Payroll, then Reports
- Sage 50 Accounts: Go to Invoices and credits, then Reports
On the menu bar click Report, then Report Properties.
Enter a new report name and description, then select OK.
On the menu bar, click Toolbox, then Add Expression.
Select the area of the document where you want the new expression to appear.
Use the formatting rules when building expressions to create your own expression. Or use one of the common expressions for Sage 50 Accounts or Sage 50 Payroll.
Click OK.
On the menu bar click File, then Save As.
Enter a new file name then select Save.
On the menu bar click File, then Exit.

Common Expressions

We've gathered the most common expressions to help you add expressions, for both:

Common expressions for Sage 50 Accounts reports
Sage 50 Payroll common expressions

We've also gathered using dates in expressions on Sage Accounts reports. For example, the age of a transaction or the first day of the financial year.

Snippets

The Expression Editor in Sage Report Designer - Snippets contains a feature you can use to save common expressions and calculations to use in future expressions and filters.

Functions

A Report Designer - Function is a predefined formula that performs calculations using specific values in a particular order.

To help to build expressions, you can use the following ▼ functions and operators:

General	Show averages, If statements, totals.
Date	Show the number of days in the month, convert alphanumeric fields into date fields.
Sign	Show the Debit or Credit values. You can also show all values as positive, or as the reverse of their original sign.
String	Convert a numeric field to an alphanumeric string, remove all unused spaces in a variable.
Value	Round up or down the decimal places.

If statements

In Report Designer - Using If statements in the Expression Editor are being used on a report to say if a certain condition is met. If a condition is met, perform one action; otherwise, perform a different action. You can use an If statement to return certain values depending on the condition specified.

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.

Leave your details