For many busi­ness­es, one of the fore­most report­ing needs is to mea­sure month­ly sales through­out the year and com­pare those results to the pre­vi­ous year. This fun­da­men­tal analy­sis of whether sales are trend­ing up or down is often the start­ing point for dis­cov­er­ing impor­tant insights that shape your path forward. 

For SAP Busi­ness One cus­tomers run­ning on Microsoft SQL Serv­er, the eas­i­est way to begin this analy­sis is to extract the rel­e­vant data through a SQL query. From there, you can visu­al­ize and ana­lyze the data using Microsoft Excel or Pow­er BI, SAP Ana­lyt­ics solu­tions, or oth­er solu­tions like Tableau. 

Using a Query to Access Your SAP Busi­ness One Data

To help you get start­ed, I have draft­ed the sam­ple query below. You can use this query with the built-in SAP Busi­ness One Query Tools. 

Notes on the Query

• My query was writ­ten using sam­ple data and is designed to com­pare data from 2017 with 2016, so you will like­ly want to edit the years to meet your requirements. 
• This data is being pulled from the Jour­nal Entry tables in SAP Busi­ness One, which are part of the Gen­er­al Ledger.
• Cus­tomers run­ning SAP Busi­ness One ver­sion for SAP HANA, should refer to this post on the dif­fer­ences between SQL Serv­er and HANA.

Tables Used

• OCRD — Busi­ness Part­ners (Mas­ter Data)
• JDT1 — Jour­nal Entries (Detail)

Why pull trans­ac­tion details from the Gen­er­al Ledger?

All trans­ac­tions end up in the Gen­er­al Ledger, so it is the most reli­able source for queries where you want to ensure the num­bers 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 Aver­age Sales’,

(SUM(T2.Debit) — sum(T2.Credit)) AS ​‘2018’,

((SUM(T2.Debit) — sum(T2.Credit))/Month(GetDate()))AS ​‘2018 Aver­age Sales’

FROM dbo.OCRD T0

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

Due­date) = 2017 AND T1.TransType in (‘13’,‘14’)

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

Due­date) = 2018 AND T2.TransType in (‘13’,‘14’)

WHERE T0.CardType = ​‘C’

Group By T0.CardCode, T0.CardName