report

[Former Member]

Hi

experts .i want to know about read table and group by ,order by statement and its use.please help me with examples.

[Former Member]

Hi

Group By and Order By is not used now-a-days instead we SORT the itab by that field and use the control break statements lik AT NEW, AT END OF .

see the sample report and understand the read

REPORT zm_shelf_life_list

NO STANDARD PAGE HEADING

MESSAGE-ID zm_msg

LINE-SIZE 124

LINE-COUNT 60(1).

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

• Purpose : This report displays the list of Materials Shelf Life

• based on the Values entered on Selection Screen.

*

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

• T A B L E S D E C L A R A T I O N S

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

TABLES: mara, " Material Master

t001w, " Plants

t001l. " Storage Locations

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

• T Y P E S D E C L A R A T I O N S

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

• Material Documents Main Structure

TYPES: BEGIN OF s_mat,

mblnr TYPE mblnr, " GI Doc No

mjahr TYPE mjahr, " Fiscal Year

zeile TYPE mblpo, " Item No

bwart TYPE bwart, " Movement Type

matnr TYPE matnr, " Material No

werks TYPE werks_d, " Plant

lgort TYPE lgort_d, " St.Location

menge TYPE menge_d, " Quantity

meins TYPE meins, " UOM

hsdat TYPE hsdat, " Production date

vfdat TYPE vfdat, " Shelf Exp.Date

END OF s_mat.

• Material data Structure

TYPES: BEGIN OF s_mat1,

matnr TYPE matnr, " Material No

bwart TYPE bwart, " Movement Type

menge TYPE menge_d, " Quantity

meins TYPE meins, " UOM

werks TYPE werks_d, " Plant

lgort TYPE lgort_d, " St.Location

hsdat TYPE hsdat, " Production date

vfdat TYPE vfdat, " Shelf Exp.Date

END OF s_mat1.

• Material Text Details

TYPES: BEGIN OF s_mt,

matnr TYPE matnr, " Material No

maktx TYPE maktx, " Material Text

END OF s_mt.

• Material Qty Structure

TYPES: BEGIN OF s_rep1,

matnr TYPE matnr, " Material No

menge TYPE menge_d, " Quantity 105

menge1 TYPE menge_d, " Quantity 201

END OF s_rep1.

• Output Main Structure

TYPES: BEGIN OF s_rep,

matnr TYPE matnr, " Material No

bwart TYPE bwart, " Movement Type

menge TYPE menge_d, " Quantity

maktx TYPE maktx, " Plant

werks TYPE werks_d, " Plant

lgort TYPE lgort_d, " St.Location

meins TYPE meins, " UOM

hsdat TYPE hsdat, " Production date

vfdat TYPE vfdat, " Shelf Exp.Date

rsl TYPE i, " RSL

END OF s_rep.

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

• D A T A D E C L A R A T I O N S

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

DATA: gv_title1 TYPE sylisel, " Report title

gv_dial, " Color flag

gv_year(4) TYPE n, " Fiscal Year

gv_date TYPE sy-datum, " Date

gv_days TYPE i, " Days

gv_menge TYPE menge_d. " Quantity

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

• C O N S T A N T S D E C L A R A T I O N S

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

CONSTANTS: c_x TYPE c VALUE 'X', " Flag

c_105 TYPE bwart VALUE '105', " Movement Type

c_201 TYPE bwart VALUE '201', " Movement Type

c_261 TYPE bwart VALUE '261', " Movement Type

c_202 TYPE bwart VALUE '202', " Movement Type

c_262 TYPE bwart VALUE '262', " Movement Type

c_641 TYPE bwart VALUE '641', " Movement Type

c_643 TYPE bwart VALUE '643', " Movement Type

c_642 TYPE bwart VALUE '642', " Movement Type

c_106 TYPE bwart VALUE '106', " Movement Type

c_644 TYPE bwart VALUE '644', " Movement Type

c_645 TYPE bwart VALUE '645', " Movement Type

c_646 TYPE bwart VALUE '646', " Movement Type

c_661 TYPE bwart VALUE '661', " Movement Type

c_662 TYPE bwart VALUE '662', " Movement Type

c_541 TYPE bwart VALUE '541', " Movement Type

c_542 TYPE bwart VALUE '542'. " Movement Type

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

• I N T E R N A L T A B L E S D E C L A R A T I O N S

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

DATA: i_mat TYPE STANDARD TABLE OF s_mat WITH HEADER LINE,

i_mat1 TYPE STANDARD TABLE OF s_mat1 WITH HEADER LINE,

i_mt TYPE STANDARD TABLE OF s_mt WITH HEADER LINE,

i_rep TYPE STANDARD TABLE OF s_rep WITH HEADER LINE,

i_rep1 TYPE STANDARD TABLE OF s_rep1 WITH HEADER LINE.

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

• S E L E C T I O N S C R E E N

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

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_matnr FOR mara-matnr, " Material No

s_werks FOR t001w-werks, " Plant

s_lgort FOR t001l-lgort. " Storage Location

SELECTION-SCREEN END OF BLOCK b1.

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

• I N I T I A L I Z A T I O N *

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

