Skip to Content
Technical Articles

Aggregation using CDS view for any given slice and dice in odata — Part 1


There are many blogs on aggregation using CDS view or without CDS view, even there is topic called embedded analytics where in all KPI, Charts and aggregation is achieved and using KPI modeler one can consume all these functionality.

All you need is the understanding of the annotations and the CDS views (most of the part) and yes if you haven’t started digging on that, start now, don’t be left behind.

There are already many blogs available for embedded analytics, cds views with analytical capabilities and stuff, which has in-depth information. I will be mentioning those in the references.

In this blog I will try to cover dynamic aggregation for any given dimensions and measures, similarly pivot table in ms excel sheet.


Basic knowledge of ABAP CDS view, SAP Odata service, Cration of odata service using CDS view with reference data source.

Dynamic aggregation(for any given slice and dice of dimensions and measures) can be achieve either we can go with abap(group by) or CDS view.

And in CDS view also there are two ways either using SADL Layer in Odata or Analytical engine framework  which can generate the aggregations for you automatically.

This blog will basically give information about analytical application wherein for given LOB how odata service for charts and KPI single service can be use for aggregation any slice and dice of dimensions and measures.

Aggregations in CDS Views

So basically we can add analytical capabilities to a CDS view using two approaches —

  1. Basic Analytical CDS View -> For simple analytical Fiori apps, which uses SAP SADL layer queries
  2. Advance Analytical CDS view(Handled in Analytical engine ) -> For Complex analytical Fiori apps with annotation @Analytics.query: true  that uses Analytical Engine (Cubes and stuff)

What is it about Basic and Advanced stuff..??

Basic means, you don’t need to have a view which has the annotation  @Analytics.query: true and only basic calculations will work. The Query will be handled by the SADL layer (Dynamic ABAP Select query)

For Advance Analytical capabilities like exception aggregations, complex formulas, query browser app etc.., which the analytical engine (BW) provides, we need to go with the Analytical Query CDS view.


Advance Analytical CDS view

So Using Advance Analytical CDS view(Embedded analytics) in CDS view we can use annotation Analytics.datacategory and Analytics.query. And we need to put aggregation.default or default.aggregation annotation on measures so that in metadata of odata will clearly get to know which fields are dimension and which one are measures. For BW Analytical engine, what are advantage and how to use Mahesh Kumar Palavalli will publish in next blog.


Aggregation using SADL layer

When we want to use single service for read(including analytical quires, KPI, Charts, Stories and aggregation) and the same service need to be use for CUD operations then it approach can be use.

So lets get started  —

first create CDS view according to business requirement using VDM concepts as below —


add annotation on measures as below —



So what have we got here is the annotation @DefaultAggregation : #SUM, this annotation makes that particular column as a measure and does the sum of that column data and #AVG will get the average value.

now in next step  create odata service using RDS(Refrence data source) which is best approach for generating odata service

once we introduce default.aggregation on measures, In metadata of service(which is created by RDS) all other fields become dimension as below —


Rest all properties become dimension in metadata as below–


now the given service will work as it is, except there will be one ‘Analytical ID’  key get generated suppressing all the other semantics key, how to resolve this technical key into semantics key I will cover on later part, which is used for fetching single record, navigation from one entity to other, or updating and deleting  for given semantics key.

In Below Image .1 is aggregation Id followed by sales order(semantic key) then subsequent key 000001 is sales order item and at last PUSD is Parameter where in value is usd(currency conversion).



If we are  executing service then it wont give aggregation value but it will run as normal service

with all filters, count, inline count, pagination($top,$skip) depending on URL call

If we want aggregation(groupby) on given dimension then we need to call URL as below and it will give aggregation based on given dimensions for the measures.



In Below use case we are passing netwr and vkorg, so based on organisation dynamic groupby is result set is coming .


by using above url we can see for any given dimension and measures results will come in pivot(like in excel pivot is there, which can be used in different charts and  executive dashboard.

below is the results set —

Based on Sales Orgnization and division(spart) below is aggregation

Based on Sales org, division, and sales area–




  •         If you pass all non measure fields, again the aggregation will not be applied, which is obvious.


  • Always pass the aggregation annotations at the consumption view level as these annotations won’t be propagated from child view(interface,basic) to the parent view(consumption).


  • Don’t create the CDS view with aggregations manually like SUM or AVG or COUNT and expose them as the OData service for the Fiori App. It will loose it’s charm, try to design it in a generic way using defualtaggregation annotations, so that it can be reusable in different smart controls.

Now again there are different cases

1.) Simple CDS view as above

