SAP Query: Creating a billing report

When you get familiar with the QuickViewer (SQVI), you probably would like overcome its restrictions and share your reports with other users. Allthough it is possible to convert the SQVI reports to SAP Queries,  maybe you’d rather  like to learn how to use use the SAP Query.

You create a SAP Query the same way as QuickViewer queries. The biggest difference is that you need first to create an  InfoSet that forms the basis of every query. Here you select the data source (tables, table joins, logical databases) and define the list and selection fields.  The information requirements of the users define the InfoSets.

SQ_InfoSet_Salesreport

SAP Query offers a broad range of ways to define reports. You can create different types of reports such as basic lists, statistics, and ranked lists. You are also able to drill down to documents and treat the problems with signs, You don’t need coding skills to do this, but it is also possible to add pieces of code to your queries.

Usually a reporting requirement, which the existing standard reports  cannot solve, triggers the need for a Query. Before jumping into details you should make a rough sketch. What information the report should contain, and how it should look. Next you need to find out where to find this information. Use Se16 and SQVI to help you. When you have are sure of the report, continue with InfoSet definition. Choose the infosource and select the fields. The InfoSet is assigned to a User Group. So, you must already have one or create a new. When all this is ready, you can start to create your query.

Query environment

It is not a good idea to do the queries directly in production. Usually this is also prevented by IT. Development is not so good either, because there is no data to test the query. You can create the queries in the test system. When they pass the tests,  and function without problems, you can copy them to production. It is also a good idea to copy them to development also.

SQ_phases

Steps to create a SAP Query

 

SAP Query Step-by-Step Guide:

Creating Reports with SAP Query < HTML>

SAP Query Key figure signs

SAP Query key figure signs

SAP stores all the SD key figures (net value, weight, volume, cost) in the data base as absolute values. In my billing query I had an invoice of 100 and a credit note of 100. Both with positive signs. In the query their sum was 200. Obviously I need to do something.

In the accounting document there is a special field(SHKZG) that indicates whether it is as  debit or credit posting. Is there something similar in billing document?

In the billing header table (VBRK) there are no fields that uniquely determine the sign. Only the Billing Type (VBRK-FKART) and Document Category (VBRK-VBTYP) could be used for this purpose. Disadvantage is that you need to know, which types are used and which of them produce negative values.

In tSAP Query billing: SHKZhe item table (VBRP) there is are promising field VBRP-SHKZG with somewhat strange name ‘Returns Item’. In accounting document the field  SHKZG indicates debit and credit. A closer look at VBRP reveals that field SHKZG does  identify the sign also in sales documents. Value X indicates  that the value is negative. Multiplication with -1 corrects the sign in the report.

As I was unsure of the alternative values (A,B,X) I used QuickViewer to check the values in IDES and a couple of productive ECC systems. Billing types RE, G2, S1 all had value X in this field.

Local Field solution

First I tried to solve the sign problem at query level using Local fields. The formulas for both Billing type (VBRK-FKART) and Returns Item (VBRP-SHKZG) worked fine. The shortage of Local field solution is the need to define them separately in every query.

Additional Field solution

The ideal solution would be to include the definition in the InfoSet, but it requires coding. In Stephan Kaleske’s excellent book ‘SAP Query Reporting’ I learned how this is done using field SHKZG. I am borrowing the code  in my query.

You can check the result in the picture above.

Below you find two documents that describe both solutions.

 

 SAP Query VBRK_VBRP: Determine Key Figure signs using Local fields 

 SAP Query VBRK_VBRP: Determine Key Figure signs using Additional Fields

SAP Query: additional fields (Month, Year)

SAP Query: additional fields (posting period, fiscal year)

Accounting asks you to add month (posting period) and year (fiscal year) to the billing query. You find both fields in the billing header table VBRK.  The bad thing is that both Posting period (POPER) and Fiscal year (GJAHR) are empty. What to do? You can add these fields to the query using SAP Query: additional fields functionality.

Invoice date is the only date in the query . To get the figures for a certain month or year, you must enter a date intervall in the report selection. Totals or subtotals by period are no possible. The period  is not a  possible selection criteria, either.

Is it possible to derive the period from the billing date? SAP Query: additional fields solves the problem. In the example below we add two additional fields (month and year) to the query. The values for these fields come from the invoice date VBRP-FKDAT, which is in the database in format YYYMMDD. The first four characters (YYYY) define the year and the next two (MM) the month. With a small code snippet these can be added to the infoset.

Additional fields (posting period and fiscal year) <HTML>