Analytics Standards Copy link

🚨 Blitz data = Excel worksheet UDFs, not MCP tools/connectors. Extract data only by writing =br_...() into a cell (via execute_office_js) and reading the spill — never tool_search for br_*, never refresh_mcp_connectors, and never conclude "no Oracle EBS / Blitz connector is set up" (the Blitz Report add-in is an Excel add-in, not an MCP connector, so it never appears in the connector list — its absence there is expected). Load the extraction mechanism first: read_skill("br-run-reports").

Read this at the start of any br_ skill run. These rules override any conflicting guidance in individual skill files.

Why these rules exist

The main cause of slow runs (10–15 min) is too many sequential round-trips between the model and Excel. Each execute_office_js or set_cell_range call has latency. The target runtime for a single-module analysis is under 7 minutes; for a chained orchestration (e.g. month-end readiness) under 20 minutes.

Rule 1 — One call per step: read + compute + write in the same block

Every analytics step must read its source data, run all calculations, and write its output sheet in a single execute_office_js call. Never issue a separate read call then a separate write call for the same step.

❌ Wrong:

• Call 1: get_cell_ranges to read column values
• Call 2: execute_office_js to write the results

✅ Right:

• Call 1: execute_office_js that reads, computes, and writes in one JS block

Rule 2 — All dashboard/scorecard sections in one call

All KPI cards, summary tables, charts-data, and narrative rows for a single output sheet go into a single execute_office_js call. Do not use multiple set_cell_range calls for different sections of the same sheet.

Exception: charts require a second call (separate API round-trip) — this is the one permitted exception.

Rule 3 — Skip mid-pipeline verification reads

Trust the formula_results field returned by set_cell_range — it shows computed formula values and errors. Do NOT insert intermediate get_cell_ranges calls between pipeline steps to check intermediate values. Only do one explicit re-read at the final validation step of the pipeline.

Rule 4 — Combine headers + seed formula + autoFill in one call

When adding derived columns to a source sheet, write all column headers, the seed formula row, and call autoFill down to the last data row — all in a single execute_office_js block. Do not spread these across separate set_cell_range calls.

Rule 5 — Combine write-back with the step that computed it

Any column written back to a source sheet (e.g. a Cluster label, a Flag column, a Category code) must be populated in the same JS call that computed the value — not in a follow-up call.

Rule 6 — Bulk write all narrative cells in one call

All recommendation bullets, insight text, and status labels for a single sheet go into a single set_cell_range call (or included in the single execute_office_js call for that sheet). Do not call set_cell_range once per bullet or once per row.

Rule 7 — Anchor before analysis; reconcile at the end

Before any analytics step: capture the row count and a SUM of the key amount column as the reconciliation anchor. At the final validation step: confirm the dashboard/scorecard total equals the anchor. This is the one permitted verification read outside of Step 1b.

Rule 8 — Chained orchestrations: each sub-skill follows these rules independently

When a master skill (e.g. br-month-end-readiness) delegates to sub-skills (e.g. br-ap-close-readiness), each sub-skill must follow these rules within its own pipeline. The orchestrator must not insert extra read calls between sub-skill invocations to inspect their output — read the return values from the sub-skill's final JS call instead.

Quick reference — permitted call counts per pipeline step

Excel correctness — bake these in UPFRONT (each caused a mid-run debug loop + re-run)

