Skip to content

NinjaExcel Report β€” User Manual

For accountants and report authors. No programming needed beyond writing a SELECT. Two films accompany this manual: film 1 (the Trial Balance) and film 2 (the Sales Register that grows), each in English and Thai, plus one long film joining them. Every number and claim below was produced by a gate in scripts/ninja_template/run_all.sh (gate names in brackets). Anything not built yet is under Planned, never stated as fact.

1. What it is

You design a report in Excel, exactly the way you want it to look. You tell the sheet where each number comes from with a short SQL query. NinjaExcel runs the queries against the ERP data, paints the results into your layout, makes the formulas follow, and gives you an .xlsx and a one-page PDF.

You keep full control of fonts, colours, borders, number formats and column widths. The engine only fills in and grows what you marked.

Everything runs on free open-source code (ExcelJS and PDFKit, both MIT licence). No PDF-conversion subscription is used.

2. Quick start β€” the Trial Balance example

The example in this manual is a Thai trial balance (ΰΈ‡ΰΈšΰΈ—ΰΈ”ΰΈ₯ΰΈ­ΰΈ‡) over the GardenWorld demo company: for each account, the opening balance, the debit and credit movement in the period, and the closing balance, with totals and a proof line. It works by accounting period, like the ERP's own trial balance: a period counts by its start date.

In the ERP: open the Excel Report pill β†’ sample report: Thai trial balance β†’ set From 2003-01-01 and To 2003-12-31 β†’ Run report. A second sample, the Thai sales register (Β§6A), shows a list that grows as you sell. The panel shows the painted report, with two downloads: the .xlsx and a one-page PDF.

