Previously, when programming ABAP, we were very familiar with using the syntax of Open SQL to get data and display it to ALV or smart forms. Technology evolved and SAP also evolved and launched new technicals like as CDS view or AMDP as other optimal solutions to solve performance issues of fetching data from the database, and also to take advantage of the power of new technology (HANA database).
So what is AMDP, and how to use AMDP in an ABAP report? We will learn them step by step in this article.
- ADT tool in eclipse or HANA studio
- SAP HANA Db 1.0 or higher
- SAP NW AS ABAP 7.5 or higher
- What is AMDP
- Limitation in AMDP
- First Program with AMDP
What is AMDP
AMDP – ABAP Managed Database Procedures, is a procedure, we can write code inside AMDP by using SQLSCRIPT which is a database language same as SQL script, this language is easy to understand and code. After coding logic inside AMDP method, you can consume it in ABAP report, or use AMDP same as delegate method in CDS table function.
With AMDP, We can take advantage of new features of HANA (code push-down technique), hence we still code all logic on application layer, then this logic will be executed on the database layer.
AMDP is only supported in ADT bundle or HANA studio, and don’t be supported in SAP GUI. So if you want to learn AMDP, please change IDE to ADT bundle instead SAP GUI.
Besides HANA db, AMDP can support many other db, maybe in the future. So, at this time, we can only use it in HANA db.
Limitation in AMDP
We can only create, debug AMDP in ADT bundle or HANA studio.
With AMDP, we can’t use MSEG table. So, we can use MATDOC table or proxy object (NSDM_V_MSEG) instead
Data type of parameter must be table or scalar type(Int, char,…).
With select-option parameters, we must convert to string value by using method cl_shdb_seltab=>combine_seltabs at ABAP program, then pass it to parameters. After that, we will use APPLY_FILTER to filter this condition inside AMDP method
AMDP does not handle automatic client. You need to add client as parameter value or using SESSION_CONTEXT(‘CLIENT’) in where condition
Sometimes, the system will catch syntax error with SD table, in this case, you need to select from table with alias name instead. (I don’t know another system, but in my system, when transport TR to staging environment, it always catches syntax error)
First Program with AMDP
Step 1: Create class using ADT tool
Because AMDP can’t create in SAP GUI, so I will create it (both class and methods) in ADT bundle
Firstly, we need to right-click in your package –>New –>ABAP class to create new class.
Secondly, you must write Name of class and description (two mandatory field), then click next
Create new Transport request and fill description field ( If you have already TR, maybe use it). Then, click next
Click Finish to complete this process and we created class successfully
Step 2: Declare AMDP method in class
Because Structure and Table type will re-use in ABAP Program, so, I will create it in SAP GUI (tcode SE11).
and table type
When using AMDP, you need to add interface IF_AMDP_MARKER_HDB (that is important), if you don’t add it, you can’t use any features of AMDP method.
In the future, if more db will be supported in AMDP, the general syntax will be: IF_AMDP_MARKER_XXX, with suffix XXX is db in this system.
Step 3: Implement AMDP method
We need to add some options at AMDP method as below (in class implementation):
1. By database Procedure to implement a database procedure. In this option, your AMDP method will automatically create a procedure in HANA systerm
2. FOR HDB to indicate HANA database
3. LANGUAGE SQLSCRIPT to indicate the database-specific language in which AMDP is implemented
4. OPTION READ-ONLY indicates we can only read in the database procedure
5. USING <name of table/view>: If you use tables in this procedure, please give name of table in this option
Inside AMDP method, we will fetch data from MARA and MAKT (declare previously at USING statement) with the specific conditions which passed by parameter in class definition before,
NCHAR( 32 ) same as a space in ASCII characters. I want to concat Material Code and Material description and seperate between two value by space, so I will use NCHAR( 32 )
Because AMDP can’t handle client, so we need pass value MANDT at where clause to get exactly value in this system
If you want create a variable inside AMDP method, you can use DECLARE to create. Inside AMDP, you can create both SQL Type and Type in SAP.
With using Built-in function LTRIM, I will remove leading zero before material Code (If have any)
Then, check syntax and active this class/method. So, you can consume it in ABAP Program
Step 4: Develop ABAP report to consume AMDP method
Same as class, I will also create ABAP program in ADT bundle (eclipse or HANA studio)
Right-click program folder and choose new abap program to create new program(In SAP GUI, you can create by using tcode SE38 or SE80).
Same as class, you must fill in your package, name and description. Then, click next
We will also create new Transport Request – TR or use TR which existed in the system. Then click Finish to complete this action.
In this program. We must check the system is satisfied requirement or not, by using class CL_ABAP_DB_FEATURES. If you don’t check, System might catch problems
After checking, you will call your AMDP method and pass your corresponding parameters (check material in your system and get some material code to pass this method).
Using class CL_DEMO_OUTPUT to display the result on the screen.
Run program and you will see the result
Congratulations on completing the first AMDP program. Through this article, you have had some experience with AMDP, how to build it, and use them in the ABAP report. I hope this article will provide base knowledge for everyone to easily learn and have more options to optimize the reports in your projects.
Hope this helps.