SQ01/02 Query SELECT SUM functionality

[Former Member]

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

[former_member218424]

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..

[Former Member]

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,

[former_member218424]

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..

[Former Member]

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

[former_member218424]

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...

[Former Member]

Wonderful information.

Thank you so much

[Former Member]

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.