Journal Entry Testing (JET) contains standard analyses for use in auditing and especially in the context of annual audits. JET analyzes the accounting material in the accounts with regard to matters which are of general interest, i.e. those which are not tailored to the specifics of the company: the largest postings, most frequent postings, postings at weekends or strange posting text entries and much more. In this series of blog posts, I am going to show you how to do JET analysis in SAP directly in SQL. This is quick to do and allows you to obtain results in a very short timeframe!

How can Journal Entry Testing be performed efficiently?

Journal Entry Testing (JET) analyses are standard analyses. If you look at a standard ERP system like SAP, you can soon see why some automation is worthwhile. It is often a very tedious task to obtain the data and import it into an analysis tool such as Excel. It often takes longer to obtain the data than to perform the analyses themselves! This is the reason why, in this blog series, I am going to show you how to perform such analyses directly in SQL. Then you no longer need to extract the data from the SAP system and you can save yourself the tedious task of data procurement.

Of course, you need the relevant access privileges to the SAP database. This access is often well protected, but there is a noticeable trend that, with the new SAP HANA, it is becoming increasingly easier and more common to have direct access to the SAP database, for example with the SAP HANA Studio:


Journal Entry Testing in SAP HANA Studio


Otherwise, you can also execute SQL commands directly using the “DBACOCKPIT” transaction and navigating via “Diagnostics” to “SQL Editor”.


What are the most commonly used Journal Entry Testing analyses?

There are no binding standard analyses for Journal Entry Testing. However, a glance at the literature quickly provides an insight into which analyses are used most commonly. So we have done just that and referred to the book entitled "Journal Entry Testing" by Kersten Christian Droste and Jonas Tritschler, recently published by IDW-Verlag in 2018 (in German).

The following JET analyses are proposed there:


These are certainly not all conceivable JET analyses, but undoubtedly many very important ones. We also think that the following topics belong to the Journal Entry Testing:


This is certainly not an exhaustive list of all conceivable JET analyses, but it undoubtedly covers most of the important ones.


A First JET Analysis - Document Type Statistics

As an introduction to a JET analysis, I recommend gaining an overview of document types and the frequency of use of document types. This will give you a good initial insight into which business transactions are occurring and how often.

The corresponding SQL on a SAP HANA database is:




Replace BUKRS='1000' with your company code and GJAHR=2018 with your fiscal year.

On my test data set, for example, this gives the following output:


KR Vendor invoice 1526 5
SA G/L account document 1176 10
AB Accounting document 1124 6
WE Goods receipt 528 15
RV Billing doc.transfer 432 7
... ... ... ...


A visualization in Excel illustrates the situation as follows:


Absolute frequency of document types for journal entry testing in SAP


From this bar chart, you can see very well which business transactions occur frequently and which ones are rather rare.

In zap Audit, such overview-like analyses are automatically generated in the dashboard. Which means that, thankfully, you don't have to be familiar with SQL and you don't even have to use Excel:


document types for journal entry testing


Our series on Journal Entry Testing is to be continued in future blog posts…!


nick-profil-2x (1)

About the author: 

Prof. Dr. Nick Gehrke is a member of the board of the Artificial Intelligence Centre Hamburg, and a professor and course leader at the Nordakademie Hamburg, and Co-Founder and Data Scientist at zapliance. He started zapliance after meeting Alexander Rühle at PWC and set their eyes on the same goal: Changing the way business professionals of the future work with data. 

zapliance Newsletter label

Sign up to our newsletter
to receive latest news and updates every two weeks!

zapliance customer brands

Our recommendations to you