Skip to Content
Author's profile photo Deepak Tewari

Optional Parameters in CDS Views

Hi All,

I am a newbie to SAP-HANA world and was recently exploring different types of CDS Views (particularly CDS Views with Input Parameters).

Though CDS Views with Input Parameters were introduced quite a long time back but I could not find a way to make some of the input parameters as Optional. So I played around with some annotations and found out a way to accomplish the same.

I did that with the help of annotation

@Environment.systemField: #USER

I used the above annotation in my CDS View along with parameters in the following way:

  @AbapCatalog.sqlViewName: 'Z_CDS_OP'
  @AbapCatalog.compiler.compareFilter: true
  @AccessControl.authorizationCheck: #CHECK
  @EndUserText.label: 'Material Query'
  define view ZCDS_OP
    with parameters
      @Environment.systemField: #CLIENT
      p1 : mandt,
      @Environment.systemField: #USER
      p2 : uname,
      p3 : aenam  
    as select from mara as ar
  {
    :p1    as Client,
    :p2    as Name,
    aenam  as Created_By,
    matnr  as Material
  }
  where
        ar.mandt = :p1
    and ar.aenam = :p3; 

Here the parameter p2 is defined for the sy-uname value, but it functions as an optional parameter at run-time.

If no value is passed for p2 parameter, the system defaults it to the sy-uname value.

Note that there is no asterisk (*) over p2 field. Below is the output of this query:

In this case my username is ‘FUNCTIONAL’ and is displayed in column ‘Name’.

 

Now I pass the value to the optional parameter:

Now, the p2 parameter value gets used and I get the following result:

 

This CDS view can also be consumed in an ABAP Program like the following:

    "Select Data from CDS view without optional parameter
    SELECT Client, Name, Created_By, Material
    FROM zcds_op( p3 = 'FUNCTIONAL' )
    INTO TABLE @DATA(li_mara).

and

    "Select Data from CDS view with optional parameter
    SELECT Client, Name, Created_By, Material
    FROM zcds_op( p2 = 'DEEPAK', p3 = 'FUNCTIONAL' )
    INTO TABLE @DATA(li_mara).

Both the code snippets work perfectly fine and generate the desired results.

So in this way I made a parameter optional in a CDS View.

Assigned tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Joachim Rees
      Joachim Rees

      This made me read about the help (F1 in AdT) about @Environment.systemField: #USER  an thus learn a little bit more.

      Also, interesting to see that parameters can be used in the fields-list as well - I initially thought of them as only being useful for the where-clause.

      Thanks!
      Joachim

       

      Author's profile photo santosh MAHALAHA
      santosh MAHALAHA

      Nice Article Deepak, Any idea how one can get last fiscal year based on user Input from selection screen.

      Author's profile photo Deepak Tewari
      Deepak Tewari
      Blog Post Author

      Thanks Santosh.

      Can you please elaborate your question ?

       

      Regards,

      Deepak

      Author's profile photo Rajesh Majeti
      Rajesh Majeti

      Hi Santosh,

       

      please check  dats_add_months.

      Author's profile photo madhu vari
      madhu vari

      from your blog it is known that only system fields can make optional , what about if the fields are not system fields like Material(MATNR), sales document (VBELN).

      Author's profile photo Timothy Hughes
      Timothy Hughes

      Tough luck... and the others are not optional either, they just have default values...which can also be done with @consumption.derivation (a few options).

      Interestingly enough, do NOT try to perform an ASSOCIATION to a CDS view with Parameters (defaulted or not) -- doesn't work.

      Reuse, who needs it....

      Author's profile photo Sebastian Freilinger-Huber
      Sebastian Freilinger-Huber

      Hi Timothy,

      you are correct about input parameters in associations.

      Just in case you really need this feature in the context of an OData Service based on your top level CDS View:

      We managed to get it working by adjusting IO_QUERY_OPTIONS in the IF_SADL_GW_QUERY_CONTROL~SET_QUERY_OPTIONS method for the concrete Entity Set (depending on IV_ENTITY_SET).

      To be honest, this is probably not really a SAP Best Practice (and it is depending on hard coded input parameter names, which you have to place into your ABAP Code), but it is at least a possibility to deal with it.

      Best regards,

      Sebastian

       

       

      Author's profile photo Arvind Yadav
      Arvind Yadav

      Hi Deepak.

      Can we make parameter optional for table field? In the above example you have used the system field to keep it optional.

       

      Thanks and Regards

      Arvind

      Author's profile photo Yogen Patil
      Yogen Patil

      Check this - https://blogs.sap.com/2021/06/03/abap-cds-views-working-with-optional-parameters/

       

      Author's profile photo Chang Ben
      Chang Ben

      Thanks for sharing this Deepak!

      Author's profile photo bala krishna
      bala krishna

      hi deepak,

      when i tried to use same above mentioned code  ADT in Eclipse i am getting  ‘Unexpected word @’ error at  @Environment.systemField: #CLIENT code line  and also at @Environment.systemField: #USER

      Author's profile photo Rahul Kumar
      Rahul Kumar

      Hi Deepak

       

      This mandatory (*) indication works only in ADT. Run the same thing in any reporting tool or RSRT, it does not work. Please check

      Author's profile photo Thiyagarajan Balasubramani
      Thiyagarajan Balasubramani

      Hi Deepak Tewari ,

      I have field which is not system field in standard CDS input parameter.

      I need to make this field from mandatory to optional.

      Can you please tell me how to do this?

      Thanks

      Thiyagarajan

      Author's profile photo Yogen Patil
      Yogen Patil

      Hi,

       

      Check this - https://blogs.sap.com/2021/06/03/abap-cds-views-working-with-optional-parameters/

      Author's profile photo Fulvio Brambilla
      Fulvio Brambilla

      Hi,

      i just found a way to define a standard value for a parameter and bypass it if the parameter is set with the standard value...because you can use the REPLACE function in where clause.

      Have a look and hope it helps!