10-07-2019 6:06 AM
hi,
i want to show sum menge ,
but with this code only show first 1 row of menge, and not show sum of menge,
please help for show all row of menge and show sum of menge.
REPORT ZREPORT_SALES_PERMONTH.
TYPE-POOLS : SLIS.
*----------------------------------------------------------------------*
* TABLES
*----------------------------------------------------------------------*
TABLES : MSEG, MARA, MAKT, LFA1, T005U, T023T, ZPI_MAP_PRIN, EINA, T001W, MKPF.
*----------------------------------------------------------------------*
* INTERNAL TABLES
*----------------------------------------------------------------------*
TYPES : BEGIN OF TY_RESULT,
BUDAT LIKE MKPF-BUDAT, "PERIODEZZ
* REGIO LIKE EINA-REGIO, "REGION
BEZEI LIKE T005U-BEZEI, "DESC REGIONZZ
MATNR LIKE MARA-MATNR, "MATERIALZZ
MAKTX LIKE MAKT-MAKTX, "DESCRIPTIONSZZ
WGBEZ60 LIKE T023T-WGBEZ60, "SUBCATEGORYZZ
WGBEZ LIKE T023T-WGBEZ, "CATEGORYZZ
SUM_MENGE LIKE MSEG-MENGE, "QUANTITYZZ
MEINS LIKE MARA-MEINS, "SATUANZZ
BISMT LIKE MARA-BISMT, "SKUZZ
NAME LIKE ZPI_MAP_PRIN-NAME, "PRINCIPALZZ
NAME1 LIKE LFA1-NAME1, "DISTRIBUTORZZ
BWART LIKE MSEG-BWART, "MOVEMENT TYPEZZ
END OF TY_RESULT.
TYPES : BEGIN OF TY_MARA,
BISMT LIKE MARA-BISMT, "SKU
MATNR LIKE MARA-MATNR, "MATERIAL
MEINS LIKE MARA-MEINS, "MATERIAL DESC
ZZPRIN LIKE MARA-ZZPRIN,
MATKL LIKE MARA-MATKL,
ERNAM LIKE MARA-ERNAM,
END OF TY_MARA.
TYPES : BEGIN OF TY_MSMK,
BUDAT LIKE MKPF-BUDAT, "PERIOD
MENGE LIKE MSEG-MENGE, "SUM QUANTITY
BWART LIKE MSEG-BWART, "MOVEMENT TYPE
WERKS LIKE MSEG-WERKS,
MATNR LIKE MSEG-MATNR,
MJAHR LIKE MKPF-MJAHR,
MBLNR LIKE MKPF-MBLNR,
END OF TY_MSMK.
TYPES : BEGIN OF TY_MAMA,
BISMT LIKE MARA-BISMT, "SKU
MATNR LIKE MARA-MATNR, "MATERIAL
MEINS LIKE MARA-MEINS, "MATERIAL DESC
ZZPRIN LIKE MARA-ZZPRIN,
MATKL LIKE MARA-MATKL,
ERNAM LIKE MARA-ERNAM,
MAKTX LIKE MAKT-MAKTX, "DESCRIPTIONS
END OF TY_MAMA.
TYPES : BEGIN OF TY_MSEG,
MENGE LIKE MSEG-MENGE, "SUM QUANTITY
BWART LIKE MSEG-BWART, "MOVEMENT TYPE
MATNR LIKE MSEG-MATNR,
WERKS LIKE MSEG-WERKS,
END OF TY_MSEG.
TYPES : BEGIN OF TY_MKPF,
BUDAT LIKE MKPF-BUDAT, "PERIOD
MJAHR LIKE MKPF-MJAHR,
MBLNR LIKE MKPF-MBLNR,
END OF TY_MKPF.
TYPES : BEGIN OF TY_MAKT,
MAKTX LIKE MAKT-MAKTX, "DESCRIPTIONS
END OF TY_MAKT.
TYPES : BEGIN OF TY_LFA1,
NAME1 LIKE LFA1-NAME1, "DISTRIBUTOR
LAND1 LIKE LFA1-LAND1,
LIFNR LIKE LFA1-LIFNR,
REGIO LIKE LFA1-REGIO,
END OF TY_LFA1.
TYPES : BEGIN OF TY_T023T,
WGBEZ LIKE T023T-WGBEZ, "CATEGORY
WGBEZ60 LIKE T023T-WGBEZ60, "SUBCATEGORY
MATKL LIKE T023T-MATKL,
END OF TY_T023T.
TYPES : BEGIN OF TY_ZPI_MAP_PRIN,
NAME LIKE ZPI_MAP_PRIN-NAME, "PRINCIPAL
ERNAM LIKE ZPI_MAP_PRIN-ERNAM,
ID LIKE ZPI_MAP_PRIN-ID,
END OF TY_ZPI_MAP_PRIN.
TYPES : BEGIN OF TY_T005U,
BLAND LIKE T005U-BLAND, "REGION
BEZEI LIKE T005U-BEZEI, "DESC REGION
LAND1 LIKE T005U-LAND1,
END OF TY_T005U.
TYPES : BEGIN OF TY_EINA,
REGIO LIKE EINA-REGIO,
LIFNR LIKE EINA-LIFNR,
MATNR LIKE EINA-MATNR,
END OF TY_EINA.
TYPES : BEGIN OF TY_T001W,
REGIO LIKE T001W-REGIO,
WERKS LIKE T001W-WERKS,
END OF TY_T001W.
*----------------------------------------------------------------------*
* DECLARE DATA
*----------------------------------------------------------------------*
DATA: T_MSEG TYPE TABLE OF TY_MSEG,
GS_MSEG TYPE TY_MSEG,
T_MAKT TYPE TABLE OF TY_MAKT,
GS_MAKT TYPE TY_MAKT,
T_MARA TYPE TABLE OF TY_MARA,
GS_MARA TYPE TY_MARA,
T_LFA1 TYPE TABLE OF TY_LFA1,
GS_LFA1 TYPE TY_LFA1,
T_T005U TYPE TABLE OF TY_T005U,
GS_T005U TYPE TY_T005U,
T_T023T TYPE TABLE OF TY_T023T,
GS_T023T TYPE TY_T023T,
T_ZPI_MAP_PRIN TYPE TABLE OF TY_ZPI_MAP_PRIN,
GS_ZPI_MAP_PRIN TYPE TY_ZPI_MAP_PRIN,
T_RESULT TYPE TABLE OF TY_RESULT,
GS_RESULT TYPE TY_RESULT,
T_FCAT TYPE SLIS_T_FIELDCAT_ALV,
GS_FCAT LIKE LINE OF T_FCAT,
GS_LAYO TYPE SLIS_LAYOUT_ALV,
T_EINA TYPE TABLE OF TY_EINA,
GS_EINA TYPE TY_EINA,
T_T001W TYPE TABLE OF TY_T001W,
GS_T001W TYPE TY_T001W,
T_MSMK TYPE TABLE OF TY_MSMK,
GS_MSMK TYPE TY_MSMK,
T_MAMA TYPE TABLE OF TY_MAMA,
GS_MAMA TYPE TY_MAMA,
SUM TYPE P DECIMALS 2.
INITIALIZATION.
*----------------------------------------------------------------------*
* DECLARE SELECTION-SCREEN
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-B01.
SELECT-OPTIONS: S_BUDAT FOR MKPF-BUDAT, "PERIODE
S_MATNR FOR MARA-MATNR.
SELECTION-SCREEN END OF BLOCK B1.
*----------------------------------------------------------------------*
* START-OF-SELECTION
*----------------------------------------------------------------------*
START-OF-SELECTION.
IF S_BUDAT[] IS INITIAL. "KALO INPUTAN BUDAT TIDAK DIISI AKAN KELUAR PESAN DIBAWAH
MESSAGE 'PLEASE INPUT DATE' TYPE 'I'.
ELSE.
REFRESH : T_MSMK.
CLEAR : GS_MSMK.
SELECT DISTINCT b~BUDAT
a~MENGE
a~BWART
a~WERKS
a~MATNR
b~MJAHR
b~MBLNR
INTO TABLE T_MSMK
FROM MSEG AS a
INNER JOIN MKPF AS b
ON b~MJAHR = a~MJAHR
AND b~MBLNR = a~MBLNR
WHERE MATNR IN S_MATNR
AND BUDAT IN S_BUDAT
AND BWART IN ('Z51','Z52').
SELECT REGIO
WERKS
FROM T001W
INTO TABLE T_T001W
FOR ALL ENTRIES IN T_MSEG
WHERE WERKS = T_MSEG-WERKS.
ENDIF.
IF T_MSMK[] IS NOT INITIAL.
SELECT a~BISMT
a~MATNR
a~MEINS
a~ZZPRIN
a~MATKL
a~ERNAM
b~MAKTX
INTO TABLE T_MAMA
FROM MARA AS a
INNER JOIN MAKT AS b
ON b~MATNR = a~MATNR
WHERE a~MATNR IN S_MATNR.
ENDIF.
IF T_MAMA[] IS NOT INITIAL.
SELECT REGIO
LIFNR
MATNR
FROM EINA
INTO TABLE T_EINA
FOR ALL ENTRIES IN T_MARA
WHERE MATNR = T_MARA-MATNR.
SELECT WGBEZ
WGBEZ60
MATKL
FROM T023T
INTO TABLE T_T023T
FOR ALL ENTRIES IN T_MARA
WHERE MATKL = T_MARA-MATKL.
SELECT NAME
ERNAM
ID
FROM ZPI_MAP_PRIN
INTO TABLE T_ZPI_MAP_PRIN
FOR ALL ENTRIES IN T_MARA
WHERE ID = T_MARA-ZZPRIN.
ENDIF.
SELECT BLAND
BEZEI
LAND1
FROM T005U
INTO TABLE T_T005U
FOR ALL ENTRIES IN T_EINA
WHERE BLAND = T_EINA-REGIO
AND LAND1 = 'ID'.
SELECT NAME1
LAND1
LIFNR
REGIO
FROM LFA1
INTO TABLE T_LFA1
FOR ALL ENTRIES IN T_EINA
WHERE LIFNR = T_EINA-LIFNR.
*----------------------------------------------------------------------*
* END-OF-SELECTION
*----------------------------------------------------------------------*
LOOP AT T_MSMK INTO GS_MSMK.
GS_RESULT-BUDAT = GS_MSMK-BUDAT.
IF GS_MSEG-BWART = 'Z51'.
GS_RESULT-SUM_MENGE = GS_RESULT-SUM_MENGE + GS_MSMK-MENGE.
ELSEIF GS_MSEG-BWART = 'Z52'.
GS_RESULT-SUM_MENGE = GS_RESULT-SUM_MENGE - GS_MSMK-MENGE.
ENDIF.
GS_RESULT-BWART = GS_MSMK-BWART.
IF S_BUDAT = 'X'.
ENDIF.
READ TABLE T_MAMA INTO GS_MAMA WITH KEY MATNR = GS_MSMK-MATNR.
GS_RESULT-BISMT = GS_MAMA-BISMT.
GS_RESULT-MATNR = GS_MAMA-MATNR.
GS_RESULT-MEINS = GS_MAMA-MEINS.
GS_RESULT-MAKTX = GS_MAMA-MAKTX.
IF S_MATNR = 'X'.
ENDIF.
READ TABLE T_ZPI_MAP_PRIN INTO GS_ZPI_MAP_PRIN WITH KEY ID = GS_MAMA-ZZPRIN.
GS_ZPI_MAP_PRIN-ERNAM = GS_MAMA-ERNAM.
IF SY-SUBRC EQ 0.
GS_RESULT-NAME = GS_ZPI_MAP_PRIN-NAME.
ENDIF.
READ TABLE T_T023T INTO GS_T023T WITH KEY MATKL = GS_MAMA-MATKL.
IF SY-SUBRC EQ 0.
GS_RESULT-WGBEZ = GS_T023T-WGBEZ.
GS_RESULT-WGBEZ60 = GS_T023T-WGBEZ60.
ENDIF.
READ TABLE T_EINA INTO GS_EINA WITH KEY MATNR = GS_MAMA-MATNR.
IF SY-SUBRC EQ 0.
GS_RESULT-MATNR = GS_EINA-MATNR.
ENDIF.
READ TABLE T_T005U INTO GS_T005U WITH KEY BLAND = GS_EINA-REGIO
LAND1 = 'ID'.
IF SY-SUBRC EQ 0.
GS_RESULT-BEZEI = GS_T005U-BEZEI.
ENDIF.
READ TABLE T_LFA1 INTO GS_LFA1 WITH KEY LIFNR = GS_EINA-LIFNR.
IF SY-SUBRC EQ 0.
GS_RESULT-NAME1 = GS_LFA1-NAME1.
ENDIF.
APPEND GS_RESULT TO T_RESULT. "LAKUKAN APPEND UNTUK MENGGABUNGKAN GS RESULT KE T RESULT SEBELUM DI BUAT MENJADI OUTPUT DI ALV DI BAWAH
CLEAR GS_RESULT. "DI CLEAR AKAN DATA TIDAK TERTUMPUK DAN AGAR TIDAK LAMA LOAD.
ENDLOOP.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'BUDAT'.
GS_FCAT-COL_POS = 1.
GS_FCAT-SELTEXT_M = 'PERIODE'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'BEZEI'.
GS_FCAT-COL_POS = 2.
GS_FCAT-SELTEXT_M = 'DESC REGION'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'BWART'.
GS_FCAT-COL_POS = 3.
GS_FCAT-SELTEXT_M = 'MOVEMENT TYPE'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'MATNR'.
GS_FCAT-COL_POS = 4.
GS_FCAT-SELTEXT_M = 'MATERIAL'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'MAKTX'.
GS_FCAT-COL_POS = 5.
GS_FCAT-SELTEXT_M = 'DESCRIPTIONS'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'WGBEZ60'.
GS_FCAT-COL_POS = 6.
GS_FCAT-SELTEXT_M = 'SUBCATEGORY'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'WGBEZ'.
GS_FCAT-COL_POS = 7.
GS_FCAT-SELTEXT_M = 'CATEGORY'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'SUM_MENGE'.
GS_FCAT-COL_POS = 8.
GS_FCAT-SELTEXT_M = 'SUM QUANTITY'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'MEINS'.
GS_FCAT-COL_POS = 9.
GS_FCAT-SELTEXT_M = 'SATUAN'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'BISMT'.
GS_FCAT-COL_POS = 10.
GS_FCAT-SELTEXT_M = 'SKU'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'NAME'.
GS_FCAT-COL_POS = 11.
GS_FCAT-SELTEXT_M = 'PRINCIPAL'.
APPEND GS_FCAT TO T_FCAT.
CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'NAME1'.
GS_FCAT-COL_POS = 12.
GS_FCAT-SELTEXT_M = 'DISTRIBUTOR'.
APPEND GS_FCAT TO T_FCAT.
GS_LAYO-ZEBRA = 'X'. "BIKIN TAMPILAN OUTPUTNYA ANTAR ROW BERWARNA SELANG SELING
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = SY-REPID
I_CALLBACK_USER_COMMAND = 'USER_COMMAND'
I_GRID_TITLE = 'REPORT SALES PER MONTH PER REGION' "BUAT JUDUL DI ATAS OUTPUT REPORT
IS_LAYOUT = GS_LAYO
IT_FIELDCAT = T_FCAT
TABLES
T_OUTTAB = T_RESULT. "MASUKIN INTERNAL TABEL PENAMPUNF SEMUA OUTPUT YANG AKAN DITAMPILKAN
IF SY-SUBRC <> 0.
ENDIF.
FORM USER_COMMAND USING RS_COMM TYPE SY-UCOMM
RS_SEL TYPE SLIS_SELFIELD.
CASE RS_COMM.
WHEN '&IC1'.
READ TABLE T_RESULT
INTO GS_RESULT
INDEX RS_SEL-TABINDEX.
ENDCASE.
ENDFORM.
10-07-2019 6:15 AM
LOOP AT T_MSMK INTO GS_MSMK.
GS_RESULT-BUDAT = GS_MSMK-BUDAT.
IF GS_MSEG-BWART = 'Z51'.
GS_RESULT-SUM_MENGE = GS_RESULT-SUM_MENGE + GS_MSMK-MENGE.
ELSEIF GS_MSEG-BWART = 'Z52'.
GS_RESULT-SUM_MENGE = GS_RESULT-SUM_MENGE - GS_MSMK-MENGE.
ENDIF.
GS_RESULT-BWART = GS_MSMK-BWART.
IF S_BUDAT = 'X'.
ENDIF.
I believe you are using above code for menge SUM. You have used this condition for addition, but I don't see GS_MSEG-BWART changing anywhere in the loop.
IF GS_MSEG-BWART = 'Z51'.
Please update the condition inside loop by assigning some value to it, otherwise it will always compare one value only, which I guess is your first row.
10-07-2019 6:15 AM
Hello,
if you want to have the totals you have tell this the SQL Statement:
Instead of
SELECT DISTINCT b~BUDAT
a~MENGE
a~BWART
a~WERKS
a~MATNR
b~MJAHR
b~MBLNR
INTO TABLE T_MSMK
FROM MSEG AS a
INNER JOIN MKPF AS b
ON b~MJAHR = a~MJAHR
AND b~MBLNR = a~MBLNR
WHERE MATNR IN S_MATNR
AND BUDAT IN S_BUDAT
AND BWART IN ('Z51','Z52').
you should do something like this if you want to get the totals by materials document number
SELECT b~MBLNR
b~MJAHR
a~WERKS
a~MATNR
a~BWART
b~BUDAT
Sum( a~MENGE ) as Menge
INTO TABLE T_MSMK
FROM MSEG AS a
INNER JOIN MKPF AS b
ON b~MJAHR = a~MJAHR
AND b~MBLNR = a~MBLNR
WHERE MATNR IN S_MATNR
AND BUDAT IN S_BUDAT
AND BWART IN ('Z51','Z52')
Group by MBLNR MJAHR WERKS MATNR BWART BUDAT.