Skip to content
logo Knowledgebase

Report Designer Expressions and Functions

Created on  | Last modified on 

Summary

This guide explains how to use expressions and functions, which can be used in Report Designer in Sage 50 Accounts and Sage 50 Payroll.

Description

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

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

Let's take a look at how to use them.

Resolution

Expressions

When a report is generated, each expression is calculated to produce a value. This value may 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 is more than one possible result

Build an expression

  1. Select the document you want to amend, then click Edit.
  2. On the menu bar click Report, click Report Properties.
  3. Enter a new report name and description, then click OK.
  4. On the menu bar, click Toolbox. Then click Add Expression.
  5. Click the area of the document where you want the new expression to appear.
  6. Use our format rules to create your own expression, or use one of the common expressions for Sage 50 Accounts or Sage 50 Payroll.
  7. Click OK.
  8. On the menu bar click File, click Save As.
  9. Enter a new file name then click Save.
  10. On the menu bar click File, then click Exit.

Common Expressions

We've gathered together the most common expressions for both Sage 50 Accounts and Sage 50 Payroll to help you quickly add expressions.

We've also gathered together common date expressions. For example, the age of a transaction or the first day of the financial year.


Snippets

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


Functions

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

You can use the following functions and operators to help to build expressions.

GeneralShow averages, If statements, totals.
DateShow the number of days in the month, convert alphanumeric fields into date fields.
SignShow the Debit or Credit values. You can also show all values as positive, or as the reverse of their original sign.
StringConvert a numeric field to an alphanumeric string, remove all unused spaces in a variable.
ValueRound up or down the decimal places.

If statements

An If statement is an expression that is used on a report to say if a certain condition is met then do one thing, else if the condition is not met do something else. You can use an If statement to return certain values depending on the condition specified.



Image

Simple, automated and all in one place
Manage your employees’ taxable benefits easily and accurately, with online submissions to HMRC. The deadline for P11D submission is 6 July 2024. Want to know more?

Leave your details and we'll be in touch