Useful SAP Business One Queries: Comparing Monthly Sales Across Multiple Years

Richard Duffy
Richard Duffy in SQL Queries, Microsoft SQL Server June 26, 2018

For many businesses, one of the foremost reporting needs is to measure monthly sales throughout the year and compare those results to the previous year. This fundamental analysis of whether sales are trending up or down is often the starting point for discovering important insights that shape your path forward.  

For SAP Business One customers running on Microsoft SQL Server, the easiest way to begin this analysis is to extract the relevant data through a SQL query. From there, you can visualize and analyze the data using Microsoft Excel or Power BI, SAP Analytics solutions, or other solutions like Tableau. 

Using a Query to Access Your SAP Business One Data

To help you get started, I have drafted the sample query below. You can use this query with the built-in SAP Business One Query Tools.

Notes on the Query

  • My query was written using sample data and is designed to compare data from 2017 with 2016, so you will likely want to edit the years to meet your requirements. 
  • This data is being pulled from the Journal Entry tables in SAP Business One, which are part of the General Ledger.
  • Customers running SAP Business One version for SAP HANA, should refer to this post on the differences between SQL Server and HANA.

Tables Used

  • OCRD - Business Partners (Master Data)
  • JDT1 - Journal Entries (Detail)
Why pull transaction details from the General Ledger?

All transactions end up in the General Ledger, so it is the most reliable source for queries where you want to ensure the numbers are accurate.

The SQL Query Code

SELECT T0.CardCode, T0.CardName,

(SUM(T1.Debit) - sum(T1.Credit)) AS '2017',

((SUM(T1.Debit) - sum(T1.Credit))/12) AS '2017 Average Sales',

(SUM(T2.Debit) - sum(T2.Credit)) AS '2018',

((SUM(T2.Debit) - sum(T2.Credit))/Month(GetDate()))AS '2018 Average Sales'

FROM dbo.OCRD T0

LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1.

Duedate) = 2017 AND T1.TransType in ('13','14')

LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Year(T2.

Duedate) = 2018 AND T2.TransType in ('13','14')

WHERE T0.CardType = 'C'

Group By T0.CardCode, T0.CardName