topic Re: SQ01/02 Query SELECT SUM functionality in Application Development and Automation Discussions

SQ01/02 Query SELECT SUM functionality

Former Member — Fri, 12 Jun 2015 08:00:52 GMT

Hi all,

I want to make sure if the following is possible using SQ01 query with additional coding:

The case is like this; for one sales order, there are four deliveries.

I want to report the total delivered quantity, versus the order quantity.

When I join the tables using VBELN = VGBEL, POSNR = VGPOS, I get four rows in the report output, as there are four lines in the LIPS table.

I want to list one single line for the sales order, and in this line I want to list the total quantity of 180.

How can I achieve this?

I believe using the coding options in the SQ02 data source, but I'm going to need clear examples for how.

Thanks in advance

Re: SQ01/02 Query SELECT SUM functionality

former_member218424 — Fri, 12 Jun 2015 08:19:50 GMT

you can add additional field in your infoset and do coding for that field..

in code fetch data from lips table and make some of LFIMG .. then display that column in your query..

Re: SQ01/02 Query SELECT SUM functionality

Former Member — Fri, 12 Jun 2015 08:31:30 GMT

I add simple calculated fields but I did not do the coding part.

Let's say I added a calculated field named TOTAL.

How would I find this field in the internal table?

In which step of coding should I write?

Can you give me a code example for this simple case?

Thanks a lot,

Re: SQ01/02 Query SELECT SUM functionality

former_member218424 — Fri, 12 Jun 2015 08:51:16 GMT

TYPES : BEGIN OF ty_lips,
     vbbel TYPE lips-vgbel,
     vgpos TYPE lips-vgpos,
     lfimg TYPE lips-lfimg,
   END OF ty_lips.

DATA : it_lips TYPE TABLE OF ty_lips,
        wa_lips TYPE ty_lips.

DATA : g_sum TYPE i.

SELECT vgbel vgpos lfimg
   FROM lips
   INTO TABLE it_lips
   WHERE vgbel = vbap-vbeln AND
         vgpos = vbap-posnr.

IF sy-subrc EQ 0.
   LOOP AT it_lips INTO wa_lips.
     g_sum = g_sum + wa_lips-lfimg.
   ENDLOOP.
   sum = g_sum. " Your additional field name.
ENDIF.

you can add additional field in your infoset and add the above code in coding section while record processing..

Re: SQ01/02 Query SELECT SUM functionality

Former Member — Fri, 12 Jun 2015 08:59:51 GMT

Thank you so much. I will apply this.

One last question though: After using this code, will I still get 4 rows in the report?

In other words, the output would still look like this, am I right?

Ord. It. Qt Sum

100015 10 30 180

100015 10 40 180

100015 10 50 180

100015 10 60 180

Re: SQ01/02 Query SELECT SUM functionality

former_member218424 — Fri, 12 Jun 2015 09:08:16 GMT

now do not use lips in your infotype as we are directly fetching data through code..

as far as your question , i understood the lips use was just for summing purpose...

Re: SQ01/02 Query SELECT SUM functionality

Former Member — Fri, 12 Jun 2015 09:11:25 GMT

Wonderful information.

Thank you so much

Re: SQ01/02 Query SELECT SUM functionality

Former Member — Fri, 21 Aug 2015 12:19:06 GMT

Hi again, I just wanted to give an update for anyone following this thread:

The above code worked, adding the SUM variable to the infoset & the fields, and adding it in the query layout.

But the sum data was listed cumulative, like it was collecting the data correctly from LIPS, but the resultant delivery quantity was always added on top of the quantity at each line.

The problem was solved when SUM = g_SUM line was moved inside the loop, and a clear step is added.

It seems like the query report runs the code once for each row listed.

Regards,

***************************************************

TYPES : BEGIN OF ty_lips,

vbbel TYPE lips-vgbel,

vgpos TYPE lips-vgpos,

lfimg TYPE lips-lfimg,

END OF ty_lips.

DATA : it_lips TYPE TABLE OF ty_lips,

wa_lips TYPE ty_lips.

DATA : g_sum TYPE i.

SELECT vgbel vgpos lfimg

FROM lips

INTO TABLE it_lips

WHERE vgbel = vbap-vbeln AND

vgpos = vbap-posnr.

IF sy-subrc EQ 0.

LOOP AT it_lips INTO wa_lips.

g_sum = g_sum + wa_lips-lfimg.

sum = g_sum. " Your additional field name.

ENDLOOP.

CLEAR g_sum.

ENDIF.