We have now learned how to create queries with SAP QuickView and SAP Query. As an example we used a SD Billing report. Why SD and not FI? Sales figures are pretty important for finance, but SAP does not provide sales reports with financial perspective. Accounts Receivable reports view sales as open items and payments.
Over the years I have created lots of queries for finance and auditors. Some examples are credit notes, export sales per country and currency, EU triangular sales, customer billing per profit center.
SD query serves well to present the basic functions of SAP Query, such as table joins. FI tables cannot be joined, why you need to use Logical Databases for financial queries.
Although creating a SAP Query might seem quite easy, a successful result requires understanding the data. You need to know, not only the tables used and how data is stored, also the logic of the application.
Especially SAP Sales Document queries of can be very challenging. Sometimes the query does not produce anything and sometimes the result is garbage. It pays off to plan the query carefully beforehand and validate the results many times. Even programmers, who make SD reports can easily go wrong.
Special problem areas are:
- organizational structures
The organizational structures in SD and FI are different. Our example query was made from the financial viewpoint, why we used the Company Code as organizational unit.
Company Code is not an organizational structure in sales. Sales clerks do not create sales orders to Company codes. They use Sales areas, which consist of Sales organization, distribution channel and division.
In configuration the Sales organization is assigned to the Company code.
- Assignments and hierarchies of master data
Both customers and materials are maintained on Sales area level.
The customer in SD has many partner roles, such as Sold-to party, Ship-to party, Bill-to party, Payer.
Hierarchies are closely connected with master data. Customers belong to customer hierarchy and materials to product hierarchy. Handling hierarchies in queries is awkward, if not impossible.
Because a query does not summarize rows, but shows all hits, there will often be dublicates. For example, if you make a query on Sales organization level, the customer 1000 in the picture appears twice. When you add distribution channel to the query, you can see the cause. The customer has sales on two different distribution channels (1000-10 and 1000-12).
You will get duplicates also, when data is read from the wrong level. Earlier we saw an example of this, when the net sales was taken from header table instead of row table. As a result we got a report, where the same invoice appeared four times.
When you add the net value from the item table to the query, you find the cause of the problem. In the first Net value column the total amount of the invoice is repeated. Maybe the goal was to create a report that has the total per customer broken down by invoice rows. However, this is not how it is done.
Avoid pitfalls
In our example we encountered the problem of the keyfigure signs. The values are stored in the database as absolute values without signs. In the previous presentations a couple of solutions were suggested.
Sales figures are stored only in document currency along with the exhange rate. If you need the amounts also in local currency, you need to calculate it using the exhange rates. To do that you need to know how SAP handles the currencies. It varies from currency to currency. The quotation method can be direct or indirect. Some currencies have no decimal places (Japanese Yen), while some have translation ratios to allow more decimals.
- Debit and Credit notes only as monetary compensation
There are different debit and credit notes. Usually the value, quantity and cost change. But sometimes only the monetary value is corrected.
In our QuickView query we joined the customer master table KNA1 to the billing tables in order to get the customer name in the result. This is not needed in SAP Query, where most characteristic have a text file attached.
It is not a good idea to join master data to sales tables. The master data could have changed after the sales transaction. The original values are saved in the sales documents. These values should be used in the queries.
Next, nonetheless, a presentation showing how to change the table join in SAP Query. The customer master table KNA1 is joided to the sales tables and the VAT number is added to the query.
COPA-reporting?
You should not try anything complicated with SAP Query. For more advanced sales reporting for example COPA is a better choice.
Especially well it suits for marging reporting. COPA reports are updated during billing run. Currencies and signs are automatically correct. Reporting is possible also on customer- and product hierarchy levels. For calculation margings key figure schemes can be set up.
Implementing COPA requires configuration but creating the reports is very easy. COPA reports are DrillDown reports, which are created similarly as Report Painter reports.
Summary
I hope you have enjoyed my earlied posts and have already practiced creating your own queries. Don’t let setbacks discourage. With persistence and retrial you’ll become the Query Master, who is believed to know “everything about SAP”.