INITIALIZATION.

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

• A T S E L E C T I O N - S C R E E N *

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

AT SELECTION-SCREEN.

• Validate the screen fields

PERFORM validate_screen.

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

• S T A R T - O F - S E L E C T I O N *

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

START-OF-SELECTION.

• Fetch main data from MSEG Table

PERFORM fetch_data.

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

• T O P - O F - P A G E *

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

TOP-OF-PAGE.

• Header of the List

PERFORM header.

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

• E N D - O F - P A G E *

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

• Footer

END-OF-PAGE.

ULINE.

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

• E N D - O F - S E L E C T I O N *

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

END-OF-SELECTION.

• Display the Report Output data

PERFORM display_data.

&----

---

*& Form validate_screen

&----

---

• Validation of Selection Screen fields

----

---

FORM validate_screen .

• Validation of Material Number

CLEAR mara-matnr.

IF NOT s_matnr[] IS INITIAL.

SELECT matnr UP TO 1 ROWS

INTO mara-matnr

FROM mara

WHERE matnr IN s_matnr.

ENDSELECT.

IF sy-subrc <> 0.

MESSAGE e000 WITH 'Invalid Material Number'(002).

ENDIF.

ENDIF.

• Validation of Plant

CLEAR t001w-werks.

IF NOT s_werks[] IS INITIAL.

SELECT werks UP TO 1 ROWS

INTO t001w-werks

FROM t001w

WHERE werks IN s_werks.

ENDSELECT.

IF sy-subrc <> 0.

MESSAGE e000 WITH 'Invalid Plant'(003).

ENDIF.

ENDIF.

• Validation of Storage Location

CLEAR t001l-lgort.

IF NOT s_lgort[] IS INITIAL.

SELECT lgort UP TO 1 ROWS

INTO t001l-lgort

FROM t001l

WHERE lgort IN s_lgort.

ENDSELECT.

IF sy-subrc <> 0.

MESSAGE e000 WITH 'Invalid Storage Location'(004).

ENDIF.

ENDIF.

ENDFORM. " validate_screen

&----

---

*& Form fetch_data

&----

---

• Fetching the data from Database Table MSEG

----

---

FORM fetch_data .

CLEAR : gv_year, gv_date, gv_days.

gv_date = sy-datum.

gv_year = gv_date+0(4).

CLEAR i_mat.

REFRESH i_mat.

SELECT a~mblnr " GI Doc No

a~mjahr " Fiscal Year

b~zeile " Item No

b~bwart " Movement Type

b~matnr " Material No

b~werks " Plant

b~lgort " St.Location

b~menge " Quantity

b~meins " UoM

b~hsdat " Production date

b~vfdat " Shelf Exp.Date

INTO TABLE i_mat

FROM mkpf AS a JOIN mseg AS b

ON a_{mblnr = b}mblnr JOIN mara AS c

ON b_{matnr = c}matnr

WHERE a~mjahr EQ gv_year AND

b~matnr IN s_matnr AND

b~werks IN s_werks AND

b~lgort IN s_lgort AND

b~bwart IN (c_105,c_201,c_261,c_642,c_644,

c_645,c_646,c_661,c_202,c_262,c_106,c_641,

c_541,c_542, c_662) AND

c~mhdhb <> ' '.

SORT i_mat BY mblnr mjahr zeile matnr.

• Move the data to Materials Int Table

LOOP AT i_mat.

MOVE-CORRESPONDING i_mat TO i_mat1.

APPEND i_mat1.

CLEAR i_mat1.

ENDLOOP.

SORT i_mat1 BY matnr bwart.

IF NOT i_mat[] IS INITIAL.

• Get the Material Text

SELECT matnr " Material No

maktx " Material Description

INTO TABLE i_mt

FROM makt

FOR ALL ENTRIES IN i_mat

WHERE matnr = i_mat-matnr AND

spras = sy-langu.

ENDIF.

SORT i_mt BY matnr.

DELETE ADJACENT DUPLICATES FROM i_rep COMPARING matnr.

• Move the data to i_rep int.table

LOOP AT i_mat1.

AT END OF bwart.

READ TABLE i_mat1 INDEX sy-tabix.

SUM.

i_rep-menge = i_mat1-menge.

i_rep-meins = i_mat1-meins.

i_rep-hsdat = i_mat1-hsdat.

i_rep-vfdat = i_mat1-vfdat.

i_rep-matnr = i_mat1-matnr.

i_rep-werks = i_mat1-werks.

i_rep-lgort = i_mat1-lgort.

i_rep-bwart = i_mat1-bwart.

CLEAR i_mt.

READ TABLE i_mt WITH KEY matnr = i_mat1-matnr.

IF sy-subrc = 0.

i_rep-maktx = i_mt-maktx.

ENDIF.

IF NOT i_mat1-vfdat IS INITIAL.

i_rep-rsl = i_mat1-vfdat - gv_date.

ENDIF.

APPEND i_rep.

CLEAR i_rep.

ENDAT.

ENDLOOP.

SORT i_rep BY matnr.

• Move the Qty data to i_rep1 int.table

LOOP AT i_rep.

