ABAP for SAP HANA – Part 24. Custom Report Using AMDP

The other day, I had a requirement to create a new program in S/4HANA System to present some report. Being new to S/4HANA development team, I had a good learning experience. It might look pretty simple for someone who is already working in S/4HANA, but for others it would be useful to know the solution we implement in S/4HANA Clients. In this article I will try to share my experience, what I had to consider and what difficulty I faced.
AMDP was used in our report. There was no standard CDS for our need and we did not want to create a custom CDS for the same.

How to Handle Selection Screen Inputs in AMDP?

Since it is a report, we definitely need a Selection Screen. But how do we pass Selection Screen Parameters and Select Options to AMDP method?

We need to build Dynamic Where Condition based on selection screen field.

<code>  TRY.
     
  DATA(lv_afru) = cl_shdb_seltab=>combine_seltabs(
                      EXPORTING it_named_seltabs =
                       VALUE #(
                               ( name = 'WERKS' dref = REF #( s_werks[] ) )
                               ( name = 'BUDAT' dref = REF #( s_budat[] ) ) )
                                               	iv_client_field = 'MANDT' ).
 
  DATA(lv_bwart) = cl_shdb_seltab=>combine_seltabs(
                       EXPORTING it_named_seltabs =
                        VALUE #( ( name = 'BWART' dref = REF #( s_bwart[] ) ) )
                                                         iv_client_field = 'MANDT' ).
 
    CATCH cx_shdb_exception INTO gt_shdb_exception. " Exceptions of HANA DB objects
      gv_message = gt_shdb_exception->get_text( ).
      MESSAGE gv_message TYPE 'I'.
  ENDTRY.</code>

If you look closely, we are converting the selection screen fields into strings of where clause so that we can pass it into AMDP Method.

How to Consume the Selection Screen Dynamic Where Clause String in AMDP Method?

We need to apply filter. APPLY_FILTER is the keyword.

<code>    lt_mara   = APPLY_FILTER ( mara, :iv_matnr );
    lt_aufk   = APPLY_FILTER ( aufk, :iv_aufk );
    lt_afru   = APPLY_FILTER ( afru, :iv_afru );
    lt_crhd   = APPLY_FILTER ( crhd, :iv_arbpl );
    lt_matdoc = APPLY_FILTER ( matdoc, :iv_bwart );</code>

How to apply the filter and perform JOIN in AMDP Method?

This is straight forward. We need to have little SQLScript knowledge now.

<code>lt_mara = APPLY_FILTER ( mara, :iv_matnr );
    lt_aufk = APPLY_FILTER ( aufk, :iv_aufk );
    lt_afru = APPLY_FILTER ( afru, :iv_afru );
    lt_crhd = APPLY_FILTER ( crhd, :iv_arbpl );
    lt_matdoc = APPLY_FILTER ( matdoc, :iv_bwart );
 
    et_scrap =  SELECT a.mandt,
                       a.budat,
                       a.arbid,
                       j.veran,
                       a.werks,
                       a.aufnr,
                       a.gmnga,
                       a.xmnga,
                       a.grund,
                       g.grtxt,
                       a.kaptprog,
                       b.plnbez,
                       e.maktx,
                       m.STPRS,
                       c.prctr,
                       c.zz_cust_numb,
                       i.name1,
                       d.bwart,
                       f.mtart,
                       h.MTBEZ,
                       j.arbpl,
                       ( gmnga * stprs ) as p_val,
                       ( xmnga * stprs ) as s_val,
                       case
                       when gmnga = 0 then 0  "gmnga is NULL -> leads to dumb	
                       else ( xmnga / gmnga ) * 100
                       end as scp_p,
                       case
                       when GMNGA = 0 then 0
                       else ( xmnga / gmnga ) * 1000000
                       end as dppms
                      from :lt_afru a
                      inner join afko b on  a.mandt = b.mandt
                                        and a.aufnr = b.aufnr
                      inner join :lt_aufk c on  a.mandt = c.mandt
                                            and a.aufnr = c.aufnr
                      inner join :lt_matdoc d on  a.mandt  = d.mandt
                                              and b.plnbez = d.matnr
                                              and a.aufnr  = d.bktxt
                      INNER JOIN :lt_mara f ON  a.mandt  = f.mandt
                                            and b.plnbez = f.matnr
                      left outer join makt e on  e.mandt = a.mandt
                                        and e.matnr = b.plnbez
                                        and e.spras = :iv_langu
                      left outer join t157e g on  g.mandt = a.mandt
                                         and g.bwart = d.bwart
                                         and g.grund = a.grund
                                         and g.spras = :iv_langu
                      left outer join T134T h on h.mandt = a.mandt
                                             and h.mtart = f.mtart
                                             and h.spras = :iv_langu
                      LEFT outer join kna1 i on i.mandt = a.mandt
                                            and i.kunnr = c.zz_cust_numb
                      inner join :lt_crhd j on  j.mandt = a.mandt
                                            and j.objty = 'A'
                                            and j.objid = a.arbid
                      inner join mbew m on  m.mandt = a.mandt
                                        and m.matnr = b.plnbez
                                        and m.bwkey = a.werks
                      where a.mandt = :iv_client
                      ORDER BY b.plnbez,a.werks;</code>

How can we LOOP in AMDP method?

Initially, I thought I would need to LOOP the data retrieved from JOIN and massage it to get my final output. But later I figured out that we should be making use of the in-memory power of S/4HANA and do the calculation on the fly during SELECT. In this report, I avoided LOOPing in AMDP.

I added the last 4 fields to keep the calculated values during the SELECT and JOIN.

We can use the above fields to do the calculation and get the final calculated data.

The above tweak saved me from doing LOOP in AMDP Method. Not that we cannot do LOOP in AMDP. We can LOOP in AMDP using FOR. But, I was glad, I did not need to worry about FOR Loop for my first AMDP report. In my next article, I will show more tricks we can perform in AMDP methods. The interesting part is, the LOOPs are not like OPEN SQL. We need to use SQLScript code.

Above was the pseudo logic and homework I had to do to deliver my first AMDP Report. I hope someone who is venturing into ABAP on HANA would find it useful.

Requirement:

Create a custom program to generate a report (industry specific) in S/4HANA.

Development Steps:

Step 1. Create a Class with one AMDP Method and a Normal Method in HANA Studio or Eclipse or ADT.

We can view the class and methods in SE24 in ABAP workbench (GUI) but we cannot edit them in GUI. If you do not know how to mark the method as AMDP, check this article.

  • Define the Custom DATA TYPE you need

Please note, I have added 4 fields at the end to store the calculated values (screenshot above).

  • Define the Method in Class Definition
<code>CLASS zcl_stkoes_amdp DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .
  PUBLIC SECTION.
    INTERFACES:
      if_amdp_marker_hdb.
 
    TYPES : BEGIN OF gtys_scrap,
              mandt    TYPE mandt,
              budat    TYPE budat,
              arbid    TYPE objektid,
              veran    TYPE ap_veran,
              werks    TYPE werks_d,
              aufnr    TYPE aufnr,
              gmnga    TYPE ru_gmnga,
              xmnga    TYPE ru_xmnga,
              grund    TYPE co_agrnd,
              grtxt    TYPE grtxt,
              kaptprog TYPE kaptprog,
              plnbez   TYPE matnr,
              maktx    TYPE maktx,
              stprs    TYPE stprs,
              prctr    TYPE prctr,
*              zz_cust_numb TYPE kunnr,
*              name1        TYPE name1_gp,
              bwart    TYPE bwart,
              mtart    TYPE mtart,
              mtbez    TYPE mtbez,
              arbpl    TYPE arbpl,
              p_val    TYPE prcd_elements-kwert,   "Produced Value
              s_val    TYPE prcd_elements-kwert,   "scrap value
              scp_p    TYPE bseg-dmbtr,          "scrap %
              dppms    TYPE prcd_elements-kwert,   "dppmS
            END OF gtys_scrap,
            gtyt_scrap TYPE STANDARD TABLE OF gtys_scrap.
 
    METHODS:
 
      fetch_prod_scrap          " Fetching all data  (AMDP Method)
        IMPORTING
          VALUE(iv_client) TYPE mandt
          VALUE(iv_langu)  TYPE string
          VALUE(iv_matnr)  TYPE string
          VALUE(iv_aufk)   TYPE string
          VALUE(iv_afru)   TYPE string
          VALUE(iv_arbpl)  TYPE string
          VALUE(iv_bwart)  TYPE string
        EXPORTING
          VALUE(et_scrap)  TYPE gtyt_scrap,
 
      display                           "Display
        CHANGING
          it_scrap TYPE gtyt_scrap.
 
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.</code>

The above code needs to be in the CLASS Definition.

  • Write the logic of methods in Class Implementation
<code>CLASS zcl_stkoes_amdp IMPLEMENTATION.
  METHOD fetch_prod_scrap BY DATABASE PROCEDURE
                          FOR HDB
                          LANGUAGE SQLSCRIPT
                          OPTIONS READ-ONLY
                          USING  mara afru afko makt matdoc t157e t134t mbew aufk crhd.
*                    kna1.
 
    lt_mara = APPLY_FILTER ( mara, :iv_matnr );
    lt_aufk = APPLY_FILTER ( aufk, :iv_aufk );
    lt_afru = APPLY_FILTER ( afru, :iv_afru );
    lt_crhd = APPLY_FILTER ( crhd, :iv_arbpl );
    lt_matdoc = APPLY_FILTER ( matdoc, :iv_bwart );
 
    et_scrap =  SELECT  a.mandt, a.budat, a.arbid,
                        j.veran,
                        a.werks, a.aufnr, a.gmnga, a.xmnga, a.grund, g.grtxt,
                        a.kaptprog, b.plnbez, e.maktx, m.STPRS, c.prctr,
*                        c.zz_cust_numb, i.name1,
                        d.bwart, f.mtart, h.MTBEZ,
                        j.arbpl,
 
                        ( gmnga * stprs ) AS p_val,
                        ( xmnga * stprs ) AS s_val,
                        CASE
                          WHEN gmnga = 0 THEN 0
                          ELSE ( xmnga / gmnga ) * 100
                        END AS scp_p,
 
                        CASE
                          WHEN GMNGA = 0 THEN 0
                          ELSE ( xmnga / gmnga ) * 1000000
                        END AS dppms
 
                        FROM :lt_afru a
                        INNER JOIN afko b
                          ON a.mandt = b.mandt AND
                             a.aufnr = b.aufnr
                        INNER JOIN :lt_aufk c
                          ON a.mandt = c.mandt AND
                             a.aufnr = c.aufnr
                        INNER JOIN :lt_matdoc d
                          ON a.mandt  = d.mandt AND
                             b.plnbez = d.matnr AND
                             a.aufnr  = d.bktxt
                        INNER JOIN :lt_mara f
                          ON a.mandt  = f.mandt AND
                             b.plnbez = f.matnr
                        LEFT OUTER JOIN makt e
                          ON e.mandt = a.mandt AND
                             e.matnr = b.plnbez AND
                             e.spras = :iv_langu
                        LEFT OUTER JOIN t157e g
                          ON g.mandt = a.mandt AND
                             g.bwart = d.bwart AND
                             g.grund = a.grund AND
                             g.spras = :iv_langu
 
                        LEFT OUTER JOIN t134t h
                          ON h.mandt = a.mandt AND
                             h.mtart = f.mtart AND
                             h.spras = :iv_langu
*                        LEFT OUTER JOIN kna1 i
*                          ON i.mandt = a.mandt AND
*                             i.kunnr = c.zz_cust_numb
                        INNER JOIN :lt_crhd j
                          ON j.mandt = a.mandt AND
                             j.objty = 'A'     AND
                             j.objid = a.arbid
                        INNER JOIN mbew m
                          ON m.mandt = a.mandt  AND
                             m.matnr = b.plnbez AND
                             m.bwkey = a.werks
                        where a.mandt = :iv_client
                        ORDER BY b.plnbez, a.werks;
 
  ENDMETHOD.
 
  METHOD display.
 
    TRY.
        cl_salv_table=>factory(
                IMPORTING
                  r_salv_table   = DATA(lo_table)  "Basis Class ALV Tables
                CHANGING
                  t_table        = it_scrap ).
      CATCH cx_salv_msg.                                "#EC NO_HANDLER
 
    ENDTRY.
 
*...activate alv generic functions
    lo_table->get_functions( )->set_all( ).
 
*   Set the Column optimization
 
    lo_table->get_columns( )->set_optimize( ).
 
    lo_table->display( ).
 
  ENDMETHOD.
ENDCLASS.</code>

Step 2. Create you program in SE38 either in GUI or in ADT.

If you are in S/4HANA Project, make the habit of doing everything in ADT (ABAP Development Tool).

<code>REPORT zstkoes_amdp.
TABLES: mara, afru, aufk, crhd, matdoc.
**Selection Screen
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-s01.
SELECT-OPTIONS : s_matnr FOR mara-matnr,
*                 s_cust  FOR aufk-zz_cust_numb,
                 s_werks FOR afru-werks OBLIGATORY,
                 s_budat FOR afru-budat OBLIGATORY,
                 s_arbpl FOR crhd-arbpl,
                 s_prctr FOR aufk-prctr,
                 s_bwart FOR matdoc-bwart OBLIGATORY MATCHCODE OBJECT H_T156.
 
SELECTION-SCREEN END OF BLOCK b1.
**START OF SELECTION
* Build Dynamic where condition based on selection screen field.
TRY.
    DATA(gv_afru) = cl_shdb_seltab=>combine_seltabs( it_named_seltabs = VALUE #( ( name = 'WERKS'   dref  = REF #( s_werks[] ) )                                                                                 
        										       ( name = 'BUDAT'    dref    = REF #( s_budat[] ) ) )                                                                                   
      iv_client_field = 'MANDT' ).
 
    DATA(gv_bwart) = cl_shdb_seltab=>combine_seltabs( it_named_seltabs = VALUE #( ( name  = 'BWART'      dref            = REF #( s_bwart[] ) ) )                                                                                   
       iv_client_field = 'MANDT' ).
 
    DATA(gv_matnr) = cl_shdb_seltab=>combine_seltabs( it_named_seltabs = VALUE #( ( name  = 'MATNR'      dref            = REF #( s_matnr[] ) ) )                                                                                   
        iv_client_field = 'MANDT' ).
 
    DATA(gv_aufk)  = cl_shdb_seltab=>combine_seltabs( it_named_seltabs = VALUE #( ( name  = 'PRCTR'        dref            = REF #( s_prctr[] ) ) )                                                                                   
       iv_client_field = 'MANDT' ).
 
    DATA(gv_arbpl) = cl_shdb_seltab=>combine_seltabs( it_named_seltabs = VALUE #( ( name   = 'ARBPL'       dref            = REF #( s_arbpl[] ) ) )                                                                                    
        iv_client_field = 'MANDT' ).
 
  CATCH cx_shdb_exception INTO DATA(gx_shdb_exception). "exceptions of HANA DB object
    DATA(gv_message) = gx_shdb_exception->get_text( ).
    MESSAGE gv_message TYPE 'I'.
ENDTRY.
DATA(lo_amdp) = NEW zcl_stkoes_amdp( ).
lo_amdp->fetch_prod_scrap(            " Fetching all data
    EXPORTING
      iv_client = sy-mandt
      iv_langu  = CONV #( sy-langu )  "Converts directly to string
      iv_matnr  = gv_matnr
      iv_aufk   = gv_aufk
      iv_afru   = gv_afru
      iv_arbpl  = gv_arbpl
      iv_bwart  = gv_bwart
    IMPORTING
      et_scrap  = DATA(gt_scrap) ).
 
IF gt_scrap IS NOT INITIAL.
  lo_amdp->display(        "Processing of data and display
       CHANGING
         it_scrap = gt_scrap ).
ELSE.
  MESSAGE 'No Records Exist.' TYPE 'S' DISPLAY LIKE 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.
</code>

Done. Congratulations!! You just delivered your first real report in S/4HANA using AMDP.