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.

✅ Rule: If you’re doing math, date-diffs, conditional display, or VAR_Row, use Formula (No Format) unless you specifically need formatted output.

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).

✅ Rule: For “today”, use Report Run Time (or Report Start/End Date if appropriate).

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

✅ Rule: If you’re combining fields, formatting dates safely, or building MP-friendly logic, create hidden columns first and then read them with VAR_Row.

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.

✅ Rule: If VAR_Row returns blank, the first thing to check is the column name string.

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

Blank-date guard pattern (common for “Last On Cover”)

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).

✅ Rule: If a date might not exist for all rows, use hidden column + VAR_Row.GetDate() + a guard condition.

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

When comparing against a literal date in reports:

✅ Rule: Hardcoded dates should always be #yyyy-MM-dd#.

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.

✅ Rule: If you need “last year’s rate”, you usually have to derive it (premium ÷ sum insured) rather than pulling a _RateBefore.

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:

✅ Rule: For adjustment reports, always branch by Record Type (and often Record Status too).

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:

✅ Rule: If a system date field causes errors inside a formula, move it into a hidden column and then pull via VAR_Row.

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:

✅ Rule: Always guard optional text fields before concatenating to avoid “dangling separators”.

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(...).

✅ Rule: For MP reporting, design with hidden columns + Copy Row + VAR_Row from the start.

12) Use numeric getters for calculations (GetDecimal)

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

Correct:

Incorrect (string concatenation risk):

✅ Rule: Use GetDecimal for maths, GetValue for text.

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

Reports can compare a report-column numeric to a SchemeServe field:

✅ Rule: Use MAX/MIN to enforce caps/floors or pick the higher/lower of two values (just ensure both are numeric).

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

Common patterns:

Two decimal places:

Add percent sign:

Currency:

✅ Rule: Do numeric work first, then apply .ToString(...), then add symbols (%, etc.).

Quick “before you save” checklist ✅ (Reports)

• Correct column type: Formula vs Formula (No Format)
•  If using VAR_Row, do column names match exactly?
• Using GetDecimal for maths and GetDate for dates?
• Date formatting applied via .ToString(...)?
• Date fields that can be blank are guarded to avoid report errors?
• Adjustments handled via Record Type / Record Status branching?
• MP logic uses hidden columns + Copy Row where needed?
• Not relying on unsupported items (e.g., [NOW], _RateBefore, some date headers inside formulas without hidden columns)?