Classical Report of Single Table example

In this post we use some basics Events  and Control Break Statements for Generating Classical Report Based On single Table.

If You want some Basic Knowledge About Events and   Control Break Statements then must Visit on This Post Events in ABAP Report and Control Break Statements

Example -

*&---------------------------------------------------------------------*
*& Report  ZWEB
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

* Classical Report of Single Table
REPORT  ZWEB NO STANDARD PAGE HEADING LINE-SIZE 90.

TABLES : MARA .

* Local Structure 
   TYPES : BEGIN OF TY_MARA,
         MATNR TYPE MATNR,
         ERSDA TYPE ERSDA,
         MTART TYPE MTART,
         MATKL TYPE MATKL,
        END OF TY_MARA.

* Define Work Area And Internal Table
 DATA : GS_MARA  TYPE TY_MARA,
        GT_MARA LIKE STANDARD TABLE OF GS_MARA.

* Selection Screen For Material Code AS a Input
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.
  SELECT-OPTIONS : S_MATNR FOR MARA-MATNR OBLIGATORY.
SELECTION-SCREEN END OF BLOCK B1.

*Define Class Definition
CLASS CLASSICAL_RPT DEFINITION.
 
   PUBLIC SECTION.

* Define Two Methods (Get_data and Display_data)  Under Public Section
   METHODS : GET_DATA      IMPORTING I_MATNR TYPE STANDARD TABLE
                           CHANGING I_MARA TYPE STANDARD TABLE,
             DISPLAY_DATA  IMPORTING IT_MARA TYPE STANDARD TABLE.

ENDCLASS.

* Implementation of Above Defined Class And Methods
CLASS CLASSICAL_RPT IMPLEMENTATION.

  METHOD GET_DATA.

    SELECT MATNR
           ERSDA
           MTART
           MATKL FROM MARA INTO TABLE I_MARA WHERE MATNR IN S_MATNR.

  ENDMETHOD.

  METHOD DISPLAY_DATA.

    LOOP AT IT_MARA INTO GS_MARA.

*      at First Control Break Statements
      AT FIRST .
        WRITE: / 'Material No.' COLOR 5,20 'Created on' COLOR 7,40 'Type' COLOR 7,50 'Material Group' COLOR 7.

*   Uline For Under line
        ULINE.
        SKIP.
      ENDAT.

     WRITE : / GS_MARA-MATNR,20 GS_MARA-ERSDA,40 GS_MARA-MTART,50 GS_MARA-MATKL.

*      at last Control Break Statements
     AT LAST.
       ULINE.
       WRITE :25 '----* END-OF-MATERIAL *----' .
     ENDAT.

    ENDLOOP.

  ENDMETHOD.

ENDCLASS.

* Start-of-Selection Events
START-OF-SELECTION.

 DATA : OBJ TYPE REF TO CLASSICAL_RPT.

* Create Object OF CLASSICAL_RPT Class
 CREATE OBJECT OBJ.

* Call Method ANd Export Parameters To The Called Method
 CALL METHOD OBJ->GET_DATA EXPORTING I_MATNR = S_MATNR[]
                           CHANGING  I_MARA = GT_MARA.

 IF GT_MARA IS NOT INITIAL.
   CALL METHOD OBJ->DISPLAY_DATA EXPORTING IT_MARA = GT_MARA.
 ENDIF.

* Top-of-page events
 TOP-OF-PAGE.
  WRITE : SY-ULINE.
  WRITE : 'Material Master Details'.
  WRITE : SY-DATUM.
  WRITE : SY-ULINE.
  SKIP.

Selection-Screen :-

Output:

GROUP BY Clause

The GROUP BY Clause combines groups of rows that have the same Record in their specified columns column1 column2, in the resulting set into a single row.
Group By clause categorizes and summarizes the lines of database table based on a single field or Multiple field, that field is mentioned in the group by clause.
and that  mentioned  fields in the group by clause cannot be specified for the aggregate function. We have to use another fields which are not specified in group by clause to calculate the aggregate function.
GROUP BY cannot be specified for pool tables and cluster tables .
the use of GROUP BY , the statement SELECT avoids the SAP buffering.
so that's why Fetching Data from database Table directly may be take some time.

