CDS Part 20 – Virtual Elements (ABAP Functions) in CDS

May 26, 2020

45601

Another great problem statement from SAPYard fan has resulted in making this article come alive. Thanks to all of you for being a wonderful set of people who have encouraged us and enabled us to think out-of-box.

Let us first understand the problem statement.

Problem Statement:
We need to achieve the functionality of computing the Sales Running Total for a set of data consisting different company codes, material, batches for different months of the year through CDS (Core Data Services).

Possible Solutions:
1. Using SQL windows functions in ABAP CDS views based on Table Function with OVER / PARTITION operator.
DRAWBACK: If any value misses in between, the running total restarts with new value.
2. Using union of 12 CDS Views for 12 time periods grouped correctly with possibility of matching company code.
DRAWBACK: Very simple disadvantage is we can just target one single company code and also keeping material and batches same.

It took me around a fortnight to get a solution. Some might have guessed the importance of 14 days during this time but you are wrong. Don’t relate that and this please.

From day 1 since problem statement was given, there was a need for me to improvise for testing it. So, I created a Z-Table and also added data from back end.

Step 1:

Create a Custom Table which can mimic the production system.

zcc – Company Code, zmat – Material Number, zbatch – Batch Number, zyear – Year , zperiod – Month of the Year, zsales – Sales Quantity, zuom – Unit of Measure

Step 2:

Insert Records.

Step 3:

Run the DB data preview to see if the records are well and truly created.

Carefully observe above. There is NO record for month ‘03‘ of Year 2020 for Company Code ‘1000’, Material ‘M123’ and Batch ‘B123’.

Also, the cumulative sales or what is called as Running Total of Sales is also not available as it is just a DB Table.

Announcement

We are starting a new Instructor-Led Paid Training on Advanced ABAP CDS with SAP Fiori Elements. This will be 1 hour daily on working days from 7:30 AM to 8:30 AM IST starting 15th of June 2020. Please register using this link and we will share more details soon.

Step 4:

Create a CDS.

We will spend some time in this step of course. :).

Step 4.1:

First observe use of annotation “@OData.publish” in Line 6.

Limitation:
The proposed solution works very well in case we are exposing the CDS as a OData Service.

But, we would be very thankful if anyone finds and tells us there is any other source that is also giving us same results. Share and Comment below.

Step 4.2:

New concept of ABAP Code Exits for Virtual Elements !!!

SAP has been very responsive to the issues that cannot be easily achieved through CDS.

One such problem was that of implementing complex logic for a CDS when fields to be calculated are not available in DB Table. We can call them dynamic fields which are virtual, not existing physically in a memory location as part of DB table.

Recommendation from SAP is to use Virtual Elements in following cases:

Calculating values of fields that are not part of a persistence model
Filtering of calculated field values
Sorting of calculated field values

In this article only first of the three are touched upon. Following is the skeleton of the syntax:

define view <CdsConsumptionView>
		as select from <data_source>
{
		...
		@ObjectModel.readOnly: true
		@ObjectModel.virtualElement
		@ObjectModel.virtualElementCalculatedBy: 'ABAP:<code_exit_class>'
		cast( '' as <dtype> preserving type) as <view.element>
		...
}

Check ‘ABAP:ZCL_CDS_FUNCTION’ at line 21 with annotation @ObjectModel.virtualElementCalculatedBy:.

Step 4.3:

Observe that there is NO value in the “running_total” field of the data preview of CDS. Why?

Answer: The CDS as is known runs on DB Layer. So, effectively the Virtual Elements values are NOT calculated in DB. Hence, is not seen in Data Preview. The moment we run Odata (in application layer), the calculated values are seen. Therefore the runnig_total comes blank when we do data preview in Eclipse.

Step 5:

Create a new class ‘ZCL_CDS_FUNCTION‘.

Step 6:

Let’s explore more about interface:

“it_original_data” – The values are populated from the CDS, at run-time. All the calculated data like aggregations etc. that are part of the values fetched from the DB layer is pulled from this importing parameter.

“it_requested_calc_elements” – List of elements that are Virtual Elements, nothing but the fields that we are supposedly going to calculate at the run time.

“ct_calculated_data” – The end result of the data calculated in the method and that will be displayed as the output in the Odata.

Also Read: CDS Part 16. Usage of Built-In Functions in CDS – IV

Step 7:

Activate the service we have created.

Step 8:

Test the service by running the metadata from Gateway Client.

Step 9:

Load the entity set “ZRUN_TTL_CDS”.

Explanation:
The values of “virtual elements” are calculated in run-time on the Application Layer. To testify this fact, we can see that “zcl_cds_function” is available in the AS.

We repeat again. The CDS entity gets executed at DB Layer. And, effectively the Virtual Elements values are NOT calculated in DB. Hence, the Virtual Elements are not seen in Data Preview. The moment we run Odata, the calculated values are visible as the OData gets executed at the Application Layer level and the Virtual Elements are calculated at the Application Layer.

Step 10:

Test, Test and Test and Validate. You will like it. 🙂

