The last stage of the customer app from the series Building a customer app in Uniface 10 added a reporting service: a cockpit, a month-end report, a year comparison, margins, a cash forecast, stock turnover, service figures and a sales funnel. All of it lives in one Uniface service, KPI2_SVC: ProcScript operations that build plain SQL, run it against SQLite and return Uniface lists or write HTML files.
This article shows how the service hands figures to its callers, how it writes HTML without a report painter, four of the queries that needed a trick, and one deliberate restriction: no window functions, no CTEs, and never more than one scalar subquery in a select list.
Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) and the SQLite version of its SLE driver (3.50.2), October 2026.
A cockpit as an associative list
The cockpit returns 18 figures. Instead of 18 OUT parameters, the operation returns one Uniface list with key=value items, built with putitem/id:
call COUNT_SQL("SELECT COALESCE(SUM(d.NET_CENTS), 0) FROM SALES_DOC d WHERE %%(vCond) AND substr(d.DOC_DATE, 1, 7) = substr('%%(pDay)', 1, 7) AND d.DOC_DATE <= '%%(pDay)'", vN)
putitem/id pData, "SALES_MONTH", vN
call COUNT_SQL("SELECT COUNT(*) FROM SALES_DOC WHERE DOC_TYPE = 'ORDER' AND STATUS IN ('OPEN', 'CONFIRMED')", vN)
putitem/id pData, "OPEN_ORDERS", vN
...
A caller picks what it needs with getitem/id vN, vData, "OPEN_AR". Adding a figure later doesn't change the signature, and every existing caller keeps working.
Each figure is its own small statement. That is 18 round trips instead of one big query - for a cockpit opened a few times a day on a local SQLite file, simplicity wins.
HTML without a report painter
The reports are HTML files written with lfiledump. A helper entry turns one cockpit figure into a table row, formats cents as currency and appends the row to the page:
entry KV_ROW
params
string pLabel : IN
string pData : IN
string pKey : IN
numeric pCents : IN
string pHtml : INOUT
endparams
variables
string vText, vLine
numeric vN
endvariables
getitem/id vN, pData, pKey
if (pCents = 1)
call FMT_CENTS(vN, vText)
else
vText = "%%(vN)"
endif
vLine = "<tr><td>%%(pLabel)</td><td style='text-align:right'>%%(vText)</td></tr>"
pHtml = $concat(pHtml, vLine, "%%^")
return 0
end
and the page is a list of such calls:
call KV_ROW("Open receivables", vData, "OPEN_AR", 1, vRows)
call KV_ROW("Overdue receivables", vData, "OVERDUE_AR", 1, vRows)
call KV_ROW("Open tickets", vData, "OPEN_TICKETS", 0, vRows)
Two ProcScript details: $concat takes at most five arguments, so longer pages are built in steps; and %%^ in a string literal writes a line break when the file is saved. Every operation that writes a file also logs the export with user, type, row count and file name.
The restriction first
SQLite 3.50 supports common table expressions and window functions. A running total would be one SUM(…) OVER (ORDER BY …). I still don't use them here.
The reason is not SQLite, it's the path between ProcScript and SQLite. Earlier in the project, a single SELECT without a FROM clause ended the whole Uniface process - no error, no log line, just gone (the story is in The Uniface test that "hung" had died). Statements with several scalar subqueries in the select list also caused trouble. Since then the rules for SQL in this app are boring on purpose:
- derived tables in
FROMinstead of scalar subqueries in the select list, - a calculation without a table still gets one:
SELECT … FROM (SELECT 1 AS X), - no window functions or
WITH, because I haven't tested them through this driver and I don't want to find out in production.
If your SQLite access goes through a mature driver, use window functions. If it goes through something you can't debug, the patterns below still work.
1. Twelve months without a calendar table
A year comparison needs one row per month - including months without revenue. Without a calendar table and without a recursive CTE, a derived table of twelve literal rows does it:
SELECT x.N, x.C, x.P, x.C - x.P,
CASE WHEN x.P <> 0 THEN ROUND((x.C - x.P) * 100.0 / x.P, 1) ELSE 0 END
FROM (
SELECT m.N AS N, COALESCE(a.S, 0) AS C, COALESCE(b.S, 0) AS P
FROM (SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) m
LEFT JOIN (SELECT CAST(substr(d.DOC_DATE, 6, 2) AS INTEGER) AS M, SUM(d.NET_CENTS) AS S
FROM SALES_DOC d
WHERE d.DOC_TYPE IN ('INVOICE', 'CREDIT') AND d.STATUS <> 'DRAFT'
AND substr(d.DOC_DATE, 1, 4) = '2025'
GROUP BY 1) a ON a.M = m.N
LEFT JOIN (/* same for 2024 */) b ON b.M = m.N
) x
ORDER BY x.N;
Three details:
- Aggregate first, join second. Each year is grouped into at most twelve rows before the join. Joining raw documents to months and grouping afterwards would multiply rows when both years have data.
-
* 100.0makes the percentage a real division.(C - P) * 100 / Pwith integers truncates. - Revenue includes credit notes with their negative amounts, and cancelled invoices stay in. An invoice and its cancelling credit note add up to zero over the year; if they fall into different months, the first month shows the revenue and the second the reversal.
2. A cash forecast in weekly buckets
The forecast answers: how much money comes in and goes out per week, starting today? Incoming is the open amount of issued invoices by due date; outgoing is open supplier bills by due date. Anything already overdue belongs into the first week - it is due now.
The number of weeks is a parameter (1 to 26), so the week list is built in ProcScript:
vWeeks = "SELECT 0 AS W"
vI = 1
while (vI < pWeeks)
vWeeks = "%%(vWeeks) UNION ALL SELECT %%(vI)"
vI = vI + 1
endwhile
Each side computes its bucket with a CASE:
SELECT CASE WHEN COALESCE(o.DUE_DATE, '2026-10-17') <= '2026-10-17' THEN 0
ELSE CAST((julianday(o.DUE_DATE) - julianday('2026-10-17')) / 7 AS INTEGER)
END AS W,
SUM(o.OPEN_CENTS) AS S
FROM (/* open invoices with open amount */) o
GROUP BY 1
CAST(… AS INTEGER) truncates towards zero. For positive day differences that is the floor, which is what we want; negative differences are already caught by the CASE. Without it, an invoice 3 days overdue would land in week 0 by luck and one 10 days overdue in week -1, which doesn't exist in the week list and would silently disappear.
The week dates come from julianday arithmetic, not from string concatenation:
SELECT date(julianday('2026-10-17') + w.W * 7),
date(julianday('2026-10-17') + w.W * 7 + 6),
COALESCE(a.S, 0), COALESCE(b.S, 0), COALESCE(a.S, 0) - COALESCE(b.S, 0)
FROM (/* weeks */) w
LEFT JOIN (/* incoming */) a ON a.W = w.W
LEFT JOIN (/* outgoing */) b ON b.W = w.W
ORDER BY w.W
date() accepts a Julian day number, so date(julianday(x) + 7) is "one week after x" without building a modifier string like '+' || n || ' days'.
The open amount itself is a derived table that is reused by four reports, so it lives in one entry:
(SELECT d.DOC_ID, d.CUSTOMER_ID, d.DOC_DATE, d.DUE_DATE,
d.GROSS_CENTS - COALESCE(p.PAID, 0) AS OPEN_CENTS
FROM SALES_DOC d
LEFT JOIN (SELECT DOC_ID, SUM(AMOUNT_CENTS) AS PAID
FROM PAYMENT WHERE IS_REVERSED = 0 GROUP BY DOC_ID) p
ON p.DOC_ID = d.DOC_ID
WHERE d.DOC_TYPE = 'INVOICE' AND d.STATUS IN ('ISSUED', 'PARTLY_PAID'))
3. A sales funnel that follows the documents
A funnel counts how many opportunities reached each stage. The naive version counts quotes, orders and invoices in the period - but those numbers are unrelated. Ten quotes and ten invoices don't mean the quotes turned into the invoices.
This funnel follows the chain: opportunity → its quote → an order created from that quote → an invoice created from that order. Each step is a join on the stored reference:
-- quotes
SELECT COUNT(*)
FROM OPPORTUNITY o
JOIN SALES_DOC q ON q.DOC_ID = o.QUOTE_ID AND q.DOC_TYPE = 'QUOTE'
WHERE substr(o.CREATED_AT, 1, 10) BETWEEN '2024-06-01' AND '2024-06-30';
-- invoices
SELECT COUNT(DISTINCT o.OPP_ID)
FROM OPPORTUNITY o
JOIN SALES_DOC r ON r.SOURCE_DOC_ID = o.QUOTE_ID AND r.DOC_TYPE = 'ORDER' AND r.STATUS <> 'CANCELLED'
JOIN SALES_DOC i ON i.SOURCE_DOC_ID = r.DOC_ID AND i.DOC_TYPE = 'INVOICE'
AND i.STATUS NOT IN ('DRAFT', 'CANCELLED')
WHERE substr(o.CREATED_AT, 1, 10) BETWEEN '2024-06-01' AND '2024-06-30';
COUNT(DISTINCT o.OPP_ID) matters: one order can be invoiced in parts, and three partial invoices must still count as one converted opportunity.
The test builds four opportunities: one with quote, order and invoice; one with only a quote; one without anything; one whose order was cancelled. Expected: 4 → 3 → 1 → 1, conversion 25 %.
4. Margin with a purchase price that may not exist
Gross margin per product is revenue minus quantity × purchase price. The purchase price is the one of the product's main supplier - and a product may have none. The rule is: no main supplier, cost 0. That is a visible choice, not an accident:
SELECT p.PRODUCT_ID, p.SKU, s.QTY, s.REV,
CAST(ROUND(s.QTY * COALESCE(c.PC, 0) / 1000.0) AS INTEGER) AS COST
FROM (SELECT l.PRODUCT_ID, SUM(l.QTY_MILLI) AS QTY, SUM(l.NET_CENTS) AS REV
FROM SALES_LINE l JOIN SALES_DOC d ON d.DOC_ID = l.DOC_ID
WHERE d.DOC_TYPE IN ('INVOICE', 'CREDIT') AND d.STATUS <> 'DRAFT'
AND l.PRODUCT_ID IS NOT NULL
GROUP BY l.PRODUCT_ID) s
JOIN PRODUCT p ON p.PRODUCT_ID = s.PRODUCT_ID
LEFT JOIN (SELECT PRODUCT_ID, MIN(PURCHASE_CENTS) AS PC
FROM SUPPLIER_PRODUCT WHERE IS_MAIN = 1 GROUP BY PRODUCT_ID) c
ON c.PRODUCT_ID = s.PRODUCT_ID
Quantities are stored in thousandths (QTY_MILLI), so the cost divides by 1000. Margin and percentage are computed one level up, on the named columns of this derived table, because SQLite doesn't let a select list refer to an alias defined in the same select list.
A product without main supplier now shows a 100 % margin, which is obviously wrong - and that's better than hiding it. The data quality report lists exactly these products as NO_MAIN_SUPPLIER.
Testing reports against a live database
Reports sum over everything, so a test can't simply expect "revenue = 10000" when other data exists. Two approaches cover it:
- Past periods only the tests use. The month-end and year-comparison tests put their documents into March 2024 and March 2023.
- Differences. The cockpit test reads all figures, inserts one invoice, one ticket, one opportunity and one stock movement, reads again and checks the deltas.
Takeaways
Aggregate in a derived table, then join - never join raw rows to a list of periods and group afterwards.
Generate small series with UNION ALL when you can't or won't use recursive CTEs.
Bucket dates with julianday, and catch negative differences before you cast.
Follow stored references for funnels, and count distinct starting objects.
Make defaults for missing data visible, then report them elsewhere.
