Defining CDS Views with Input Parameters

Objectives

After completing this lesson, you will be able to:

  • Define CDS views with input parameters
  • Provide values for CDS view parameters
  • Link parameters to ABAP system fields

The Need for Input Parameters

In the previous lessons, you saw how powerful CDS views are when it comes to doing calculations on the database. Maybe you already asked yourself in how far the consumer of the CDS view can influence the way the calculations are done. Of course the consumer can provide a WHERE clause to perform a selection on the result. But how does that help when you want to choose the target currency for a currency conversion?

CDS views with input parameters close this gap. They allow the consumer of a view to provide additional information when retrieving data from this view. The CDS view in turn evaluates this information and takes it into account when calculating the results. Some possible use cases for parameters are as follows:

  • The consumer specifies the target currency of a currency conversion.

  • The consumer specifies whether prices are calculated with or without tax.

  • The consumer provides a separator that is used when concatenating fields.

  • the consumer specifies the language in which translatable text is returned.

Another important motivation for input views are CDS views with aggregations where you want to filter the data that enter the aggregation. If for example you calculate the total revenue and you want to specify a date up to which revenue should be taken into account.

Finally, SQL does not know about mandatory selection criteria. A consumer may provide a WHERE clause or might not. With a mandatory input parameter, the view can force the consumer to provide a necessary selection criterion - for example, a language key, a date or a username.

Input Parameter Declaration

You define input parameters by adding WITH PARAMETERS after the CDS view entity name. The parameters are then defined in a comma-separated list.

Each input parameter must be typed with an elementary data type. The parameter name and the parameter type are separated by a colon (:). The type of a parameter can be a predefined ABAP dictionary type (in the form abap.<type>(<len>)), or a data element. Structure types, table types or reference types are not allowed.

You can use parameter annotations to provide metadata for parameters.

Access to Parameters

To access the input parameters Inside the CDS view definition, you have to use prefix $parameters followed by a period sign (.).

You can use an input parameter in the following positions:

  • As standalone element in the element list
  • As input for a case distinction
  • As input for expressions and functions
  • On the right-hand side of a condition in a WHERE clause
  • On the right-hand side of a filter condition in a path expression

We will now look at some examples here.

The parameter, TargetCurrency, is used directly in the element list. A typical motivation could be that this element is used in a @semantics.amount.currencyCode: annotation for another element.

Here, a parameter is used in a CASE distinction, allowing the consumer to decide whether the free seats or the occupied seats are returned.

In this example, the input parameter TargetCurrency is used as input for a built-in function, namely the currency conversion function. It allows the consumer of a view to specify the currency in which the flight prices are displayed.

In this example, the input parameter Language is used in the filter condition of a path expression. It allows the consumer of the view to specify the language of the text in the element CurrencyName.

In this example, input parameter FirstFlightDate is used in the WHERE condition of the view definition. This establishes a kind of a mandatory selection criterion. The consumer is forced to filter the flights by a minimum flight date.

Input Parameters in Data Preview

When you open the Data Preview tool for a CDS view which defines input parameters, you are prompted to enter actual values for these input parameters before you can analyze the result. Note the asterisks sign (*) next to the parameter names, indicating that the parameters are mandatory.

Only entries that are compatible with the parameter type are accepted. But the tool does not perform any further checks.

Hint

When the focus is on an input field, the screen displays the expected type above the input mask.

Input Parameters in View-on-View

When you define a new CDS view and use a CDS view with input parameters as data source, you have to supply all input parameters with actual values. Let us have a look at an example:

This CDS view reads from a CDS view that defines four parameters.

The parameters are supplied inside a pair of parentheses that follow the name of the source view. As there is not just one parameter, the parameters are supplied in a comma separated list.

Hint

When you define a CDS view that reads from a CDS view with parameters, you can use code-completion (Ctrl + Space) to insert the full signature of the CDS view.

Each parameter name is followed by a colon sign (:), which serves as parameter assignment operator, and after that the actual value. One option is a literal as actual value. Another, very common option is to "forward" a parameter. This means that the calling view defines a parameter itself and uses it to supply the parameter of its data source. Therefore, the consumer of this view is responsible for supplying the actual value. With this technique, you can hand down information through an entire stack of CDS views.

