How to create ABAP CDS View with enabling derivation of the value for parameters

There have been dozens of blogs online for walking through developers on how to create ABAP CDS view with parameters in ABAP for HANA, however, one major issue still exists is that although input parameters can restrict or filter the data from CDS views, the limitation of the value input for a parameter to filter out more specific data range is significant.

Here the author presents you a way to enable derivation of the value for parameters to tackle similar problems in your future development using the example shown below.

Taking a financial case as an example, if we want to trace back the last three months data instead of user selected month period using the input parameter as the filter, creating parameters traditionally without any derivation would be insufficient.

This is one example in Analysis for Office 2.4 SP3 (latest version), where the prompt for user input is shown as below:

In this simple case, our objective is to get the last three months of data tracing back from the end of April, which should include February, March and April data, and the user inputs have been shown above.

Here are the steps on how to realize this functionality by enabling derivation of the value for user input parameters.

Step 1: Create a private basic view for the parameters we want to make derivation with in the bottom layer:

Rationale: This basic view is to make sure it only returns one record all the time, so we can create a dummy table with only one row.

Step 2: Create another similar basic view on top of the previous view, but here we include all the necessary calculations to the parameter:

Rationale: CDS view parameters cannot be used directly in the same view. That is why we need to have these two layers of views as shown above. FYI, the red block represents the logic of this input parameter derivation.

Step 3: In a consumption view where we want to use this derivation along with our defined parameters, we can create a parameter where user can fill in their initial values, say April, in this case. This is more like a starting point of the user period selection:

Step 4: Add the derivation annotation on top of the second parameter, where we plan to integrate the derivation, and we hide this parameter to make it compatible with the prompt you saw before Step 1:

Rationale:

@Consumption.derivation.loopupEntity

Reads the result to fill the parameter, in this case, it is the view we created in Step 2 (P_QUARTERSTARTPERIOD).

@Consumption.derivation.resultElement

Element of the entity/scalar export parameter of the procedure providing the result, where in this case is the end parameter name we defined after calculation and derivation (QuarterStartPeriod)

@Consumption.derivation.binding.targetElement

This enables provision of a parametrization for the (scalar) input parameters of the lookupEntity procedure. This sub-annotation specifies a parameter name of lookupEntity or procedure, where in this case is the parameter we created in Step 1 and Step 2, i.e. P_Period.

@Consumption.derivation.binding.type

Specifies how the target is filled (by a constant, a parameter or a (filtered) element), where in this case is the #Parameter.

@Consumption.derivation.binding.value

It contains, depending on the type, the constant value, the parameter name or the element name, where in this case is the parameter we defined in Step 3 of this consumption view, in other words, is the parameter we want to derive from, i.e. P_CurrentToPeriod.

After these steps, the user should be able to get the last three months data with only one user input, which in this case is February, March and April, according to the logic we made in Step 2. The following is an example, showing part of the report after clicking OK in prompt with user input April as a requesting period:

The applications of CDS view parameter derivation are enormous, developers can easily utilize this feature and thus make the future CDS view development more smoothly and flexible, no matter what frontend tools we implement.