Income Statement in SAP Business One: Build it with SQL Queries - featured image

Publications

Income Statement in SAP Business One: Building it with SQL Queries

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

The same scene plays out every month: management asks for the income statement in SAP Business One and accounting replies that it will be ready «when the period closes». Meanwhile, decisions are made blind or, worse, with hand-built Excel sheets nobody can audit. Yet all the information is already in your database, recorded journal entry by journal entry. The problem, therefore, is not data: it is access.

In this tutorial you will build your own income statement with SQL queries straight against the general ledger, ready to run in Query Manager on both SQL Server and SAP HANA. That way you will have net profit, revenue and expenses up to date, without waiting for the formal close.

Why build your income statement in SAP Business One with SQL?

SAP Business One includes a standard profit and loss report in the Financials module. It is accurate and audited, but rigid: you cannot combine it with other sources, schedule it into alerts, or feed an external dashboard. On top of that, it requires navigating screens a commercial manager will never open.

A SQL query, by contrast, gives you three concrete advantages. First, speed: the month's result appears in seconds, any day of the month. Second, flexibility: you can cross revenue with profit centers, months or branches in a single view. Finally, automation: the same query can feed SAP B1 internal alerts, a connected Excel file or your BI tool.

income statement in SAP Business One - accounting analysis with calculator and financial reports
The income statement stops being a monthly document and becomes a daily query.

Prerequisites

  • SAP Business One 9.3 or higher (SQL Server or HANA).
  • Access to the Query Manager (Tools → Queries → Query Generator).
  • A chart of accounts with properly defined account types: income statement accounts must be flagged as Revenue o Expense.
  • Basic SQL knowledge (SELECT, JOIN, GROUP BY).

The three tables that feed the report

Every income statement in SAP Business One comes from the general ledger, not from sales documents. So you only need three tables:

  • OACT: the chart of accounts. The field ActType classifies each account: I for revenue, E for expenses and N for balance sheet accounts.
  • OJDT: the header of the journal entries, with the posting date (Reference Date).
  • JDT1: the lines of each journal entry, with debits, credits, account and profit center.

One key detail: revenue increases on the credit side, so its balance is calculated as Credit - Debit. Expenses, by contrast, increase on the debit side: Debit - Credit. As a result, respecting this sign convention is what makes your net profit tie out with the official report.

Step-by-step tutorial

Step 1: Detailed income statement by account

Let us start with the basics: the balance of each income statement account over a date range. Open Query Manager, create a new query and paste the code for your database engine. The variables [%0] and [%1] will prompt for the from and to dates when you run it.

SQL Server

-- Estado de resultados detallado por cuenta (SQL Server)
SELECT
    T0.AcctCode AS [Cuenta],
    T0.AcctName AS [Nombre],
    CASE T0.ActType WHEN 'I' THEN 'Ingreso' ELSE 'Gasto' END AS [Tipo],
    SUM(CASE WHEN T0.ActType = 'I'
        THEN T1.Credit - T1.Debit
        ELSE T1.Debit - T1.Credit END) AS [Saldo]
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
WHERE T0.ActType IN ('I','E')
  AND T2.RefDate BETWEEN [%0] AND [%1]
GROUP BY T0.AcctCode, T0.AcctName, T0.ActType
HAVING SUM(CASE WHEN T0.ActType = 'I'
    THEN T1.Credit - T1.Debit
    ELSE T1.Debit - T1.Credit END) <> 0
ORDER BY T0.ActType DESC, T0.AcctCode

SAP HANA

-- Estado de resultados detallado por cuenta (SAP HANA)
SELECT
    T0."AcctCode" AS "Cuenta",
    T0."AcctName" AS "Nombre",
    CASE T0."ActType" WHEN 'I' THEN 'Ingreso' ELSE 'Gasto' END AS "Tipo",
    SUM(CASE WHEN T0."ActType" = 'I'
        THEN T1."Credit" - T1."Debit"
        ELSE T1."Debit" - T1."Credit" END) AS "Saldo"
FROM OACT T0
INNER JOIN JDT1 T1 ON T0."AcctCode" = T1."Account"
INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId"
WHERE T0."ActType" IN ('I','E')
  AND T2."RefDate" BETWEEN [%0] AND [%1]
GROUP BY T0."AcctCode", T0."AcctName", T0."ActType"
HAVING SUM(CASE WHEN T0."ActType" = 'I'
    THEN T1."Credit" - T1."Debit"
    ELSE T1."Debit" - T1."Credit" END) <> 0
ORDER BY T0."ActType" DESC, T0."AcctCode"

Run the query with the first and last day of the month. Then compare the total against the standard profit and loss report: they must match to the cent. If they do not, check that no income statement account is misclassified in the chart of accounts.