example : 
DATA : BEGIN OF WA,
        VBELN      TYPE VBAP-VBELN,
        KWMENG TYPE VBAP-KWMENG,
        KBMENG  TYPE VBAP-KBMENG,
       END OF WA.

START-OF-SELECTION.
WRITE:/    'Sales doc. no',
        25 'Max Order Quantity',
        45 'Min Cumulative Confirmed Quantity'.
SELECT VBELN
       MAX( KWMENG ) MIN( KBMENG )
  FROM VBAP
  INTO (WA-VBELN,WA-KWMENG, WA-KBMENG)
  GROUP BY VBELN .

 WRITE:/   WA-VBELN,
         25 WA-KWMENG,
         45 WA-KBMENG.

 ENDSELECT.

Bypassing Buffer in Select Statement

Whenever we use open SQL statements to get fetch data in SAP,it will get data from buffer area(depends on table buffer settings) for better performance, but in real world scenarios some tables may updated very frequently.
we may need to bypass buffer to get real-time data, in that case we will bypass buffer using 'BYPASSING BUFFER' keyword.
Now fetching records directly from database may take time. Hence performance will go down. That’s why SQL fetches data from buffer.

Example :

Output:

Maximum, Minimum, Average, Sum and Count by Select (ABAP SELECT AGGREGATE Statement)

Aggregate Statement return a single value from the values in several lines of a column in  a database table.
The value is calculated  in the database system.
These Statement can be used with SELECT statement by specifying a column(column Field) of a database table as an argument.
Any number of column field can be used as arguments of aggregate Statement in SELECT statement.
Also multiple aggregate Statement can be used in a single SELECT statement.

Following are the aggregate Statement available in sap abap :

1 Average(AVG)
2 MAXIMUM(MAX)
3 Minimum(MIN)
4 Total(SUM)
5 Count(COUNT) 



Average(AVG) : 
Syntax : AVG([DISTINCT]  field1) AVG determines the  average value  of the column field1 specified. It can only be applied to a numeric field.
Data types like  DF16_DEC, D34_DEC (decimal floating point numbers ) , F(float) are recommended for the target fields.


Maximum(MAX) :
Syntax : MAX([DISTINCT] field1)
MAX determines the maximum value of the contents of the column field1 specified.


Minimum(MIN) : 
Syntax : MIN([DISTINCT]  field1)
MIN determines the minimum value of the contents of the column field1 specified.


Total(SUM) :
Syntax : SUM([DISTINCT]  field1)
SUM determines the sum of contents of the column field1 specified.
This can only be applied to a numeric field
To avoid overflows it is recommended to make data type of target field greater than of the source field.


Count(COUNT) :
1. Syntax :  COUNT(DISTINCT field1)
It determines the number of different values in the column field1 .

2. Syntax :  COUNT( * )
It determines the number of rows in the result set. No column field field1 is specified in this case.


example : In the ekpo table we find MENGE field with PO number '3500014180'.

Output

Some interesting points about aggregate Statement :
1. When aggregate Statements are used,the SELECT statement bypasses SAP table buffering. It is a disadvantage if tables are buffered.
2. Columns of the type STRING or RAWSTRING cannot be used with aggregate Statement.
3. Pooled and Cluster tables do not any support aggregate Statement other than COUNT (*).
4. If the addition FOR ALL ENTRIES is used before WHERE , apart from COUNT(*) no other aggregate Statement can be used.

SELECT DISTINCT

SELECT DISTINCT : is a SQL Select query, which is used to get the unique entries of the fields in the select statement.
It will not allow any duplicate entry into the internal table.


In the below example we are having a selection screen where we are defining a selection range of Material number by select option.
At first we are fetching the records with normal select statement and we find Multiple records from the database.

Now with the similar selection range we use select distinct statement and we are getting only one records.
This is because we have selected only the material number in select statement with distinct clause. Now distinct will not allow any duplicate entry of material number.

Notes: Whenever it's possible avoid SELECT DISTINCT, instead select data into internal table, sort and use DELETE ADJACENT DUPLICATES for performance wise.