Input Parameters in ABAP SQL

The syntax for supplying the input parameters of a CDS view in ABAP SQL is very similar to the syntax that you use in CDS. The only difference is the assignment operator: In CDS, it is the colon sign ":" whereas in ABAP SQL the equals sign (=) is used instead.

To provide the actual values you can choose from the following options:

Literals
Depending on the type of the parameter, you can use number literals or text literals as actual values.
Host Variables
Instead of literals you can use ABAP constants or ABAP variables to provide the actual values. As usual in ABAP SQL, host variables and constants have to be escaped with the "at" sign (@). The type has to be compatible with the parameter type. In particular, only elementary ABAP data objects are allowed.
Host Expressions
You can even embed entire ABAP expressions in the ABAP SQL statement and use the result as actual value for a view parameter. These host expressions have to be surrounded by a pair of parentheses and prefixed with the "at" sign ( @). Note, that blanks are mandatory after the opening parenthesis and before the closing parenthesis. The expression has to have an elementary result and the type has to be compatible with the parameter type.

How to Define and Use a CDS View with an Input Parameter

Play the video to see how to define and use a CDS view with an Input Parameter.

Access to ABAP System Fields Using CDS View Parameters

The ABAP runtime maintains various system fields that hold information of general interest: the system date and time, the logon client and language, the user name of the current user, and so on. When you want to use the values of these system fields in CDS entities, the recommended way is as follows:

  1. In your CDS view, define input parameters with a type that matches the type of the system field, for example, a date or a language key.
  2. Annotate the parameter with Environment.systemField: and choose the annotation value that is related to the system field you want to access, for example, #SYSTEM_DATE or #SYSTEM_LANGUAGE.

If a parameter is annotated like this, the ABAP runtime will automatically supply the parameter with the value of the related system field, in case no other value is provided by the consumer. As a consequence, you can still supply all parameters as before when you use the view as data source in an ABAP SQL statement. However, the syntax check only forces you to supply the parameters that are NOT linked to a system field. For those parameters that are link to a system field the ABAP runtime uses the value for the related system field as a kind of a default value.

In the example, the two parameters of the CDS view are linked to the system date and the system language. The ABAP code does not supply either of the parameters. Therefore, the ABAP runtime takes the values from the system fields.

Parameters that are linked to ABAP system fields, look very much like optional parameters. However, keep the following in mind: this mechanism only works when accessing the view from within ABAP. In CDS and on database level, the parameters remain mandatory parameters.

Currently the Environment.systemField annotation supports seven different values. The list shows them together with the related system field.

Note

For some of the system fields, direct access is not recommended in ABAP cloud. It is recommended that you use getter methods of ABAP class CL_ABAP_CONTEXT_INFO instead.

How to Access an ABAP System Field in ABAP CDS

Play the video to see how to access an ABAP System Field in ABAP CDS.

Define and Use a CDS View Entity with Input Parameters

You extended your CDS view entity with SQL functions and used literals and session variables as input. Now you replace some literals and session variables with input parameters.

Template:

  • /LRN/CL_S4D430_RLS_PATH_EXPR (Global Class)

Solution:

  • /LRN/C_EMPLOYEE_PAR (Data Definition)
  • /LRN/CL_S4D430_CPS_PARAMETER (Global Class)

Task 1: Define Input Parameters

