Conditional Formatting

Conditional Formatting

Any report item property that accepts an expression can be made dynamic. The most common use cases are highlighting rows based on data values, alternating row background colours, and showing or hiding items based on conditions.

Applying an expression to a property

1. Select a text box, table row, or other item in the designer.
2. In the Properties panel, click the property field you want to make conditional (e.g. BackgroundColor).
3. Click the small expression button (the fx icon or the ... button next to the field).
4. Enter an expression beginning with =.

Background colour based on a value

Highlight rows where a numeric value exceeds a threshold:

=IIf({Variance} < 0, "LightCoral", "Transparent")

Three-tier colour coding:

=Switch(
    {Score} >= 90, "LightGreen",
    {Score} >= 60, "LightYellow",
    True,          "LightCoral"
)

Colours can be any named HTML/CSS colour (Red, LightBlue, #FF6347) or Transparent.

Alternating row colours (banded rows)

Use RowNumber in the detail row's BackgroundColor expression:

=IIf(RowNumber(Nothing) Mod 2 = 0, "#F0F4F8", "White")

RowNumber(Nothing) counts rows from 1 in the innermost scope. Pass a group name string to reset the count per group:

=IIf(RowNumber("CategoryGroup") Mod 2 = 0, "#F0F4F8", "White")

Font colour and weight

Bold overdue items and colour them red:

' Color property
=IIf({DueDate} < Today() And {Status} <> "Closed", "Red", "Black")

' FontWeight property
=IIf({DueDate} < Today() And {Status} <> "Closed", "Bold", "Normal")

Showing and hiding items

The Hidden property on any report item accepts a Boolean expression. True hides the item; False shows it.

' Hide a text box when the value is null or empty
=IIf(IsNothing({Notes}) Or {Notes} = "", True, False)

' Simplified — direct Boolean expression
=IsNothing({Notes})

' Show a "Total" row only on the last page
=IIf({@PageNumber} < {@TotalPages}, True, False)

To show or hide an entire table row, set the Hidden property on the TableRow, not on individual cells.

Visibility toggle (drill-down)

To make a detail section collapsible (show/hide on click):

1. Select the detail row or sub-section.
2. Set its Hidden expression to True (hidden by default).
3. Set ToggleItem to the name of the text box that should act as the toggle trigger.
4. The text box gains a +/− expand icon at runtime.

Conditional borders

Change a cell's border colour based on data:

' BorderColor property on a specific side (e.g. BorderColorLeft)
=IIf({Status} = "Overdue", "Red", "Silver")

Conditional text content

Show different text depending on a value:

=Switch(
    {OrderStatus} = "S", "Shipped",
    {OrderStatus} = "P", "Pending",
    {OrderStatus} = "C", "Cancelled",
    True,                "Unknown"
)

Or use a formatted representation:

=IIf({Amount} >= 0,
    "+" & Format({Amount}, "C2"),
    Format({Amount}, "C2"))

Tips

• IIf evaluates both branches even when a branch won't be returned. If either branch can throw (e.g. division by zero, null access), guard it first with IsNothing or a null check.
• For more than two conditions, Switch is cleaner than nested IIf calls.
• Named colours (LightGreen, Coral, SteelBlue) are generally more readable in expressions than hex codes.
• Test conditional formatting in the Preview tab to see realistic data before exporting.

See also Expressions and Formulas for the full expression reference.