Report Designer - Using if statements in the Expression Editor

How to use if statements in Report Designer for Sage 50 Accounts and Sage 50 Payroll.

An if statement checks it meets a condition and performs an action accordingly. If it doesn't meet the condition, it performs a different action.

You can use an if statement to return specific values based on the condition.

Format of a basic if statement

A typical if statement uses the following format:

Condition ? Instruction 1 : Instruction 2

• ? command means then
• : command means else

The expression is therefore saying:

If the condition is true THEN apply instruction 1, ELSE apply instruction 2.

When constructing the if statement, you must always include at least one condition, an instruction 1 and an instruction 2.

---

Format of a nested if statement

You can specify more than one condition with different results within one expression. This is a nested if statement.

Nested if statements use the following format:

Condition 1 ? Instruction 1 : Condition 2 ? Instruction 2 : Instruction 3

where:

• ? command means then
• : command means else

The expression is therefore saying:

If condition 1 is true THEN apply instruction 1, ELSE if condition 2 is true THEN apply instruction 2, ELSE apply instruction 3.

When constructing a nested if statement, you must end the statement with an else instruction to cover when it doesn't meet the specified conditions.

---

Example

Here's is an example using the Sales Ledger Analysis Type field: 

SALES_LEDGER.ANALSYSIS1 LIKE "NORTH" ? "Michael" : SALES_LEDGER.ANALSYSIS1 LIKE "SOUTH" ? "Sharon" : "Not Assigned"

The above statement will return "Michael" for all items in the search using "North" as the sales analysis type. "Sharon" for any using "South", and "Not Assigned" for any that aren't "North" or "South".

---

Why not use conditional formatting?

Using if statements provide far more flexibility than conditional formatting.

• Groups and sorts - You can group and sort a report based on an if statement
• Use dates, wildcards and functions in if statements
• Ease of use - When constructing if statements, you can enter all the information in one expression
• You don't need to set up properties on several objects as is sometimes necessary with conditional formatting
• Custom totals - Conditional formatting simply hides or formats information, it can't calculate totals based on conditionally formatted values
• You can total if statements
• Column based reporting - using if statements can produce column based reports that you can run for a date range

If you're looking to format your expression, for example make the colour red when it meets a condition, you need to use conditional formatting.

---