Step 2: Executive summary with net profit

The account-level detail is useful for accounting; management, however, wants three numbers: revenue, expenses and profit. This version delivers exactly that.

SQL Server

-- Resumen ejecutivo del período (SQL Server)
SELECT
    SUM(CASE WHEN T0.ActType = 'I' THEN T1.Credit - T1.Debit ELSE 0 END) AS [Ingresos],
    SUM(CASE WHEN T0.ActType = 'E' THEN T1.Debit - T1.Credit ELSE 0 END) AS [Gastos],
    SUM(CASE WHEN T0.ActType = 'I' THEN T1.Credit - T1.Debit
             ELSE -1 * (T1.Debit - T1.Credit) END) AS [Utilidad Neta]
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
WHERE T0.ActType IN ('I','E')
  AND T2.RefDate BETWEEN [%0] AND [%1]

SAP HANA

-- Resumen ejecutivo del período (SAP HANA)
SELECT
    SUM(CASE WHEN T0."ActType" = 'I' THEN T1."Credit" - T1."Debit" ELSE 0 END) AS "Ingresos",
    SUM(CASE WHEN T0."ActType" = 'E' THEN T1."Debit" - T1."Credit" ELSE 0 END) AS "Gastos",
    SUM(CASE WHEN T0."ActType" = 'I' THEN T1."Credit" - T1."Debit"
             ELSE -1 * (T1."Debit" - T1."Credit") END) AS "Utilidad Neta"
FROM OACT T0
INNER JOIN JDT1 T1 ON T0."AcctCode" = T1."Account"
INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId"
WHERE T0."ActType" IN ('I','E')
  AND T2."RefDate" BETWEEN [%0] AND [%1]
income statement in SAP Business One - dashboard with financial indicators and charts
Three numbers management wants to see every Monday: revenue, expenses and profit.

Step 3: Month-by-month comparison for the current year

A number on its own says little. The next level, then, is seeing the month-by-month evolution of the current year in a single table. That way you spot trends before they turn into problems.

SQL Server

-- Evolución mensual del año en curso (SQL Server)
SELECT
    MONTH(T2.RefDate) AS [Mes],
    SUM(CASE WHEN T0.ActType = 'I' THEN T1.Credit - T1.Debit ELSE 0 END) AS [Ingresos],
    SUM(CASE WHEN T0.ActType = 'E' THEN T1.Debit - T1.Credit ELSE 0 END) AS [Gastos],
    SUM(CASE WHEN T0.ActType = 'I' THEN T1.Credit - T1.Debit
             ELSE -1 * (T1.Debit - T1.Credit) END) AS [Utilidad]
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
WHERE T0.ActType IN ('I','E')
  AND YEAR(T2.RefDate) = YEAR(GETDATE())
GROUP BY MONTH(T2.RefDate)
ORDER BY [Mes]

SAP HANA

-- Evolución mensual del año en curso (SAP HANA)
SELECT
    MONTH(T2."RefDate") AS "Mes",
    SUM(CASE WHEN T0."ActType" = 'I' THEN T1."Credit" - T1."Debit" ELSE 0 END) AS "Ingresos",
    SUM(CASE WHEN T0."ActType" = 'E' THEN T1."Debit" - T1."Credit" ELSE 0 END) AS "Gastos",
    SUM(CASE WHEN T0."ActType" = 'I' THEN T1."Credit" - T1."Debit"
             ELSE -1 * (T1."Debit" - T1."Credit") END) AS "Utilidad"
FROM OACT T0
INNER JOIN JDT1 T1 ON T0."AcctCode" = T1."Account"
INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId"
WHERE T0."ActType" IN ('I','E')
  AND YEAR(T2."RefDate") = YEAR(CURRENT_DATE)
GROUP BY MONTH(T2."RefDate")
ORDER BY "Mes"

Step 4: Income statement by profit center

Here is the real jump in value: knowing which line of business makes money and which loses it. If you record profit centers in your journal entries, this query breaks profit down by each one. It also exposes the lines posted with no center assigned, a very common silent error.

SQL Server

-- Utilidad por centro de beneficio (SQL Server)
SELECT
    ISNULL(T1.ProfitCode, 'SIN CENTRO') AS [Centro],
    ISNULL(T3.PrcName, 'Sin asignar') AS [Nombre Centro],
    SUM(CASE WHEN T0.ActType = 'I' THEN T1.Credit - T1.Debit ELSE 0 END) AS [Ingresos],
    SUM(CASE WHEN T0.ActType = 'E' THEN T1.Debit - T1.Credit ELSE 0 END) AS [Gastos],
    SUM(CASE WHEN T0.ActType = 'I' THEN T1.Credit - T1.Debit
             ELSE -1 * (T1.Debit - T1.Credit) END) AS [Utilidad]
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
LEFT JOIN OPRC T3 ON T1.ProfitCode = T3.PrcCode
WHERE T0.ActType IN ('I','E')
  AND T2.RefDate BETWEEN [%0] AND [%1]
