Technical Articles
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:
- Get a count of distinct Sales Documents
- 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:
Great blog Uladzislau !
Good one . Thanks for sharing
Alway find your blog useful.
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!
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
Hi Madhu,
see my new blog Count Not Zero Exception Aggregation with ABAP CDS View how to approach to your requirement.
Regards, Uladzislau
Thanks Pralat, it Worked
Hi Madhu,
glad I could help.
Regards, Uladzislau
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).
Hi Shubhangi,
your requirement is more complex. Please consider creating BEx query on top of CDS view cube transient provider.
Regards, Uladzislau
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
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
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
Georgi,
you can not create query on top of query. Only query on top of cube.
Uladzislau