In case of simple CDS view for navigation,update, delete or for single record fetch(using key) we need to use key, to resolve Analytical ID into semantics key there is already interface method we can directly used as below —

if_sadl_gw_dpc_util~get_dpc( )->get_keys_from_analytical_id(
EXPORTING io_tech_request_context = io_tech_request_context
IMPORTING et_keys = DATA(lt_keys) ).
this method is already implemented in DPC class.
2.)CDS view with parameter — If  we are not using annotation default.aggregation and using CDS view with parameter then there will be 2 entities get generated, and in URL can be use as below —

URL : /sap/opu/odata/SAP/Service_Name/Entity(p_parameter=’Value’)/Set

But as we are using annotation default.aggregation so instead of Set there will be Results in URL as below —

URL : /sap/opu/odata/SAP/Service_Name/Entity(p_parameter=’Value’)/Results

In Next Blog Mahesh Kumar Palavalli will cover Advance Analytics, after that if time permits I will try to cover CUD(Create, Update and Delete) operation using same service, with and without input parameters in CDS view, using currency and Unit conversion, Yeartodate, Monthtodate and quatertodate calculation using CDS view.

Feel free to comment and let me know if I miss something here or you would like to add something to this as I am also in the learning stage at this point.

Thanks a lot to Mahesh Kumar Palavalli for making me understand difference between both approach Sadl layer and analitical layer



Abhijeet Kankani





You must be Logged on to comment or reply to a post.
  • /
  • Hi Abhijeet,

    Firstly, kudos for this interesting and helpful blog.

    But I find the SAP handling of the Generated ID key, suppressing the existing semantic keys, pretty discomforting.

    As you know, the development and implementation phase is always incremental, based on changing requirements. Now assume that I have no requirements for any measurements to be displayed. (Here, I am referring to SAPUI5-based applications and not Fiori-WebIDE based ones). So my consumption CDS view(which is exposed as OData Service) does not have any annotations for aggregation. All my existing key-based actions like read, navigation, etc would have been implemented using the semantic keys.

    Now based on a new requirement, I happened to add few aggregations in my consumption CDS view. Then all my existing implemented and working scenarios are going to fail, as the framework has replaced my entity keys.

    Correct me if my understanding is incorrect. Your valued inputs and opinions would be highly helpful.



    • Hi Samson,

      Yes, once you introduce aggregation annotation in CDS view, it will generate new technical key supressing the existing semantic key.

      And it will break design for the navigation using semantic key, but one can use new generated key while doing navigation(which can be found in metadata).

      As you are saying combination of using few cds view as aggregation notation and few cds without aggregation will not work, which is obvious, but what alternative solution is make all cds view as aggregated view wheather we are using measures or not.

      Same challenge we faced as you mentioned and we introduced one more field on each cds called count  as aggregated and all cds view become aggregated cds view as below --

      @DefaultAggregation: #SUM

      1 as count_

      And if we want to read semantic key for any purpose, it can be read by using below method --

      if_sadl_gw_dpc_util~get_dpc( )->get_keys_from_analytical_id(
      EXPORTING io_tech_request_context = io_tech_request_context
      IMPORTING et_keys = DATA(lt_keys) ).

      Hope this will help you.





  • Hi Abhijeet,

    It was really wonderful to learn through your blog. I had a question on this. Is it possible to write subqueries using odata. For ex: We fetch a list of 2 fields(1 dimension and 1 measure aggregated) with only top 4 values. and then write a subquery to aggregate the measure for those 4 entries?. I am not able to to achieve the second part.

    Below is the query for reference:




  • Hi Dutta,


    Happy to hear that this blog help you.

    I believe 'SlsVolumeNetAmtInDspCrcy' is measure and SalesOrganization,SalesOrganization  are dimension here.

    So you will get groupby on SalesOrganization and SalesOrganization_T .



    Abhijeet Kankani

  • Hi Abhijeet ,

    Thanks for the blog. It is highly informative. Wanted to know if you are planning to release further version of this blog.
    I am getting issues while using both aggregation and parameters in CDS and then using the CDS(as Reference Data Source) , I am unable to get any output in list report .
    If i consume this service in analytical list report then only table view comes(with data) but without any aggregation on UI . Any ideas here?


  • Hi Anoop,

    Happy to hear that this article helped you.

    Even if someone using with parameter in CDS views it will work as it was working previously.

    There is two case – 1.) If you are calling URL without $select, it will give you a list view.

    2.) If you are using $seect in URL so it will give you only those fields which you selected and it will aggregate all your measures based on dimensions.

    It doesn’t matter what UI/UX you are using, the service will be giving you data in the same way, it is just UI how you are consuming.

    Hope it will clear your query.


    Abhijeet Kankani