# Computed / Derived Fields — Feature Guide

**Status as of 2026-09-02:** backend complete, live-verified on GB5DEMO for both the fixed
report catalog and the BI Platform. **No frontend code was changed** — see §1 for exactly what
that does and doesn't mean in practice.

**Audience:** this doc has a section for report/view developers adding a computed field to a
fixed AccountReports-style report, and a section for whoever manages BI Platform dataset field
mappings. Read §2 (concepts) first regardless of which one you are.

---

## 1. Is this a backend-only change?

Yes. Every file touched is `.cs` or `.sql` — no Angular/TypeScript file was edited.

That works because a computed field is deliberately made to look **exactly like an ordinary
field** to everything downstream of it:

- **Fixed reports:** a computed field gets a normal `MREPORTVSFIELDS` row and a normal
  `MREPORTVIEWFIELDS` row — the same two tables every physical, SQL-returned field already has
  one of. The report-viewer's field-catalog endpoints (`GetReportViewField`,
  `GetReportViewWithFields`) return it identically to any other column: same `FieldTitle`,
  `FieldType`, `CalFormat`, `Alignment`, `DisplaySlNo`. The actual **value** now also shows up as
  an ordinary extra key in the JSON row data (grid) and in every export (Excel/CSV/PDF/HTML). A
  frontend grid/export renderer that already reads its column list from the field catalog (which
  `gb-reportviewer` does) needs no code change to display it.
- **BI Platform:** a computed field gets a normal `MBIFIELDMAPPING` row (just tagged
  `MAPPEDAS = 2` instead of `0`/`1`) and shows up as an ordinary extra key in `RunBIQuery`'s
  `Data` rows, with its own entry in `Meta.ComputedFieldsUsed`.

**What this claim does *not* cover** — flagging honestly, not verified this pass:
- No one has actually opened `gb-reportviewer` or a BI dashboard widget in a browser and looked
  at the rendered column. The backend contract is exactly what the FE already consumes for every
  other field, so it's expected to render with zero FE changes — but "expected" is not "clicked
  through," and this is the same category of gap already tracked for this whole report family.
- If a specific frontend screen hardcodes its column list instead of reading it from the field
  catalog (some do, for report-family-specific grids), that screen won't pick up a new computed
  field without its own change. That's a pre-existing FE pattern issue, not something this
  feature introduces.
- The BI Platform's computed field is **excluded from the Dimension/Measure pickers** on purpose
  (see §2) — a picker screen that lets someone browse "available measures" won't show it. It has
  its own picker endpoint (`GET /BI/GetComputedFieldsForDataset`) that a screen would need to call
  if it wants to let someone pick a computed field explicitly.

---

## 2. What a computed field is, and the two places it lives

A computed field is a field whose value is **not** returned by the report's SQL/DAL query, but
derived from other fields the query *does* return, via a small expression evaluated once per row.

```
Open       = ABS(OpeningBalance)
OpenDC     = CASE WHEN OpeningBalance > 0 THEN 'D' ELSE 'C' END
TextStatus = CASE Status WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' ELSE 'Cancelled' END
```

It exists in two, independently-configured places, sharing one evaluation engine:

| | Fixed reports | BI Platform |
|---|---|---|
| Where it's defined | `MREPORTVSFIELDS.COMPUTEEXPRESSION` | `MBIFIELDMAPPING.COMPUTEEXPRESSION` |
| How it's marked | Any non-empty `COMPUTEEXPRESSION` | `MAPPEDAS = 2` (0=Dimension, 1=Measure) |
| Scoped to | One `REPORTID`, wired onto a view via a normal `MREPORTVIEWFIELDS` row | One `BICATALOGID` (dataset) |
| Applies to | AccountRegister/Ledger/Voucher-style reports (`BaseReportEndpoint<TRequest,TData>`) | Ad-hoc `RunBIQuery` datasets (Warehouse/AnalysisQuery/ApiService) |
| Shows up in | The on-screen grid JSON **and** every export format | `RunBIQuery`'s `Data` + `Meta.ComputedFieldsUsed` |

Both use the exact same evaluation engine — `System.Data.DataColumn.Expression`, the same
mechanism already used elsewhere in this codebase for Ratio Analysis and KPI formulas, just
generalized to run once per row instead of once per dashboard tile. It's a real .NET API, not a
new query language: no SQL text is ever built from it, so there's no injection surface, and a
broken expression can never fail the whole report (see §5).

### Expression syntax — what you can actually write

