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.
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. 🙂
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, LinkedIn Group, Facebook Page, Twitter and Instagram.
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
- CDS Part 1. Core Data Services – Introduction
- CDS Part 2. Core Data Services – Deep Dive
- ABAP on SAP HANA. Part XII. Open SQL, CDS or AMDP, which Code to Data Technique to use?
- CDS Part 3. Expose CDS Views as OData Service through Annotation
- CDS Part 4. Data Access in S/4HANA Cloud – CDS View Introduction
- CDS Part 5. ABAP CDS Views With Authorization
- CDS Part 6. Basic Expressions & Operations Available for CDS View – I
- CDS Part 7. Basic Expressions & Operations Available for CDS View – II
- CDS Part 8. Usage of Built-In Functions in CDS – I
- CDS Part 9. Usage of Built-In Functions in CDS – II
- CDS Part 10. Usage of Built-In Functions in CDS – III
- CDS Part 11. How to Consume CDS View in Smart Business Service KPI Fiori Apps?
- CDS Part 12. Useful 6 CDS Related Tools in ADT
- CDS Part 13. Key Definition in CDS Views
- CDS Part 14. ABAP Annotations for Translatable Texts in CDS Views
- CDS Part 15. Associations in CDS Views – I
- OData Service from CDS Annotation Not Working in Browser Mode
- CDS Part 16. Usage of Built-In Functions in CDS – IV
- CDS Part 17. How to Overcome GUID Mismatch Linking Problem in ABAP CDS?
- ABAP CDS Online Training – October 2019 Batch
- VDM 1 – S/4HANA Embedded Analytics Using CDS Virtual Data Model
- VDM 2 – S/4HANA Embedded Analytics Using CDS Virtual Data Model – Value Helps & Annotations
- CDS Part 18 – Bar Chart & Donut Chart using CDS Views
- VDM 3 – S/4HANA Embedded Analytics Using CDS Virtual Data Model – Meta Data Extensions(MDE)
- How to Append Standard Tables having Replacement Objects in S/4HANA
- ABAP Programming Model for SAP Fiori – 2 – CDS – Introduction
- ABAP Programming Model for SAP Fiori – 3 – CDS Views Creation
- CDS Part 19 – Finding Week of the Year in CDS Views
- CDS Part 20 – Virtual Elements (ABAP Functions) in CDS
- ABAP Programming Model for SAP Fiori – 13 – How to Preview and Download PDF in Fiori Apps
- CDS Part 21 – CDS View Finder Tool
- CDS – 22: How to Extend the CDS View if it Contains Aggregate Functions
- CDS – 23: CDS Performance Analysis – Basics – 1
- CDS with Fiori Elements – 1 – Create Your First Fiori List Report Using CDS
- CDS – 24 – How to build a Master-Detail Fiori Application using CDS Association
- CDS – 25: CDS Performance Analysis – 2
- CDS – 26: Analyzing CDS with Analytical Annotation and Manipulating its Filter Options
- How to Create SM30 Like Table Maintenance T-Code in SAP RAP – Part 1
- How to Create SM30 Like Table Maintenance T-Code in SAP RAP – Part 2 – Validations and Action via RAP
- Table Maintenance Generator from SAP RAP – Part 3 – FIORI UI App
Step by Step Virtual Data Model – VDM Tutorials
- VDM 1 – S/4HANA Embedded Analytics Using CDS Virtual Data Model
- VDM 2 – S/4HANA Embedded Analytics Using CDS Virtual Data Model – Value Helps & Annotations
- CDS Part 18 – Bar Chart & Donut Chart using CDS Views
- VDM 3 – S/4HANA Embedded Analytics Using CDS Virtual Data Model – Meta Data Extensions(MDE)
- VDM 4 – How to Expose & Consume CDS in Power BI?
- CDS – 26: Analyzing CDS with Analytical Annotation and Manipulating its Filter Options
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!
how can we debug or see if virtual elements are executing ?
great blog on virtual element please make a blog on filter and sorting also
Awesome Blog
Thanks Sandeep !
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.
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?
Hi Ahmad,
Thanks for your kind words!
Parameter details not sure. If any working example is found, we shall post it.
Thanks,
Ruthvik.
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.
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.
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
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.