Below are the general rules for building formulas in SchemeServe Reports, in the same “rules + do/don’t + patterns” style as rating files and documents. Reporting is its own engine, so some things that work in rating/docs either don’t exist or need a different approach.

General rules for SchemeServe reporting formulas

1) Choose the correct column type first

Reporting formulas behave differently depending on the column type:

Column Header (no formula): safest, but limited formatting.
Formula: supports formatting; some fields return date/time differently here.
Formula (No Format): best for raw calculations (days, math, IF logic), and avoids some formatting conflicts.

2) Reports don’t support “NOW” the same way

[NOW] generally doesn’t work in reports.
Use:
[Report Run Time] when you need “today/now” inside a report context.

Some date headers also exist (e.g., Report Start Date / Report End Date).

3) Know when you must use VAR_Row

In reports, a formula can:
Reference system IDs directly: [RiskAddress_Postcode_Value]
Or reference values from earlier columns using VAR_Row

Use VAR_Row when:
You need to use values from other columns (especially hidden columns),
You’re working with MP-derived columns that aren’t reliable as direct system IDs,
You need .GetDecimal() / .GetDate() to force correct data typing.

Common patterns:
VAR_Row.GetValue("Column Name") → returns text
VAR_Row.GetDecimal("Column Name") → returns number
VAR_Row.GetDate("Column Name") → returns date

4) VAR_Row column names must match exactly

When referencing a column:
the name must match exact spelling, spaces, and casing used in the report column title.

5) Date fields: format them explicitly (and guard blanks)

Reports often return dates with time, or error if a date is missing in the dataset.

Formatting example
[Record Inception Date].ToString("dd/MM/yyyy")

Blank-date guard pattern (common for “Last On Cover”)
IF([VAR_Row].GetDate("Record Last On Cover").Year > 1,   [VAR_Row].GetDate("Record Last On Cover").ToString("dd/MM/yyyy"),   "")

Why this matters:
Some reports error if you reference a date field that may be empty for some returned rows (e.g., records never on cover).

6) Fixed date comparisons use #YYYY-MM-DD#

When comparing against a literal date in reports:
IF([Policy Inception] < #2019-04-01#, "Old Ref", "New Ref")

7) Understand which “previous values” exist in reports

Reporting supports:
_CoverBefore / _ResultBefore only for calculation questions (because those store previous values).
There is no _RateBefore in reporting.

8) Adjustment reporting needs explicit logic

A common trap: adding _CoverBefore to _Cover works for MTAs but breaks renewals unless you gate it.

Safe pattern:
IF([Record Status]="Cancelled", 0,   IF([Record Type]="Adjustment",      ([CalcQuestionID_CoverBefore] + [CalcQuestionID_Cover]),      [CalcQuestionID_Cover]   ) )

9) Cancelled / On Cover logic often needs hidden columns

Some “special” report fields can’t be safely used inside formulas (or error out depending on report type), so the workaround is:

Add hidden column(s) using the standard header (e.g., Record Status, Record Last Cancelled)
Use VAR_Row.GetValue/GetDate in the visible formula column

Example pattern:
IF(VAR_Row.GetValue("RecordStatus")="Cancelled",   VAR_Row.GetDate("RecordLastCancelled").ToString("dd/MM/yyyy"),   "")

10) Use the right string functions

Reports use the same style of text operations you’ve seen elsewhere:

.Substring(start, length)
.Replace(old, new)
.ToUpper()
.Length
.Contains("text")
+ to concatenate

Example: initials:
[Client First Name].Substring(0,1) + [Client Surname].Substring(0,1)

11) Multi-Page (MP) is different: often requires hidden columns + copy row

Key MP rules:

Some MP column headers “copy down” automatically; [MP_RowCount] does not (you must tick Copy Row if you need it for calculations).
Many “single-line” formula patterns cannot be used directly with MP system IDs; instead:
Pull each MP element into hidden columns,
Combine them with VAR_Row.GetValue(...).

12) Use numeric getters for calculations (GetDecimal)

If you do maths on VAR_Row.GetValue(...), you risk treating numbers as strings.

Correct:
(VAR_Row.GetDecimal("Column A")) + (VAR_Row.GetDecimal("Column B"))

Incorrect (string concatenation risk):
(VAR_Row.GetValue("Column A")) + (VAR_Row.GetValue("Column B"))

13) MAX/MIN patterns in reports (including VAR_Row)

Reports can compare a report-column numeric to a SchemeServe field:
MAX(VAR_Row.GetDecimal("MD Excess"), [FloodExcess_Cover])

14) Formatting numbers for export (percent, decimals, currency)

Common patterns:

Two decimal places:
[IPT_Rate].ToString("0.00")

Add percent sign:
([IPT_Rate].ToString("0.00")) + "%"

Currency:
[Premium Total £ (1 Year)].ToString("N2")