*Various Line Item data from the OData Output in GW Service Testing for Company Code 1000*

*Various Line Item data from the OData Output in GW Service Testing* for Company Code 2000

Company Code	Material	Batch	Period	Sales Quantity	Running Total (Cumulative Sales Qty)
1000	M123	B123	Jan-20	100	100
1000	M123	B123	Feb-20	146	246
					246
1000	M123	B123	Apr-20	98	344
1000	M123	B123	May-20	102	446
1000	M123	B123	Jun-20	106	552
1000	M461	B123	Jan-20	153	153
1000	M461	B123	Feb-20	85	238
1000	M461	B123	Mar-20	40	278
1000	M461	B123	Apr-20	59	337
1000	M461	B123	May-20	115	452
1000	M461	B123	Jun-20	126	578
1000	M461	B777	Jan-20	256	256
1000	M461	B777	Feb-20	202	458
1000	M461	B777	Mar-20	236	694
1000	M461	B777	Apr-20	110	804
1000	M461	B777	May-20	189	993
1000	M461	B777	Jun-20	215	1208
2000	M123	B123	Jan-20	78	78
2000	M123	B123	Feb-20	91	169
2000	M123	B123	Mar-20	93	262
2000	M123	B123	Apr-20	85	347
2000	M123	B123	May-20	88	435
2000	M123	B123	Jun-20	71	506
2000	M461	B123	Jan-20	152	152
2000	M461	B123	Feb-20	156	308
2000	M461	B123	Mar-20	152	460
2000	M461	B123	Apr-20	156	616
2000	M461	B123	May-20	189	805
2000	M461	B123	Jun-20	143	948
2000	M461	B777	Jan-20	26	26
2000	M461	B777	Feb-20	56	82
2000	M461	B777	Mar-20	69	151
2000	M461	B777	Apr-20	78	229
2000	M461	B777	May-20	71	300
2000	M461	B777	Jun-20	89	389

We were really excited to learn about Virtual Elements and get this solution. Hope you find it interesting and worth trying. Please stay tuned, we shall be bringing more unique contents as we experiment and learn.

Please follow our LinkedIn Page, Linke dIn Group, Facebook Page, Twitter and Instagra m.

Save our number +1-646-727-9273 and send us a Whatsapp message ‘LEARN’ to be part of our Learning Community.

Free Step by Step Core Data Services Exercises

Step by Step Virtual Data Model – VDM Tutorials

12 COMMENTS

shubham August 14, 2021 At 8:49 am

Great blog Ruthvik. But I would like to know if we are going to use ABAP Code in CDS then how this will be handled by the complier will the code be executed on DB layer or Application layer. Is Code push down achieved here? Thanks!

kaveri March 22, 2021 At 12:27 pm

how can we debug or see if virtual elements are executing ?

santosh January 8, 2021 At 8:53 am

great blog on virtual element please make a blog on filter and sorting also

Sandeep October 19, 2020 At 10:04 am

Awesome Blog

- Ruthvik Gautham October 20, 2020 At 12:57 pm
  
  Thanks Sandeep !
  
Surbjeet Dhillon September 25, 2020 At 7:56 am

As an alternative to using OData Publish you can use SEGW to import the CDS as a Data Source Reference. From my experience as soon as the CDS becomes analytical (e.g. by using annotations for aggregation) then virtual elements are no longer supported or called.

Ahmad July 14, 2020 At 10:46 pm

Awersome!!

Thanks for the great blog.
So this capture the line details and we calculate in ABAP, so just checking if our ABAP Class captures paramters details in cds view?

- Ruthvik Gautham July 23, 2020 At 12:06 am
  
  Hi Ahmad,
  
  Thanks for your kind words!
  
  Parameter details not sure. If any working example is found, we shall post it.
  
  Thanks,
  Ruthvik.
  
Saswata May 27, 2020 At 6:38 am

Great article but this doesn’t work in 750 release…Could you please also update the minimum version required to achieve this? I guess it is 752 but wanted to double check.

- Ruthvik Gautham May 30, 2020 At 12:51 am
  
  Hi Saswata,
  
  Thanks for trying out the logic. We need to check if this works in 750 release. As and when we get any observation, will be more than happy to share it here.
  
  Thanks,
  Ruthvik.
  
Himanshu Kawatra May 27, 2020 At 2:05 am

Hi Ruthvik Gautham,

This is really a great blog and you made that so simple for beginner …
Even i have used that virtual element concept so many time in real time development But i still found that table function is better approach. because over their every thing is on DB layer. And We can achieve that same scenario by using CDS Table function also.

But still appreciate your effort to make it so simple…

Cheers,
Himanshu Kawatra

- Ruthvik Gautham May 30, 2020 At 12:49 am
  
  Hi Himanshu,
  
  Firstly a big thank you for being very constructive in your thoughts and encouragement given to us.
  
  As you have rightly said and as has been already mentioned in the article, the same functionality can be achieved using table function. The drawback was OVER / PARTITION operator does not include the missing records in between as zero and continue. That is where this code was helpful to achieve desired result.
  
  Thanks,
  Ruthvik.