`DataColumn.Expression` officially documents six functions: **`Len`, `IsNull`, `IIf`, `Convert`,
`Trim`, `Substring`** — plus ordinary arithmetic (`+ - * /`), comparison (`= <> > < >= <=`),
boolean (`AND OR NOT`), and string literals in single quotes.

**There is no `Abs()` and no SQL `CASE WHEN...END`.** Both are common enough that they're worth
calling out explicitly:

| You want | Don't write | Write instead |
|---|---|---|
| Absolute value | `Abs(OpeningBalance)` | `IIf(OpeningBalance < 0, -OpeningBalance, OpeningBalance)` |
| Two-way branch | `CASE WHEN X > 0 THEN 'D' ELSE 'C' END` | `IIf(X > 0, 'D', 'C')` |
| Multi-way branch | `CASE Status WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' ELSE 'Cancelled' END` | `IIf(Status = 0, 'Pending', IIf(Status = 1, 'Active', 'Cancelled'))` — nest `IIf` per extra branch |
| Null-safe compare | `X > 0` where X can be null | `IIf(IsNull(X, 0) > 0, 'D', 'C')` — a null flowing into `IIf`'s condition evaluates false silently, so guard it explicitly if the source field can be null |

If you write `CASE WHEN...` for a fixed-report field, the engine detects the literal `CASE`
keyword and rejects it at evaluation time with a message pointing you at the `IIf` form (§5). The
BI Platform additionally **rejects the expression at save time**, not just at query time — see
§4.

---

## 3. Report developers / view-setting members — fixed reports

**Who this is for:** anyone adding a field to an AccountRegister/Ledger/Voucher-family report.
**How:** today, by migration — there is no live admin screen yet (flagged as the natural next
step, not built this pass; see §6). This matches how every other field on these reports has
always been added.

### Step-by-step

1. **Confirm the source fields your expression needs are already returned by the report's DTO.**
   A computed field can only reference a field the report's own SQL/DAL already produces —
   nothing is fetched on its behalf. Check the DTO class (e.g.
   `AccountsDAL/DTO/AccountsReports/AccountLedgerDetailReportDTO.cs`) for the exact property
   names; those are the identifiers your expression uses.

2. **Add one `MREPORTVSFIELDS` row** — the field's report-wide definition. Pick a `FIELDNAME`
   that doesn't collide with an existing property on the DTO (the evaluator refuses to overwrite
   a real value — see §5), a `FIELDTYPE` matching what your expression returns (`0` Int, `1`
   String, `2` Long, `3` Date, `4` Boolean, `6` Double/Decimal), and the `COMPUTEEXPRESSION` text.

   ```sql
   INSERT INTO MREPORTVSFIELDS
       (REPORTVSFIELDSID, REPORTID, SLNO, FIELDNAME, FIELDTITLE, FIELDTYPE, FIELDWIDTH, ISDISPLAY,
        ISMERGECOLUMN, ISGROUPCOLUMN, ISSUBTOTAL, ISGRANDTOTAL, CALFORMAT, DISPLAYSLNO, ALIGNMENT,
        SOURCETYPE, OBJECTFIELDTYPE, DIMENSIONFIELDID, COMPUTEEXPRESSION)
   VALUES
       (<new id>, <REPORTID>, <next SLNO>, 'Open', 'Open (Abs)', 6, 130, 1, 0, 0, 0, 0, 'N2',
        <next DISPLAYSLNO>, 2, 2, 1, -1,
        'IIf(AccountOpeningBalance < 0, -AccountOpeningBalance, AccountOpeningBalance)');
   ```

3. **Add one `MREPORTVIEWFIELDS` row per view** you want the field to appear on — the normal
   per-view display config (title, width, order, format), pointing at the `REPORTVSFIELDSID` from
   step 2. A computed field with no `MREPORTVIEWFIELDS` row is defined but invisible everywhere —
   this is what makes it opt-in per view, not automatic for the whole report.

   ```sql
   INSERT INTO MREPORTVIEWFIELDS
       (REPORTVIEWFIELDSID, REPORTID, REPORTVIEWID, REPORTVSFIELDSID, SLNO, FIELDTITLE, FIELDWIDTH,
        ISDISPLAY, ISMERGECOLUMN, ISGROUPCOLUMN, ISSUBTOTAL, ISGRANDTOTAL, CALFORMAT, DISPLAYSLNO,
        ALIGNMENT, DISPLAYTYPE, AGGREGATIONTYPE, ISCOLUMNTOTAL, ISROWTOTAL, PIVOTLEVEL,
        SUPPRESSIFEMPTY, DIMENSIONFIELDID)
   VALUES
       (<new id>, <REPORTID>, <REPORTVIEWID>, <REPORTVSFIELDSID from step 2>, <next SLNO>,
        'Open (Abs)', 130, 0, 0, 0, 0, 0, 'N2', <next DISPLAYSLNO>, 2, 0, 0, 0, 0, 1, 0, -1);
   ```

   `ISDISPLAY = 0` means **shown** (0=ON/1=OFF — the inverted convention this table has always
   used, confirmed live; double-check your view's other rows use the same convention before
   assuming, since at least one view was found seeded backwards — see the pilot migration's own
   note for that specific incident).

