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.