Result for that period [B2_pill_end_to_end, run in a real headless browser on the ERP's own GardenWorld data]: 21 accounts, total debit 40,392.44 = total credit 40,392.44, proof line 0.00, every account row equal to an independent query, a valid .xlsx, and a one-page PDF; 3 queries in about 35 ms.

Without the ERP, the same engine runs from a command line (developer use):

FROM="2003-01-01 00:00:00.0" TO="2003-12-31 00:00:00.0" \
  node scripts/ninja_template/W29_TwinXlsx.js parity_tb.xlsx my_report.xlsx

3. Anatomy of a template

An .xlsx with these sheets:

Sheet You put here
Input The layout with tags. A tag is text like @el.value@3 β€” "put the values of this query here; the query is on Process row 3".
Process One row per query piece (Β§4).
Reference Anything you like (notes, a sample result). Not used by the engine.
Output The sheet the report is painted on. Rebuilt on every run from BACKUP.
BACKUP The master copy of Output. Make all layout edits here.

The Process sheet columns

Column Meaning
ANNOTATION, ROW Helper formulas; they show the tag name and row number.
SELECT What to read, e.g. el.value, SUM(a.amtacctdr).
TABLE The first table of a query must end with the alias a (e.g. fact_acct a). Later rows may add INNER JOIN … here.
JOIN Extra conditions (optional).
WHERE Filters, GROUP BY, ORDER BY.
Direction S, V, > or + (Β§5).
VALUES Filled by the engine with the query results. Put END after the last row.

Rules the engine applies for you:

  • Client scoping is automatic. Every query is limited to the current client; you never type it.
  • Rows that continue a query (empty TABLE) add their SELECT to the query above them.

4. The worked example β€” Process rows

# SELECT TABLE WHERE Dir
1 a.name ad_client a S
2 CONCAT(to_char(DATE #1,'DD/MM/YYYY'),' - ',to_char(DATE #2,'DD/MM/YYYY')) ad_org a S
3 el.value fact_acct a a.postingtype='A' AND SUBSTR(p.startdate,1,10) <= SUBSTR(#2,1,10) GROUP BY el.value, el.name ORDER BY el.value V
4 el.name INNER JOIN c_elementvalue el ON el.c_elementvalue_id=a.account_id V
5 SUM(CASE WHEN SUBSTR(p.startdate,1,10) < SUBSTR(#1,1,10) THEN a.amtacctdr-a.amtacctcr ELSE 0 END) INNER JOIN c_period p ON p.c_period_id=a.c_period_id V
6 SUM(CASE WHEN SUBSTR(p.startdate,1,10) >= SUBSTR(#1,1,10) THEN a.amtacctdr ELSE 0 END) V
7 SUM(CASE WHEN SUBSTR(p.startdate,1,10) >= SUBSTR(#1,1,10) THEN a.amtacctcr ELSE 0 END) V

The trick of this report: opening is every period that starts before #1, movement is the periods that start inside the range. One query, three CASE columns. Row 1 is the company name and row 2 the period text, both painted into the heading.

5. Directions β€” how results are painted

Dir Meaning Example in this manual
S One value into one cell (scalar). Rows 1–2: the company name and the period text 01/01/2003 - 31/12/2003 into the heading.
V A list downward; the sheet grows one row per result, formulas below are pushed down and copied. Rows 2–6: one line per account. 21 results β†’ 21 rows.
> A list to the right; columns are inserted and formulas to the right follow. Used in the parity_v2 fixture [A_engine_parity_v2].
+ A cross-tab: one list across, one down, amounts at the crossings (written as C3+, pointing at the axis row). The parity_xtab fixture: four accounts across [A_engine_parity_xtab].

Date binds

#1 is the From date, #2 the To date, #3 the account-group parameter. They are replaced with quoted values before the query runs.

6. How rows grow and formulas follow

After the last list row there must be a spacer row: a row with a formula in the cell under every tag column (the trial balance uses =""). That formula tells the engine "the list ends here"; the list grows into it.

Totals must include the spacer row. The template's totals are =SUM(C6:C7) where row 6 is the first account and row 7 the spacer. After 21 accounts were painted they became =SUM(C6:C27) automatically [T_trialbalance checks the range].

A range that stops above the spacer, such as =SUM(C6:C6), does not grow β€” the gate catches this on purpose [T_NEG_range].

The closing-balance formula in each row (=C6+D6-E6) is copied down for every new row, with its references moved one row each time.

Formulas supported: arithmetic, SUM, COUNT, COUNTA, MIN, MAX, ABS, ROUND, ROW, IF, CONCATENATE. Other functions show #NAME? (Β§8).

6A. The Sales Register β€” a list that grows as you sell

The second sample is a Thai sales register (ΰΈ—ΰΈ°ΰΉ€ΰΈšΰΈ΅ΰΈ’ΰΈ™ΰΈΰΈ²ΰΈ£ΰΈ‚ΰΈ²ΰΈ’): one row per completed sales invoice, a total, and a document count. It is the clearest way to see rows being pushed down.

# SELECT TABLE WHERE Dir
1 a.name ad_client a S
2 period text (as in Β§4) ad_org a S
3 a.documentno c_invoice a a.issotrx='Y' AND a.docstatus='CO' and the invoice date between #1 and #2, ORDER BY date, number V
4 SUBSTR(a.dateinvoiced,1,10) V
5 bp.name INNER JOIN c_bpartner bp ON bp.c_bpartner_id=a.c_bpartner_id V
6 a.grandtotal V

The sheet has the list's first row, a spacer row, a total =SUM(D6:D7) and a count =COUNT(D6:D7). When the report runs, the list is pushed down to make room for every invoice, the total and the count move down with it, and their ranges grow to cover all the rows.

Watch it happen (measured in a real browser on the ERP's own GardenWorld data, with three POS sales entered through the real screens): the register had 4 rows, total 541.02; after the three sales it had 7 rows, total 559.02 (up by exactly the three new invoices, 18.00), and the total's range became SUM(D6:D13) [U_usecases]. The Trial Balance of the same session still balanced (debit = credit = 41,283.78).

Size: a list is no longer limited to 248 items. The old .xls format stopped there, silently. The register was tested on 1,441 invoices: every row equals an independent query, the total and count are right, in 2.8 seconds [R_register_big].

7. Getting the files

  • The .xlsx keeps your fonts, fills, borders, number formats, merged cells, column widths and row heights [B_conv_style_*, B_host_roundtrip_*, C_style_*].
  • The PDF is one page sized to the sheet. Every visible cell's text must be present in the PDF [T_pdf_content: 144 of 144 cells for the trial balance].
  • Thai (and Lao) text is placed with a script-appropriate font so no letters drop out. This was a real defect found by that gate and fixed.
  • Your template file is never changed by a run β€” the report is written to a different file [T_manual_claims β†’ CLAIM_NO_OVERWRITE].

7A. Trying it safely: the standard GardenWorld use cases and reset

A small standard set of use cases enters real documents into the GardenWorld demo company through the ERP's own screens, so a report can be tried on live data. Today it has UC-1, a POS cash sale (customer Joe Block, organisation HQ, product Γ— quantity, then Complete: the ERP creates the shipment, invoice, stock move and the posting lines in one signed step; 16 posting lines per sale).

Reset is built in. Each run starts in a fresh browser session, so GardenWorld reloads clean from its seed and nothing a use case entered survives. This is proved, not assumed: the runner runs the reports before entering, after entering, and in a second fresh session, and the last must equal the first [U_usecases; its negative control U_NEG_usecases must fail]. In the film this is shown by clearing the browser's local data (cache, signer, operation log) β€” nothing is sent anywhere.

Developer command: node scripts/erp_usecases/run_usecases.js <bim-ootb folder> 3 (three sales).

8. Troubleshooting β€” real messages

You see Meaning Do
Your hand-typed text in Output is gone after a run Output is rebuilt from BACKUP every run. [CLAIM_BACKUP_RULE] Make every layout/text edit in BACKUP.
Β§SHEET_MISSING BACKUP The template has no BACKUP sheet. Copy your finished Output sheet and name it BACKUP.
ROW NO. n .. CHECK >> Direction (Column 8) of Process Sheet is Empty. Fill with S, V or > A Process row has no Direction. Fill column Direction with S, V, > or +.
NO NAME IN SELECT ROW OF PROCESS SHEET A row has no SELECT. Fill SELECT.
NO ' FROM' IN ORIGINAL SQL … The statement has no table. The first row of a query needs a TABLE ending in a.
A cell shows #NAME? The formula uses a function the engine does not support. It is shown as an error, never as a silent 0 [CLAIM_NAME_ERROR]. Use a supported function (Β§6) or compute it in the SQL.
A total did not include all rows The range stops above the spacer row. Make the range end on the spacer row (Β§6).
SQL ERROR: <the database's own message> followed by statement: SELECT … One of your Process-sheet queries failed (for example a misspelled column: no such column: el.valu). You see the real database message and the exact statement that failed, so you can fix the cell. The legacy plugin hid the real SQL error. Correct the SELECT/TABLE/WHERE text it shows [T_sql_error; its negative control T_NEG_sql_error must fail].
LIST TRUNCATED at Process column … A list had more values than the sheet can hold (about 16,000). Before this version a list was cut silently at 248. Narrow the filter or split the report.
A query returns no data The cell stays empty and the run notes RETURNS NOTHING. Check the dates and filters.

9. Limits

  • One page per sheet; no page breaks.
  • A single list can hold about 16,000 values (the .xlsx column limit of the Process sheet), tested to 1,441 rows. Very long lists make a very tall single-page PDF.
  • Formula shifting is verified for relative references and simple ranges only. Absolute references ($A$1), cross-sheet references, whole-column references (A:A) and named ranges are not yet verified β€” avoid them in grown areas.
  • Images are not supported.
  • The PDF layout was measured against a commercial converter's output on three reference sheets: about 98.6 % of 1,833 checked items (page size, fills, border lines, words) match; the single known deviation is a few shared vertical borders on the largest sheet [D_geom_geom, D_geom_xtab, D_geom_v2]. This figure is for those reference sheets; the trial balance has no commercial reference PDF.
  • The example works by accounting period (a period counts by its start date), not by individual posting date, because the ERP's own database stores the period on each posting.
  • Thai labels in the example are a hand translation.

10. How this improves over the legacy plugin

Each line cites the gate that proves it. The first column says what the user gets.

Improvement Evidence
No server needed. The legacy plugin ran inside a running iDempiere/OSGi server; the default gate run needs no server and no Postgres. scripts/ninja_template/run_all.sh header; all gates run on a local SQLite copy of the data.
No commercial PDF converter. The PDF is produced by free code (PDFKit, MIT). T_pdf_content, D_geom_*.
Your template is never overwritten. The legacy plugin rewrote the template file in place (a truncated file was a real customer fault). T_manual_claims β†’ CLAIM_NO_OVERWRITE.
Wrong functions are visible. An unsupported formula shows #NAME?, not a silent 0. CLAIM_NAME_ERROR.
Formatting survives growth. Fonts, fills, borders, number formats, merges, widths and heights kept after rows/columns are inserted. B_conv_style_*, C_style_* (earlier two-way check: 50,600 cells, 0 differences).
PDF text is checked cell by cell. T_pdf_content: missing cells = 0.
Thai and Lao script render. T_pdf_content (found and fixed a real drop-out).
Totals that don't grow are caught. T_NEG_range.
Runs inside the ERP, in the browser. Pill β†’ template β†’ dates β†’ Run, with .xlsx and PDF downloads, no server round trip. B2_browser_run, B2_pill_end_to_end (21 accounts, 0 differences, one-page PDF).
Lists are not cut. The legacy .xls host silently stopped a list at 248 items. R_register_big (1,441 rows, 0 differences, 2.8 s) and R_NEG_register (a total that does not grow must fail).
Sees what you just entered. The report reads the ERP's session, including documents entered a moment ago. U_usecases (three live sales: 4 β†’ 7 rows, total +18.00).
Clean reset, proved. U_usecases: a fresh session equals the baseline; U_NEG_usecases must fail.
Proof built into the demo. Debits equal credits, checked against an independent calculation. T_trialbalance.

Witnessed against the faults we had in the legacy plugin

The old plugin had real faults, and each one now has a witness (an automatic check that must pass, plus a negative control that must fail, so the check can say NO). A witness that judged nothing says INCONCLUSIVE, never PASS [R_VACUOUS, T_VACUOUS].

Legacy fault What now guards against it Witness
Output out of step with BACKUP β€” hand edits typed into Output were lost, or the two sheets drifted apart and gave wrong numbers Output is rebuilt from BACKUP on every run. A cell typed into Output, even one outside the layout, is discarded; every run is also compared cell by cell with frozen reference results CLAIM_BACKUP_RULE in T_manual_claims (negative control T_NEG_manual_claims); A_engine_*, C_cells_*
The template file was overwritten in place (a truncated file was a real customer fault) The template is never written to; the report goes to a different file CLAIM_NO_OVERWRITE
An unsupported formula gave a silent 0 It shows #NAME? CLAIM_NAME_ERROR
A list was silently cut at 248 items No cut; tested to 1,441 rows R_register_big, R_NEG_register
Totals did not cover rows that were pushed down Ranges grow with the list T_trialbalance, T_NEG_range, U_usecases
Formatting lost after rows or columns were inserted Fonts, fills, borders, formats, merges and sizes are kept B_conv_style_*, C_style_* (50,600 cells, 0 differences)
The real SQL error was hidden, so a broken query was hard to find The database's own message and the failing statement are shown T_sql_error
Letters missing from the PDF (for example Thai and Lao script) Each script gets a font that has its letters T_pdf_content, D_content_*

Not yet built (so no claim): a warning before the run when Output differs from BACKUP. Today the report is correct because Output is rebuilt; the planned template check would also tell you that your hand edit was discarded.

Planned (not built β€” no claim made)

  • Template check before running: missing sheets, Process addresses outside Output, empty Direction, and Output-vs-BACKUP drift (a hand edit in Output is discarded today; this would warn first).
  • SELECT-only guard: a template's SQL is refused unless it is a read-only SELECT.
  • Per-run SQL de-duplication with timings.
  • Quiet logging.
  • Calibrated PDF column widths from a reference PDF.