These are not speed rules, but each one cost multiple diagnosis/re-run passes on a real run (that's how a target-7-minute job became 30). Handle them in the FIRST write of every step, not after you discover the symptom.

1. Never apply Text ("@") number format to a cell that holds a FORMULA. It turns the formula into a literal string that never evaluates (symptom seen: all COUNTIF aging counts came back 0). Apply "@" ONLY to literal data/label cells that must stay verbatim — never to a formula range — and set the format BEFORE writing the value.

2. Force literal labels that Excel could coerce — to text. Values like an aging bucket "8-30", a period "Feb-09", or a range "1000-1999" are parsed by the workbook's locale (US: setting a cell .values = "8-30" becomes the 30-Aug date serial, zeroing the data underneath). Write such labels as text: prefix with an apostrophe ('8-30) OR pre-set those label cells (not formula cells, per #1) to "@" before writing — or use an unambiguous form ("8–30 days").

3. Classify from the ACTUAL source value, including literal sentinels. When you derive a category/flag from a column, test the real value — do not assume "non-empty = a person / the normal case". (Symptom seen: a Held By column contains the literal text "System", so a "any value in Held By means a person → Manual" rule misclassified all 460 rows and forced a k-means re-run.) Enumerate the real values first; handle the sentinel/blank cases explicitly.

4. Sanitize every string cell that Excel will parse as a FORMULA before writing it. A string whose value begins with =, +, -, or @ (e.g. an invoice number "=-", a code "+A1", "@dept") is interpreted as a formula, and one bad value makes the whole Range.values write throw a GeneralException / InvalidArgument. Because the write is one big array, the error names no row — so you waste several round-trips binary-searching for the offender (symptom seen: invoice number "=-" on row 103 forced a 4-call binary search). Bake it into the FIRST write: map every string cell through v => (typeof v === "string" && /^[=+\-@]/.test(v)) ? "'" + v : v (apostrophe-prefix forces a text literal), AND pre-set any id / key / invoice-number / code column to text format (range.numberFormat = "@") so values like "=-", "001234", "1E5" store verbatim. Never discover this from the symptom.

5. Compute and WRITE in separate calls when the source range is big — never load the whole used range into the write call. Reading a large used range (1400+ rows × 50+ cols) into JS and writing the result in the same call hits the office-js memory ceiling and throws a GeneralException at Range.values with no useful location (symptom seen: two failed 1478×52 single-call attempts). For a big extract this is the one place Rule 1 yields: call 1 reads only the specific columns you need (not usedRange.values), computes, and stashes the result array with blobs.setJSON("<key>", result); call 2 reads it back with blobs.getJSON("<key>") and does a single full-range .values = write — not row-by-row, not chunk-and-sync. Set the autofilter with sheet.autoFilter.apply(headerRange) — range.autoFilter() is not a function and throws. Set column widths in the same write so amounts never render ######.

6. Get-or-create a sheet with getItemOrNullObject — NEVER getItem inside a try/catch. office-js defers worksheets.getItem("X")'s ItemNotFound to context.sync(), after the catch block — so try { ws = sheets.getItem("X"); ws.getUsedRange().clear(...); } catch { ws = sheets.add("X"); } does not catch the missing-sheet case; the whole call throws ItemNotFound at WorksheetCollection.getItem and wastes a retry (symptom seen: blitz_ and Dup_Candidates creation each failed once this way in the same run). Always: const ws = ctx.workbook.worksheets.getItemOrNullObject("X"); ws.load("isNullObject"); await ctx.sync(); then const sheet = ws.isNullObject ? ctx.workbook.worksheets.add("X") : ws; (clear sheet if it was reused). Same rule for any get-or-create of a chart / name / table: use the *OrNullObject variant + load("isNullObject"), never a try/catch around the throwing getter. (Generic office-js discipline the same runs also slipped on, FYI: assign a range to a variable, load() it, await ctx.sync(), then read from that SAME variable — never re-call sheet.getRange("A1") for the read. Each getRange() returns a fresh, un-loaded proxy, so loading one and reading another throws PropertyNotLoaded at Range.values — this cost ~5 retries in one run. Also: when writing a 2-D array, the array shape must match the target range exactly ([[v]] → a 1×1 cell, not a 1×N range); and validate a getRange("A1:..") address is non-empty before adding a chart from it.)

7. Building a chart: let charts.add create the series; recolor by loading series.items — never series.add(), and numberFormat belongs on a RANGE, not a chart. Two chart-styling slips cost retries in a real run: (a) calling chart.series.add() to inject a series manually throws — instead create the chart from the data range in one shot (const ch = sheet.charts.add(Excel.ChartType.columnClustered, sheet.getRange("K15:L20"), Excel.ChartSeriesBy.columns)), which builds the series for you; to recolor, ch.series.load("items"); await ctx.sync(); then ch.series.items[i].format.fill.setSolidColor("#2E75B6"). (b) numberFormat is a Range property — set it on the source cells before charting (range.numberFormat = "$#,##0", a single string for the whole range — not a shape-mismatched 2-D array, per #6); to format a chart's axis use ch.axes.valueAxis.numberFormat = "$#,##0", never Range.numberFormat on the chart object. Keep the chart call minimal — title + position + one setSolidColor per series — and do it in the second (charts-only) execute_office_js call.

8. A date read via range.values is a SERIAL NUMBER — keep it a real date by applying a date numberFormat on every date column you write to a derived sheet. office-js range.values returns a date cell's underlying serial (e.g. 39814), NOT the formatted text — the source sheet's date format does not travel with .values. (And .values is the right thing to read: the analytics needs the serial for date math — date2 − date1 for a "within N days" rule — so do NOT switch to .text.) The miss is purely on the write: when you write that serial into your Dup_Candidates / Worklist / Pay_Schedule / dashboard sheet, apply a date number-format to those columns exactly like you format the amount columns — sheet.getRange("H2:H<last>").numberFormat = "dd-mmm-yyyy" (single string for the whole column, per #6) — so it renders as a real, sortable Excel date instead of 39814. (Symptom seen: Dup_Candidates Invoice Date A/B showed raw serials because the write formatted the $ columns but not the date columns.) Pass the serial through unchanged — do not convert to a string; the date-format makes it a true date the user can sort and filter. 🚨 Do NOT "solve" the serial display by stringifying it with a formatDate() helper that emits "13-Mar-2009" — a live run did exactly that and the dates can no longer sort or filter as dates (text, not a date). Keep the serial in the array; format the column. Canonical pattern (the whole fix is one extra line per date column):

   // write the RAW serials (not strings) ...
   sheet.getRange(`H2:H${lastRow}`).values = dateSerials;          // [[39885],[40179],...]
   // ... then format the column so they render as real dates:
   sheet.getRange(`H2:H${lastRow}`).numberFormat = "dd-mmm-yyyy";  // one string for the whole column