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.

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
ActTypeclassifies each account:Ifor revenue,Efor expenses andNfor 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]

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.BPLIdif 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.

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
BtfStatusin 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 (
DebitandCredit). For system currency, useSYSDebandSYSCred. - 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
- Official SAP Business One documentation on the SAP Help Portal
- SAP Community: the SAP Business One forum
- JDT1 table reference in the SAP Business One SDK
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.




