Create CDS View with Parameters for ABAP using SAP HANA Studio
This SAP HANA tutorial shows how to create CDS View with parameters using SAP HANA Studio and create ABAP program which query parametric CDS view and display CDS view data on a table.
SAP CDS views enable ABAP developers to perform database queries with high performance and build powerful SQL query using native SQL functions and use database Join types instead of ABAP OpenSQL statements used with ABAP Loop statements. Using parameters in CDS view definitions enable ABAP database programmers to filter data at SAP HANA database layer and minimize data transferred to application layer resulting high performance improvements.
In this tutorial, first part will show how to create parameterized CDS View using SAP HANA Studio.
In the second part of the tutorial, ABAP programmer will create a standart report which will display data from CDS view with parameters passed as filter criteria and list on a table GUI control.
Create Parameterized CDS View using SAP HANA Studio
This SAP HANA tutorial section shows how to create parameterized CDS View using SAP HANA Studio. If you know how to create CDS view, you will realize it is not very different to create a CDS View with parameters
Launch SAP HANA Studio as our development IDE in order to create CDS View with parameters.
Create a new project and connect to your target SAP system, or if you have already created a project connected to the SAP system open it.
Please note for this tutorial, I have created the parameterized CDS view as a local object so I am using the $TMP package.
In Project Explorer window drill-down the target project:
SAP Package > Dictionary > ABAP DDL Sources
Right click on ABAP DDL Sources and select "New DDL Source" to create a new SAP CDS View with Parameters
If you are new with CDS views, please check my previous tutorial How to Create CDS View in SAP HANA Studio. In that tutorial, follow the steps until you select the CDS view template. Because we will use the template Define View with Parameters for this tutorial.
As you will see from the text the template defines a view with a single input parameter.
The input parameter to the CDS View can be used as an element in the select list or as an operand in conditional or arithmetic expressions.
In general, the input parameter argument is used in WHERE clause for the SELECT query forming the SQL View
@AbapCatalog.sqlViewName: '${sql_view_name}'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: '${ddl_source_description}'
define view ${ddl_source_name_editable}
with parameters ${parameter_name} : ${parameter_type}
as select from ${data_source_name} {
${cursor}
}
Let's now modify the above parameterized CDS View template so that we can pass more than one parameter and build a SQL SELECT query joining database tables
Following CDS view is for returning items of a billing document with filter criteria of sales organization and billing document number from the VBRK header table. Besides a language parameter is passed as in input parameter to the CDS view to return the material description in login language of the SAP user.
For this task, I will use ABAP database tables VBRK, VBRP and MAKT
Here is the SQL query and CDS View definition with parameters.
@AbapCatalog.sqlViewName: 'Z_CDSVIEW_VUK431'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'VUK431 Invoice List Report for R0P'
define view Z_Ddlsqlview_Vuk431
with parameters p_vkorg : abap.char( 4 ),
p_vbeln : vbeln,
p_spras : spras
as
select
from vbrk
left outer join vbrp
on vbrk.vbeln = vbrp.vbeln
association [1..*] to makt
on vbrp.matnr = makt.matnr
{
vbrk.vbeln,
vbrk.fkart,
vbrk.erdat,
vbrk.netwr,
vbrk.kunrg,
vbrk.stceg,
vbrk.vbeln as awkey,
vbrk.knumv,
vbrk.land1,
vbrp.posnr,
vbrp.matnr,
vbrp.fkimg,
vbrp.vrkme,
vbrp.netwr as item_netwr,
vbrp.mwsbp,
vbrp.pstyv,
coalesce(makt[1: spras = :p_spras ].maktx,
makt[1: spras = 'E'].maktx ) as maktx
}
where
vbrk.vbeln = $parameters.p_vbeln
I assume you are familier with SQL table joins for example with LEFT OUTER JOIN or LEFT JOIN as short.
If required to remember LEFT OUTER JOIN, we can say that data from the table in the LEFT side of the join expression will be returned even there is not a corresponding join condition on the RIGHT side table.
Left Outer Join will return all data from the LEFT table with corresponding data from Right table according to the Join condition.
Of course there is an interesting JOIN condition here.
Although it is not listed as a JOIN type between database tables, Association relates MAKT table with VBRP table over the join condition field matnr which is common in both tables.
Although there is "one to many" relation between material and its descriptions (generally there are material text created in different languages for a single item), Association prevents dataset row counts to multiply.
Please check following SQL code block from the above CDS view source code
From "one to many" join set for each material number, only one row is selected.
If there is a matching row from MAKT table with spras column equal to input parameter p_spras, it is used.
Otherwise, MAKT row with English description is used.
coalesce(makt[1: spras = :p_spras ].maktx, makt[1: spras = 'E'].maktx) as maktx
If you check the parameter definition part of the CDS view, each parameter definition is seperated with comma from others.
Additionally, ABAP dictionary types can be used as well as basic SQL data types.
with parameters p_vkorg : abap.char( 4 ),
p_vbeln : vbeln,
p_spras : spras
Within the SQL codes of the CDS view, parameters can be used using two methods.
Either using ":" in front of the parameter name or using the $parameters collection
// : in front of parameter name
spras = :p_spras
// $parameters collection
vbrk.vbeln = $parameters.p_vbeln
Display Data in ABAP Program using Parameterized CDS View
This tutorial section shows how can ABAP programmers use CDS view with parameters to display data in their ABAP reports.
Using a CDS view is not different from querying a database table in ABAP.
But if your SQL query is Select'ing data from a CDS View with parameters, following syntax can be used.
" call cds with parameters
SELECT * FROM z_cdsview_vuk431( p_vkorg = 'TR01',
p_vbeln = '0162000098',
p_spras = @sy-langu )
INTO TABLE @DATA(lt_cdsview_data).
It is important to note for ABAP programmers that not all database platforms support views with parameters.
If your SAP system's database platform does not support parameterized CDS views, you will experience runtime dumps when above ABAP code is executed.
To prevent runtime errors, following feature cl_abap_dbfeatures=>views_with_parameters could be tested before
IF abap_true = cl_abap_dbfeatures=>use_features(
requested_features = VALUE #( ( cl_abap_dbfeatures=>views_with_parameters ) )
).
" call cds with parameters
SELECT * FROM z_cdsview_vuk431( p_vkorg = 'TR01',
p_vbeln = '0162000098',
p_spras = @sy-langu )
INTO TABLE @DATA(lt_cdsview_data).
cl_demo_output=>display_data
(
EXPORTING
name = 'VUK431 Data'
value = lt_cdsview_data
).
ELSE.
" parameterized cds views are not supported on current database
ENDIF.
For demo purposes in this tutorial, I'll use cl_demo_output=>display_data class method to display lt_cdsview_data internal table data populated from parameterized CDS View
