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:
Reads the result to fill the parameter, in this case, it is the view we created in Step 2 (P_QUARTERSTARTPERIOD).
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)
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.
Specifies how the target is filled (by a constant, a parameter or a (filtered) element), where in this case is the #Parameter.
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.
Awesome Blog - Thanks Yuchen Dong
Nice way to structure ABAP CDS code. Thank you for sharing
Job well done!
It is very clear and useful!
This is a great blog and I've referenced it when using consumption derivations at my client.
I do have a follow up question though: is it possible to use consumption derivation with an actual field in my CDS view?
I'm not in the latest support pack so I don't have the equivalent to SY-DATUM available yet, so in my basic CDS view I have a substring formula to derive the current date from the timestamp. I'd then like to use this field as my "passing" value to my other CDS View.
I understand we have three options under binding.type: PARAMETER, CONSTANT and ELEMENT.
I was able to validate the PARAMETER and CONSTANT options, but not ELEMENT. Not sure if ELEMENT is what would apply to the field in the CDS view.
I was wondering if you had any experience in this scenario above. I can provide screenshots if necessary.
Very helpful and clear how to fill the parameters, but unfortunately now I got stuck when it comes to apply the filter range/interval - how did you do it?
When trying to use a where close ( where FiscalPeriod >= $parameters.P_CurrentFromPeriod and
FiscalPeriod <= $parameters.P_CurrentToPeriod ) at the end of the view definition, I get an error saying that I can only use 'OR', therefor I can't activate the view.
Any help is appreciated.
I have almost the same scenario handling of ranges, if you have some breakthrough could you please help with this.
Thank you for your blog. I followed the steps and encotuered the following error:
Here's my MAIN_VIEW with consumption derivation for parameter p_fromDate which looks up value in the view SUPPORTING_VIEW using binding value based on parameter p_startDate defined in the MAIN_VIEW.
Here's my SUPPORTING_VIEW:
It all works fine if I change the binding value to #CONSTANT and provide value manually:
Have anyone encountered and solved the problem with this error?
I have follow the same approach for populating default values and get QTD starting period. But when user manually entered new period or change the period then its not working. can you tell me how to overcome this issue ?
waiting for your reply