i_rep1-matnr = i_rep-matnr.

IF i_rep-bwart = c_105.

i_rep1-menge = i_rep-menge.

ELSEIF i_rep-bwart = c_106.

i_rep1-menge = i_rep1-menge - i_rep-menge.

ELSEIF ( i_rep-bwart = c_643 OR i_rep-bwart = c_201 OR

i_rep-bwart = c_261 OR i_rep-bwart = c_641 OR

i_rep-bwart = c_645 OR i_rep-bwart = c_661 OR

i_rep-bwart = c_541 ).

i_rep1-menge1 = i_rep1-menge1 + i_rep-menge.

ELSEIF ( i_rep-bwart = c_202 OR i_rep-bwart = c_262 OR

i_rep-bwart = c_642 OR i_rep-bwart = c_644 OR

i_rep-bwart = c_646 OR i_rep-bwart = c_662 OR

i_rep-bwart = c_542 ).

i_rep1-menge1 = i_rep1-menge1 - i_rep-menge.

ENDIF.

COLLECT i_rep1.

CLEAR i_rep1.

ENDLOOP.

SORT i_rep1 BY matnr.

ENDFORM. " fetch_data

&----

---

*& Form display_data

&----

---

• display the Output Report

----

---

FORM display_data .

DATA: lv_flag. " New Flag

IF i_rep1[] IS INITIAL.

MESSAGE i000 WITH 'No data found'(014).

ELSE.

LOOP AT i_rep1.

• Toggle Color

PERFORM toggle_color.

IF lv_flag <> space.

NEW-LINE.

ENDIF.

CLEAR gv_menge.

gv_menge = i_rep1-menge - i_rep1-menge1.

WRITE:/1 sy-vline,2(18) i_rep1-matnr.

lv_flag = c_x.

CLEAR i_rep.

READ TABLE i_rep WITH KEY matnr = i_rep1-matnr

bwart = c_105.

WRITE: 20 sy-vline,21(40) i_rep-maktx,

61 sy-vline,62(5) i_rep-werks,

67 sy-vline,68(7) i_rep-lgort,

75 sy-vline,76(10) i_rep-hsdat,

86 sy-vline,87(14) i_rep-vfdat,

101 sy-vline,102(4) i_rep-rsl,

106 sy-vline,107(13) gv_menge UNIT i_rep-meins,

120 sy-vline,121(3) i_rep-meins,

124 sy-vline.

ENDLOOP.

ULINE.

ENDIF.

ENDFORM. " display_data

&----

---

*& Form header

&----

---

• Write the Report Header

----

---

FORM header .

FORMAT RESET.

gv_title1 = sy-title. " Set List Header

• Standard header

CALL FUNCTION 'Z_STANDARD_HEADER'

EXPORTING

title1 = gv_title1.

FORMAT COLOR COL_HEADING.

ULINE.

WRITE:/1 sy-vline,2(18) 'Material No'(005) CENTERED,

20 sy-vline,21(40) 'Material Description'(006) CENTERED,

61 sy-vline,62(5) 'Plant'(007),

67 sy-vline,68(7) 'St.Locn'(008),

75 sy-vline,76(10) 'Prodn.Date'(009),

86 sy-vline,87(14) 'Shelf Exp.Date'(010),

101 sy-vline,102(4) 'RSL'(011),

106 sy-vline,107(13) 'Quantity'(012) CENTERED,

120 sy-vline,121(3) 'UoM'(013),

124 sy-vline.

ULINE.

ENDFORM. " header

&----

---

*& Form toggle_color

&----

---

• This routine alters the color of the records in the list

----

---

FORM toggle_color.

IF gv_dial = space.

FORMAT COLOR COL_NORMAL INTENSIFIED OFF.

gv_dial = c_x.

ELSE.

FORMAT COLOR 1 INTENSIFIED OFF.

CLEAR gv_dial.

ENDIF.

ENDFORM. " toggle_color

<b>Reward points for useful Answers</b>

Regards

Anji

[Former Member]

thanx.

[Former Member]

<b>REAB TABLE</b> reads only 1 record at a time

these are the results for SY-SUBRC checks after read table

SY-SUBRC = 0:

An entry was read.

SY-TABIX is set to the index of the entry.

SY-SUBRC = 2:

An entry was read.

SY-TABIX is set to the index of the entry. This return code can only occur when you use the COMPARING addition. For further detauls, refer to the COMPARING section of the additions

SY-SUBRC = 4:

No entry was read.

The value of SY-TABIX depends on the table type and whether the BINARY SEARCH addition was specified.

If the table is a SORTED TABLE or a table sorted in ascending order of the type STANDARD TABLE with the BINARY SEARCH addition, SY-TABIX refers to the next-highest index.

Otherwise, SY-TABIX is undefined.

SY-SUBRC = 8:

No entry was read.

This return code only occurs with a SORTED TABLE or a STANDARD TABLE with the BINARY SEARCH addition. SY-TABIX is set to the number of all entries plus 1.

EX: READ TABLE it_tab WITH TABLE KEY matnr = 'mat001'.

<b>Reward Points If Useful...</b>