4. **Verify**, in this order:
   - Call the report's normal endpoint (no `ReportFormat` header) and confirm the new field name
     appears as a key in the JSON row data with the value you expect.
   - Call it again with `ReportFormat: 1` (CSV) or `0` (Excel) and confirm the column appears with
     the right header/format.
   - Call a **different, unrelated** report/view and confirm nothing changed — this is the no-op
     guarantee: a report/view with no computed fields configured is byte-for-byte unaffected.

### Worked example (exactly what shipped in the pilot)

Report: `AccountLedgerReport`, `REPORTID = -1399975210`, Detail view `REPORTVIEWID = 1000000101`.

| FieldName | FieldType | Expression | Result (live, real data) |
|---|---|---|---|
| `Open` | 6 (Decimal) | `IIf(AccountOpeningBalance < 0, -AccountOpeningBalance, AccountOpeningBalance)` | `AccountOpeningBalance=0` → `Open=0.00` |
| `OpenDC` | 1 (String) | `IIf(AccountOpeningBalance > 0, 'D', 'C')` | → `C` |
| `TextStatus` | 1 (String) | `IIf(Status = 0, 'Pending', IIf(Status = 1, 'Active', 'Cancelled'))` | `Status=1` → `Active` |

The full, real migration is `DB/Migrations/20260902_AccountLedger_ComputedFields_Pilot_SqlServer.sql`
(+ `_Postgres.sql` twin) — copy its shape directly for a new field rather than retyping the
INSERT from scratch.

---

## 4. BI Platform field-mapping owners — ad-hoc datasets

**Who this is for:** whoever configures a `MBICATALOG` dataset's field mappings (Warehouse fact,
AnalysisQuery, or ApiService-kind). **How:** via the real save API — this side already has a
proper endpoint, unlike the fixed-report side.

### Step-by-step

1. **Confirm the source measure/dimension your expression needs is already registered** for the
   dataset — call `GET /BI/GetMeasuresForDataset?BICatalogId=<id>` /
   `GetDimensionsForDataset?BICatalogId=<id>` and note the exact `SourceFieldName`s. A computed
   field can only reference fields already in that list (or another computed field in the same
   request) — nothing is auto-added on your behalf (see §5 for why).

2. **Save the computed field mapping**:

   ```
   POST /BI/SaveBIFieldMapping
   {
     "BICatalogId": 2,
     "SourceFieldName": "ABSNETPROFIT",
     "MappedAs": 2,
     "Label": "Abs Net Profit",
     "DataType": 6,
     "IsVisible": true,
     "SortOrder": 100,
     "ComputeExpression": "IIf(NETPROFIT < 0, -NETPROFIT, NETPROFIT)"
   }
   ```

   `SourceFieldName` here is the **output key**, not a physical column — this is the name callers
   will use in `ComputedFields` (step 3) and the key that shows up in `RunBIQuery`'s `Data` rows.
   The save call **compiles the expression** before writing anything — a syntax error (unbalanced
   parens, unknown function) or an empty `ComputeExpression` on a `MappedAs=2` row is rejected
   with the exact `DataColumn.Expression` parser message, and nothing is written on failure.

3. **Request it in a query** by adding its name to the new `ComputedFields` list, alongside the
   `Dimensions`/`Measures` it depends on:

   ```
   POST /BI/RunBIQuery
   {
     "DatasetId": 2,
     "Dimensions": [],
     "Measures": [{ "Field": "NETPROFIT", "Aggregation": 1 }],
     "ComputedFields": ["ABSNETPROFIT"]
   }
   ```

   Response `Data` carries both keys (`NETPROFIT`, `ABSNETPROFIT`); `Meta.ComputedFieldsUsed`
   carries the field's label, data type, resolved source fields, and additivity classification
   (always reported `NonAdditive` — see §5) — but never the raw expression text, since `Meta`
   ships to every caller and the formula is admin configuration, not query metadata.

