topic Re: Ledger Report in Application Development and Automation Discussions

Ledger Report

Former Member — Mon, 19 May 2008 06:09:22 GMT

Hi experts, i'm new in ABAP programming. Can anybody give me a better query of the following condition.

1. sum amount(DMBTR) in this row if document type is KZ. Include amount if document number exist in BSAS, BSIS, BSAK and BSIK.

2. sum amount(DMBTR) in this row if document type is KR. Include amount if document number exist in BSAS, BSIS, BSAK and BSIK.

3. sum amount(DMBTR) in this row if document type is DR. Include amount if document number exist in BSAS, BSIS, BSAD and BSID.

4.sum amount(DMBTR) in this row if document type is DZ. Include amount if document number exist in BSAS, BSIS, BSAD and BSID.

5.sum amount(DMBTR) of all other document types. Include amount if document number exist in BSAS, BSIS, BSAK and BSIK, BSAD and BSID.

6. sum amount(DMBTR) if document number exist in BSAS, BSIS but not in BSAK, BSIK, BSAD and BSID.

Any satisfactory reply will be given points.

Please guys i really need your help.

Re: Ledger Report

Former Member — Mon, 19 May 2008 07:03:58 GMT

My feeling is that you are probably better off describing the business requirement than the proposing these various table reads... my instinct is that your report may well work better if you were to start with BKPF (document header) and then the corresponding BSEG entries... the data in the BSI* and BSA* index tables comes from these two sources so if your business requirement is something like "for documents in company code XYZ posted to SAP between 01.01.2008 and 31.01.2008, provide cleared and open item totals for the following document types" then you should find that a select from BKPF via the BUKRS+CPUDT key followed by an efficient access into BSEG will probably give you the data you need... but try rephrasing your question and you should get some more relevant assistance.

To help you along your way, it's worth noting that the table names you mentioned follow a certain pattern - all of them start "BS" and the 3rd character is "I" if the item is uncleared (e.g. the AUGDT clearing date will be empty) or "A" if the item is cleared... and the 4th character is based on the "account type" (field KOART) in BSEG e.g. "K" = Kreditor... thus BSIK = Open vendor items, and BSAD = cleared debtor items. So rule 4, for example, could be rephrased as "4. if BKPF-BLART (document type) is DZ and BSEG-KOART = 'D' or BSEG-KOART = 'S' then add BSEG-DMBTR to sum4".

Jonathan

Re: Ledger Report

Former Member — Tue, 20 May 2008 02:21:03 GMT

Thanks for the reply sir.

But sir we are talking of over 10 million records in the table bseg. Is there any alternative way of minimizing performance by accessing those tables excluding BSEG. The parameters are bukrs, budat ang gjahr. I currently using the query to bseg table.

Re: Ledger Report

Former Member — Tue, 20 May 2008 02:35:56 GMT

That's why I suggest going through BKPF to restrict your initial selection first - and then you go to BSEG for the document numbers you have identified. There is a BKPF index on BUKRS + BSTAT + BUDAT so you should be able to bring the matching document list down very fast, then you can do blocks of selects back to BSEG for the document number (BUKRSBELNRGJAHR) you have identified, and sum the results.

And if you have 10 million records in BSEG, then it's quite likely you have nearly the same number of records split between the BSI* and BSA* tables.

Jonathan