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.

Format of a basic If statement

A typical If statement uses the following format:

Condition ? Instruction 1 : Instruction 2

• The ? command is used to mean then.
• The : command is used to mean 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:

• The ? command is used to mean then.
• The : command is used to mean 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 always end the statement with an else instruction to cover when the specified conditions aren't met.

Example

Here 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 analsis type, "Sharon" for any using "South", and "Not Assigned" for any that are not "North" or "South".

Why not use conditional formatting?

Using If statements provides far more flexibility than conditional formatting.

• Groups and sorts - You can group and sort a report based on an If statement.
• Dates, wildcards and functions can be used quickly and easily in If statements
• Ease of use - When constructing If statements, the information can all be entered 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 and because information is simply being hidden, totals cannot be calculated based on conditionally formatted values. If statements can be totalled if required.
• Column based reporting - Another benefit of using If statements is the ability to produce column based reports that you can run for a date range. For further information, please refer to the Common examples tab.

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

[BCB:47:Sales - SEB:ECB]