SUM through RBDRSEG lines in ABAP Query

Question

I am having trouble figuring out where to start/how to get the correct output. I am very new to ABAP coding. I am trying to create an Infoset query and need to do a bit of coding in SQ02.

I have two tables joined - one being RBKP as the header for Invoice Receipts the other is RBDRSEG for the Invoice Document Items.

The query needs to run following some irrelevant parameters/variants, but when it does so it needs to - - -

Look in RBDRSEG for all same Document numbers RBKP-BELNR EQ RBDRSEG-RBLNR

In doing so RBDRSEG may or may not have multiple line results for each Doc No.

I need to total the field RBDRSEG-DMBTR for each Doc No. Result. (If there a 5 lines for a Doc. No.; DMBTR will have a different value for each that need to be totaled)

At this point I need the output to only show (along with other fields in RBKP) One line with the SUM of the DMBTR field for each Doc. No.

I then need to have another field showing the difference of the Field RBKP - RMWWWR which is the Invoice Total and the Total that was calculated earlier for that Doc. No. for field DMBTR.

If you could help, I would be incredibly grateful.

Answer 1

first you need to define a structure that will contain your selection data. An example structure for your requirement may look like this:

don't forget to activate the structure and make sure it doesn't contain errors.

now create the selection report. To use a report as the data selection method, you need to add two comments, *<QUERY_HEAD> and *<QUERY_BODY>. *<QUERY_HEAD> has to be placed where your start-of-selecton usually would go, *<QUERY_BODY> inside a loop that puts the selected lines into an internal table with the same name as the structure you defined in SE11.

I made an example report to show how this would work:

REPORT  ZSTACK_RBKP_INFOSET_QUERY.

tables:
  rbkp,
  ZSTACK_RBKP_INFOSET_STR.

select-OPTIONS:
  so_belnr for rbkp-belnr,
  so_gjahr for rbkp-gjahr.

data:
      itab type standard table of ZSTACK_RBKP_INFOSET_STR,
      wa_itab type ZSTACK_RBKP_INFOSET_STR.

data:
      lv_diff type dmbtr.

*here your selection starts.

*<QUERY_HEAD>

select rbkp~belnr
   rbkp~gjahr
   rbkp~rmwwr
   rbkp~waers
   sum( RBDRSEG~DMBTR ) as DMBTR
   from RBKP left outer join RBDRSEG
     on RBDRSEG~RBLNR eq RBKP~BELNR and
        RBDRSEG~RJAHR eq RBKP~GJAHR
   into corresponding fields of table itab
  where rbkp~belnr in so_belnr and
    rbkp~gjahr in so_gjahr
  group by rbkp~belnr rbkp~gjahr rbkp~rmwwr rbkp~waers.


loop at itab into wa_itab.

  lv_diff = wa_itab-dmbtr - wa_itab-rmwwr.

  move lv_diff to wa_itab-diff.
  modify itab from wa_itab.

endloop.



* this is the part that forwards your result set to the infoset
LOOP AT itab INTO ZSTACK_RBKP_INFOSET_STR.


*<QUERY_BODY>

ENDLOOP.

the sample report first selects the RBKP lines along with a sum of RBDRSEG-DMBTR for each document in RBKP. After that, a loop updates the DIFF column with the difference between the selected columns RMWWR and DMBTR.

Unfortunately in our SAP System the table RBDRSEG is empty, so I can't test that part of the report. But you can test the report in your system by just adding a break point before the first loop and then start the report. You should then be able to have a look at the selected lines in internal table ITAB and see if the selection works as expected.

caveats in the example report: both RBKP and RBDRSEG reference different currency fields. So it may be possible your values in RMWWR and DMBTR are in different currencies (RMWWR is in document currency, DMBTR seems to be in default company currency). If that can be the case, you will have to convert them into the appropriate currency before calculating the difference. Please make sure to join RBKP and RBDRSEG using both the document number in BELNR/RBLNR and the year in GJAHR/RJAHR (field in RBDRSEG is RJAHR, not GJAHR, although GJAHR also exists in RBDRSEG).

when your report works as expected, create the infoset based on your report. You can then use the infoset like any other infoset.

Update: I just realized that because you wrote about being new to ABAP I immediately assumed you need to create a report for your infoset. Depending on your actual requirements this may not be the case. You could create a simple infoset query over table RBKP and then use the infoset editor to add two more fields for the line total and the difference, then add some abap code that selects the sum of all corresponding lines in RBDRSEG and calculates the difference between RMWWR and that aggregated sum. This would probably be slower than a customized abap report as the select would have to be repeated for each line in RBKP, so it really depends on the amount of data your users are going to query. A customized ABAP report is fine, flexible and quick but may be overkill and the number of people able to change a report is smaller than the number of people able to modify an infoset.

---

Additional Info on the variant using the infoset designer

first create a simple infoset reading only table RBKP (so no table join in the infoset definition). Now go to application-specific enhancements:

In my example I already added 2 fields, LINETOTAL and DIFFERENCE. Both have the same properties as RBDRSEG-DMBTR. Make sure your field containing the sum of RBDRSEG-DMBTR has a lower sequence (here '1') than the field containing the difference. The sequence determines which fields will be calculated first.

Click on the coding button for the first field and add the coding to select the sum for a single RBKP entry:

Then do the same for the difference field:

Now you have both fields available in your field list, you can add them to your field group on the right:

As mentioned before, the code you just entered will be processed for each line in RBKP. So this might have a huge impact on runtime performance, depending on the size of your initial result set.