Create a copy of the CDS view entity that you worked on in the previous exercise (suggested name: Z##_C_EmployeeQueryP, where ## is your group number).

Note

If you have not finished the previous exercise, create a copy of CDS data definition /LRN/C_EMPLOYEE_FNC, instead.

In the new data definition, replace the hard-coded target currency with an input parameter (suggested name: p_target_curr) and session variable $session.system_field with a second input parameter (suggested name: p_date).

Steps

  1. Create a copy of your data definition with SQL functions (suggested name was: Z##_C_EmployeeQuery, where ## is your group number). Alternatively , copy from data definition /LRN/C_EMPLOYEE_FNC (suggested name for the new view entity: Z##_C_EmployeeQueryP).

    1. In the Project Explorer view, right-click the Z##_C_EMPLOYEE_QUERY data definition or the /LRN/C_EMPLOYEE_FUNC data definition to open the context menu.

    2. From the context menu, choose Duplicate ....

    3. Enter the name of your package in the Package field. In the Name field, enter the name Z##_C_EmployeeQueryP, where ## stands for your group number.

    4. Adjust the description and choose Next.

    5. Confirm the transport request and choose Finish.

  2. Edit the definition of your view entity. Define an input parameter to replace the hard-coded currency code for the currency conversion (suggested name: p_target_curr). Use the same type that is used in the CurrencyCodeUSD view element.

    1. Adjust the code as follows:

      Code Snippet
      123456
      
  define view entity Z##_C_EmployeeQueryP
     with parameters 
       p_target_curr : /dmo/currency_code
     as select from Z##_R_Employee

  3. Define a second input parameter to replace the usage of session variable $session.system_date (suggested name: p_date). Use a suitable predefined type.

    1. Adjust the code as follows:

      Code Snippet
      1234567
      
  define view entity Z##_C_EmployeeQueryP
    with parameters 
      p_target_curr : /dmo/currency_code,
      p_date        : abap.dats
    as select from Z##_R_Employee

  4. Annotate the second parameter with a label (suggested value: Date of evaluation).

    Note

    This is not necessary for the first parameter because that parameter is typed with a data element.

    1. Adjust the code as follows:

      Code Snippet
      12345678
      
  define view entity Z##_C_EmployeeQueryP
    with parameters 
      p_target_curr : /dmo/currency_code,
      @EndUserText.label: 'Date of evaluation'
      p_date        : abap.dats
    as select from Z##_R_Employee

  5. In the definition of the CurrencyCodeUSD view element, replace the CAST expression with the input parameter.

    Hint

    Use code-completion to access the parameter.

    1. Adjust the code as follows:

      Code Snippet
      1234
      
   // cast( 'USD' as /dmo/currency_code ) as CurrencyCodeUSD,
   $parameters.p_target_curr as CurrencyCodeUSD,

  6. Adjust the name of the CurrencyCodeUSD view element (suggested name: CurrencyCode).

    Note

    Do not forget to also adjust the value of the two @Semantics.amount.currencyCode annotations.

    1. Adjust the code as follows:

      Code Snippet
      123456789101112131415161718
      
  @EndUserText.label: 'Annual Salary'
  @Semantics.amount.currencyCode: 'CurrencyCode'
  currency_conversion( amount=> AnnualSalary,
                       source_currency => CurrencyCode,
                       target_currency => $projection.CurrencyCode,
                       exchange_rate_date => $session.system_date
                     )      as AnnualSalaryConverted,

  @EndUserText.label: 'Monthly Salary'
  @Semantics.amount.currencyCode: 'CurrencyCode'
  cast( $projection.AnnualSalaryConverted as abap.fltp )
  / 12.0                    as MonthlySalaryConverted,

  // CurrencyCode,
  // cast( 'USD' as /dmo/currency_code ) as CurrencyCodeUSD,
  $parameters.p_target_curr as CurrencyCode,

  7. In the currency conversion, replace session variable $session.system_date with the date parameter.

    1. Adjust the code as follows:

      Code Snippet
      123456789
      
  @EndUserText.label: 'Annual Salary'
  @Semantics.amount.currencyCode: 'CurrencyCode'
  currency_conversion( amount             => AnnualSalary,
                       source_currency    => CurrencyCode,
                       target_currency    => $projection.CurrencyCode,
                       exchange_rate_date => $parameters.p_date
                     )      as AnnualSalaryConverted,

  8. In the calculation of the CompanyAffiliation view element, replace session variable $session.system_date with the date parameter.

    1. Adjust the code as follows:

      Code Snippet
      123456
      
division( dats_days_between( EntryDate,
                             $parameters.p_date ),
          365,
          1 )      as CompanyAffiliation,

  9. Activate and test the CDS view entity.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F8 to test the CDS view entity in the Data Preview tool.

    3. Enter values for the two parameters and choose Open Data Preview.

      Hint

      Mandatory parameters have an asterisk symbol (*) next to the parameter name.

Task 2: Use Input Parameters in ABAP SQL

Create a copy of ABAP class /LRN/CL_S4D430_RLS_PATH_EXPR (suggested name: ZCL_##_PARAMETER, where ## is your group number). In the new class, adjust the SELECT statement. In the FROM clause, replace CDS view entity /LRN/C_Employee_Qry with the CDS view entity that you created in this exercise (suggested name was: Z##_C_EmployeeQueryP). In the FIELDS list, add some view elements that use the input parameters.

Steps

  1. Copy the /LRN/CL_S4D430_RLS_PATH_EXPR class to a class in your own package (suggested name: ZCL_##_PARAMETER, where ## is for your group number).

    1. In the Project Explorer view, right-click the /LRN/CL_S4D430_RLS_PATH_EXP class to open the context menu.

    2. From the context menu, choose Duplicate ....

    3. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_PARAMETER, where ## is your group number.

    4. Adjust the description and choose Next.

    5. Confirm the transport request and choose Finish.

  2. In the IF_OO_ADT_CLASSRUN~MAIN method, change the SELECT statement. In the FROM clause, replace /LRN/C_Employee_Qry with the name of the CDS view entity that you have just created (Z##_C_EmployeeQueryP).

    1. Adjust the code as follows:

      Code Snippet
      123456
      
    SELECT
      FROM Z##C_EmployeeQueryP
    FIELDS employeeid,

  3. Supply the input parameters of the view entity with values. Use a literal for the currency code (for example, 'JPY') and system field sy-datum for the date parameter.

    Hint

    Remember that sy-datum is a host variable and that you have to escape it with @.

    1. Adjust the code as follows:

      Code Snippet
      12345678
      
    SELECT
      FROM Z##C_EmployeeQueryP( 
              p_target_curr = 'JPY',
              p_date        = @sy-datum ) 
    FIELDS employeeid,

  4. Add the following view elements to the FIELDS list: MonthlySalaryConverted, CurrencyCode, CompanyAffiliation.

    1. Adjust the code as follows:

      Code Snippet
      1234567891011121314151617181920
      
   SELECT
     FROM Z##C_EmployeeQueryP( 
            p_target_curr = 'JPY',
            p_date        = @sy-datum )
   FIELDS employeeid,
          firstname,
          lastname,
          departmentid,

          departmentdescription,
          assistantname,
          \_department\_head-lastname AS headname,

          MonthlySalaryConverted,
          CurrencyCode,
          CompanyAffiliation

    INTO TABLE @DATA(result).

  5. Activate the global class and execute it as a console app.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F9 to execute the global class as a console app.

Task 3: Link Parameter to System Field

Make sure that the ABAP system automatically fills it with the system date when no other value is specified. Then remove the parameter from the SELECT statement in your ABAP class.

Steps

  1. Return to the CDS data definition and annotate the p_date parameter to link it to the system date. Use code completion to choose the annotation value.

    1. Adjust the code as follows:

      Code Snippet
      123456789
      
  define view entity Z##_C_EmployeeQueryP
    with parameters 
      p_target_curr : /dmo/currency_code,
      @EndUserText.label: 'Date of evaluation'
      @Environment.systemField: #SYSTEM_DATE
      p_date        : abap.dats
    as select from Z##_R_Employee

  2. Activate and test the CDS view entity.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F8 to test the CDS view entity in the Data Preview tool.

    3. Enter a value for the currency code parameter and choose Open Data Preview.

      Hint

      Mandatory parameters have an asterisk symbol (*) next to the parameter name.

  3. Return to your ABAP class and remove or comment the date parameter in the FROM clause.

    1. Adjust the code as follows:

      Code Snippet
      12345678
      
  SELECT
   FROM /LRN/C_Employee_Par( 
               p_target_curr = 'JPY'
*            , p_date = @sy-datum                                 
                           )
    FIELDS employeeid,

  4. Activate the global class and execute it as a console app.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F9 to execute the global class as a console app.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn