Skip to Content

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
        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).


    "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.

You must be Logged on to comment or reply to a post.
  • 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.



  • 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).

    • 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....

      • 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,




  • 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


  • 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

  • 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

  • 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!