GROUP BY T1.ProfitCode, T3.PrcName
ORDER BY [Utilidad] DESC

SAP HANA

-- Utilidad por centro de beneficio (SAP HANA)
SELECT
    IFNULL(T1."ProfitCode", 'SIN CENTRO') AS "Centro",
    IFNULL(T3."PrcName", 'Sin asignar') AS "Nombre Centro",
    SUM(CASE WHEN T0."ActType" = 'I' THEN T1."Credit" - T1."Debit" ELSE 0 END) AS "Ingresos",
    SUM(CASE WHEN T0."ActType" = 'E' THEN T1."Debit" - T1."Credit" ELSE 0 END) AS "Gastos",
    SUM(CASE WHEN T0."ActType" = 'I' THEN T1."Credit" - T1."Debit"
             ELSE -1 * (T1."Debit" - T1."Credit") END) AS "Utilidad"
FROM OACT T0
INNER JOIN JDT1 T1 ON T0."AcctCode" = T1."Account"
INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId"
LEFT JOIN OPRC T3 ON T1."ProfitCode" = T3."PrcCode"
WHERE T0."ActType" IN ('I','E')
  AND T2."RefDate" BETWEEN [%0] AND [%1]
GROUP BY T1."ProfitCode", T3."PrcName"
ORDER BY "Utilidad" DESC

Variations and use cases

Once you master the base structure, the variations are straightforward. These four, for example, are the ones management asks for most:

  • Comparison against the prior year: duplicate the columns with a second CASE filtering by YEAR(T2.RefDate) of the previous year. As a result, you get absolute and percentage variance on the same row.
  • Gross margin broken out: if your chart of accounts separates cost of sales (by segment or account range), add an intermediate category between revenue and operating expenses.
  • Filter by branch: add the condition on T1.BPLId if you work with multi-branch enabled.
  • Feed a dashboard: save the query as a view in the database and connect it to Excel or to your BI tool. That way the income statement updates itself.
income statement in SAP Business One - printed charts of monthly financial analysis
From the journal entry to management analysis: the same source, a different speed.

Best practices and common mistakes

Before presenting these numbers to management, run through this list. Most differences against the official report start here:

  • Misclassified accounts: if an income statement account was left as type N (balance sheet), it will not appear in the query. So audit the chart of accounts before trusting the report.
  • Year-end closing entry: the closing entry transfers results to equity. Consequently, if you query a year that is already closed, exclude that entry (field BtfStatus in OJDT, or the date range).
  • Using documents instead of the ledger: adding up invoices (OINV) is not an income statement: it ignores manual credit notes, accruals, depreciation and adjustments. The general ledger is the only complete source.
  • Currency: the queries use local currency (Debit and Credit). For system currency, use SYSDeb and SYSCred.
  • Inverted signs: if your revenue shows up negative, check the convention Credit - Debit. Do not flip signs «by eye»: validate against the standard report.

Official SAP resources

Conclusion

The income statement in SAP Business One does not have to wait for the accounting close. With four queries — detail by account, executive summary, monthly evolution and profit centers — you turn the general ledger into a decision dashboard available any day of the month. And every version works on both SQL Server and HANA, with no outside consultants.

Start today: run Step 1, tie the total to the official report and then move on to profit centers. But do not stop at the query: the real transformation happens when management stops waiting ten days for a number that was available on day one. If this tutorial saved you hours, share your result in the comments and tell me which report you want to master in the next installment of the Círculo Estratégico.

Most Read

Ballena jorobada bajo el agua con su aleta pectoral extendida: la esperanza en la tecnología y la biomimética
La ballena que volvió: la esperanza en la tecnología y la que no cabe en un tablero
SAP data in ChatGPT: an open door at the far end of a dark room
The Tab Next Door: The SAP Data in ChatGPT That Nobody Authorized
SAP Business One Purchase Price Variance - featured image of the tutorial
SAP Business One Purchase Price Variance: Detect the Inflation in Your Buying Costs
QlikSense format changeovers: actual minutes against the standard
QlikSense Format Changeovers: the Minutes Nobody Bills
QlikSense buttons and actions: executive dashboard with indicators
QlikSense Buttons and Actions: the Dashboard that Runs the Meeting
SAP Business One Internal Reconciliation - featured image of the tutorial
SAP Business One Internal Reconciliation: Clear False Open Items

RELATED ARTICLES