4. **A computed field is invisible to `GetMeasuresForDataset`/`GetDimensionsForDataset`** by
   design — it will never appear in a generic "pick a measure" list. If a screen needs to let
   someone pick from computed fields specifically, it calls the dedicated
   `GET /BI/GetComputedFieldsForDataset?BICatalogId=<id>` endpoint instead.

### Worked example (exactly what was live-tested)

Dataset: `BICatalogId = 2` (FFINANCE, Warehouse-kind).

```
POST /BI/SaveBIFieldMapping
{ "BICatalogId": 2, "SourceFieldName": "ABSNETPROFIT", "MappedAs": 2, "Label": "Abs Net Profit",
  "DataType": 6, "IsVisible": true, "SortOrder": 100,
  "ComputeExpression": "IIf(NETPROFIT < 0, -NETPROFIT, NETPROFIT)" }

POST /BI/RunBIQuery
{ "DatasetId": 2, "Measures": [{ "Field": "NETPROFIT", "Aggregation": 1 }],
  "ComputedFields": ["ABSNETPROFIT"] }

→ Data: [ { "NETPROFIT": 3000.0, "ABSNETPROFIT": 3000.0 } ]
```

---

## 5. Safety rules worth knowing (both sides)

- **A computed field can never overwrite a real value.** If its `FieldName`/`SourceFieldName`
  collides with a property the DAL/dataset already returns, the computed expression is silently
  skipped for that field (logged once) rather than clobbering the real value.
- **A bad expression never fails the report/query.** A syntax error or unresolvable reference
  drops just that one field, with a log line naming it; every other field and the report itself
  keep working. A per-row evaluation error (e.g. divide-by-zero) nulls just that cell.
- **A computed field can only reference fields already selected/returned — nothing is
  auto-included.** On the BI side in particular, this is the actual safety mechanism protecting
  the platform's additivity rules (the check that stops you from, say, `SUM`-ing a point-in-time
  balance across dates incorrectly): auto-injecting a missing source measure would mean choosing
  an aggregation *after* that check already ran, which could let an unsafe aggregation slip
  through. So instead: request the source measure/dimension yourself, or the query is rejected
  with a message naming exactly what's missing.
- **A computed field is always additive-unsafe (`NonAdditive`) itself**, even when every field it
  reads from is safely summable — `Abs(a) + Abs(b) != Abs(a + b)`, so the *result* of a computed
  field should never be re-aggregated at a coarser level without being recomputed there.
- **You can chain computed fields** (one referencing another in the same request/view) — order
  doesn't matter, dependencies are resolved automatically; a genuine reference cycle is rejected
  by name.
- **Sorting by a computed BI field is rejected.** Row limits (`TopN`) are applied in SQL before
  computed fields are evaluated, so sorting afterward would silently return the wrong rows.
- Row-locality is enforced: an expression can't reference `Parent.`/`Child(` relations or
  aggregate functions (`Sum`, `Avg`, `Count`, ...) — this engine evaluates one row at a time over
  values that are already aggregated; if you need an aggregate, request the underlying measure
  with the aggregation you want instead.

---

## 6. Not built in this pass (known, deliberate gaps)

- **No live admin screen for the fixed-report side.** Fields are authored via migration, same as
  every other field on these reports today. A `SaveReportVsFields`-style endpoint + a small
  "validate this expression" dry-run endpoint are the natural follow-up, once someone wants
  fields addable without a deploy.
- **The NDJSON export stream** (`ReportFormat: NdJsonStream`) still serializes the raw DTO
  directly and does not carry computed fields.
- **Sub-row/array fields** (e.g. `VoucherInstrumentArray.InstrumentNumber`) are a separate axis
  (`ReportSubRowResolver`) — a computed field only operates on the flat main row today, not inside
  an expanded sub-row.
- **Filtering on a computed BI field** isn't supported — pushing an expression into a SQL `WHERE`
  clause would defeat the "expression text never reaches SQL" safety property this whole design
  relies on.
- **Updating an existing `MBIFIELDMAPPING` row** (computed or otherwise) via the API isn't wired
  yet — `SaveBIFieldMapping` is insert-only today; that's a pre-existing gap in the BI Platform,
  not something this feature introduces.