Data Export

When you need direct SQL access to the POS — usually because APRO is installed on-premise alongside existing systems — the cash register database exposes a stable set of read-only views. They are intentionally shaped for the two most common consumers:

• On-premise integrations — accounting bridges, kitchen displays, stock management, loyalty engines — anything that runs inside the same network and wants a low-latency feed without going through REST.
• Existing BI workflows — Power BI, Tableau, Qlik, Metabase, Excel, native SQL Server Analysis Services — any tool that already understands SQL Server connections. Point the connector at the views and you get receipts, line items, payments and product master in the shape a data warehouse expects.

Views are read-only, queryable over the standard SQL Server protocol, and never join to operational tables that mutate under load — they are safe to drive dashboards and scheduled ETL jobs against.

Tip: SQL Views or Revenue API?
The SQL views and the Revenue API expose the same
underlying data — pick whichever fits the runtime:

- SQL Views (this page) — flat, relational, on-premise.
Perfect for ETL, Power BI / Tableau / Qlik / Metabase connectors and
any workflow that wants bulk access via SQL.
- Revenue API — served over the cloud endpoint my.apro.at, returns
a hierarchical JSON document with positions, payments and tax
already nested per receipt. Better for real-time system-to-system
integration, SaaS consumers and anything outside the local network.

Note
The read-only SQL user is provisioned per deployment. Ask your APRO
account manager to enable access for your tenant and to share the
server name, database, and credentials.

Overview

1. vw_Receipts

One row per business transaction. The TransactionType column distinguishes what the row represents; amounts are signed — reversal transactions (TransactionType = 4) return the booking with a negated amount so a plain SUM(TotalAmount) already nets them out.

Transaction types

Only transactions with a TransactionType in (1, 2, 3, 4, 5, 14, 24, 25) are exposed through this view. The authoritative mapping lives in vw_TransactionTypes and is also joined onto every row via TransactionTypeName.

2. vw_Receipt_Items

Line items. Join to vw_Receipts on ReceiptId / TransactionId. Values are signed the same way as the parent receipt.

3. vw_Payments

Payments recorded against receipts. Only settlements and their reversals are exposed (TransactionType in (3, 4, 5, 14, 24, 25)).

4. vw_Transactions

Light index of every transaction. Useful for watermark-based ETL (pulling only new rows since the last checkpoint).

5. vw_TransactionTypes

Lookup for every transaction type the POS emits.

6. vw_Bi_Export

The one-stop view for BI tools. Denormalized join of every turnover-relevant receipt with every line item, plus a comma-separated summary of the payment methods used on the receipt. Point Power BI or Tableau at this view and you have everything needed for revenue, margin, mix and payment-method dashboards without writing joins.

Columns are the union of vw_Receipts and vw_Receipt_Items (minus TransactionTypeId, which lives only on the receipt side), plus:

Power BI example

1. Get data → SQL Server database — server + database as provided.
2. Import or DirectQuery mode, using the read-only user.
3. Pick dbo.vw_Bi_Export as the fact table.
4. Optionally add dbo.vw_Transactions, dbo.vw_Payments, dbo.vw_products_and_categories as companion tables and relate on TransactionId / ProductId.

Import mode is normally sufficient — the views are already aggregated to transaction grain and refresh in seconds for a typical venue.

7. vw_products_and_categories

Product master with category. Useful as a dimension alongside vw_Bi_Export when the BI tool wants product names to stay stable even if historical rows predate a rename.

Only products where Geloescht = 0 (not soft-deleted) are included.

Example queries

Turnover per business day, split by tax rate — for a monthly KPI chart:

SELECT
  r.BusinessDay,
  ri.TaxName,
  SUM(ri.EffectiveAmount) AS net_turnover
FROM vw_Receipts AS r
JOIN vw_Receipt_Items AS ri ON ri.ReceiptId = r.Id
WHERE r.TransactionType IN (3, 4, 5)   -- settlements + reversals net out
  AND r.BusinessDay BETWEEN @from AND @to
GROUP BY r.BusinessDay, ri.TaxName
ORDER BY r.BusinessDay, ri.TaxName;

Top-10 products for the current month, using the BI view directly:

SELECT TOP 10
  ProductName,
  SUM(Quantity)        AS units_sold,
  SUM(EffectiveAmount) AS net_turnover
FROM vw_Bi_Export
WHERE BusinessDay >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND TransactionType IN (3, 4, 5)
GROUP BY ProductName
ORDER BY net_turnover DESC;

Cash vs. card split for a single business day:

SELECT
  PaymentTypeName,
  SUM(TotalAmount) AS total
FROM vw_Payments
WHERE CAST(DateTime AS date) = @businessDay
GROUP BY PaymentTypeName;