Skip to Content
Technical Articles
Author's profile photo Uladzislau Pralat

Advanced Exception Aggregation with ABAP CDS View

Sometimes you need to use exception aggregation result in subsequent exception aggregation in order to archive desired result with ABAP CDS view. It is possible with multiple exception aggregation steps.

In my example, I need to:

  1. Get a count of distinct Sales Documents
  2. Get average of number by Sales Documents by Sales Organization

The above 2 exception aggregation steps are translated into following ABAP CDS exception aggregation annotation:

  @AnalyticsDetails.query.axis: #COLUMNS
  @EndUserText.label: 'Sales Document Avg by Sales Org'
  @AnalyticsDetails.query.decimals: 0    
  @AnalyticsDetails: {
    exceptionAggregationSteps: [
        { exceptionAggregationBehavior: #COUNT,
          exceptionAggregationElements: ['SalesDocument']  },
        { exceptionAggregationBehavior: #AVG,
          exceptionAggregationElements: ['SalesOrganization']  }                    
    ]
  }
  @AnalyticsDetails.query.formula: '1' 0 as SalesDocumentAvgBySalesOrg

Lets see it in action:

In first step number of distinct Sales Documents is calculated reducing context to just Sales Document, for example:

In second step number of Sales Documents is averaged out adding Sales Organization into context:

Complete ABAP CDS views source code can be downloaded from GitHub:

ZI_ExceptionAggregationCube

ZC_ExceptionAggregationQuery

 

 

 

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Wouter van Heddeghem
      Wouter van Heddeghem

      Great blog Uladzislau !

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai

      Good one . Thanks for sharing

      Author's profile photo Hari Prasad Gupta
      Hari Prasad Gupta

      Alway find your blog useful.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Thanks for sharing! I was a bit confused at first because "exception" for me associates mostly with exceptions in OOP context, i.e. more like errors. 🙂 But I'm glad I found this blog, even if by accident.

      And thank you for sharing the examples on Github!

      Author's profile photo Madhu obireddy
      Madhu obireddy

      Great Job Pralat, Thanks for Sharing

       

      I have one requirement

       

      I want to get the Count of Sold-to-party that have done bussinees(meaning Sold Cases >0)

      Basically need a Count on Sold-to-party where Sold Cases greater than 0

       

      Please let me know any Suggestions

       

      Thanks

      Madhu

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Madhu,

      see my new blog Count Not Zero Exception Aggregation with ABAP CDS View how to approach to your requirement.

      Regards, Uladzislau

       

      Author's profile photo Madhu obireddy
      Madhu obireddy

      Thanks Pralat, it Worked

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Madhu,

      glad I could help.

      Regards, Uladzislau

       

      Author's profile photo SHUBHANGI DESHMUKH
      SHUBHANGI DESHMUKH

      Thanks for sharing this blog. My requirement is to find the average of the quantity based on the starting period of the year till period inputted . For example input is 2021005 , then it should get the average for 5 periods. First I have calculated SUM ,but for average aggregate function , I am not sure how to apply this formula.Also how to extract period value from 2021005 , the data type for this is NUMC(7).

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Shubhangi,

      your requirement is more complex. Please consider creating BEx query on top of CDS view cube transient provider.

      Regards, Uladzislau

       

      Author's profile photo Georgi Nushev
      Georgi Nushev

      Hi Uladzislau Pralat

      Thanks for the blog.

      Is there then a way to get this CDS also displayed as a Data Source when creating a new analytical query from the "Custom analytical queries" App?

      I have a CDS View with such exceptionAggregationSteps, but for that it must have the Analytics.query: true.

      If we have this option, we are not able to use the #CUBE category and the CDS is not visible as data source?

      I could see the COMPOSITION View, but it does not have those aggegations, which are defined by the exceptionAggregationSteps in the CONSUMPTION View.

      Any suggestions?

       

      Kind regards,

      Georgi

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Georgi,

      "Custom analytical queries" app is designed for users, not developers. Advanced aggregation concepts goes beyond users expertise and that is why is not supported by the app.

      Uladzislau

       

      Author's profile photo Georgi Nushev
      Georgi Nushev

      Thanks Uladzislau Pralat  for the fast answer.

      I think you did not understand my question correclty.

      I have the CDS View with the aggegations defined (some of them are exceptionAggregationSteps) , which I would like to provide to the user as a data source for his/her custom analytical query.

      Kind Regards,

      Georgi

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Georgi,

      you can not create query on top of query. Only query on top of cube.

      Uladzislau