Technical Articles
Count Not Zero Exception Aggregation with ABAP CDS View
Count Not Zero is not among the options for ABAP CDS View exception aggregation, but if you really want it you can walk around this limitation.
Lets take, for example, C_StockQuantityCurrentValue (Current Stock Quantity and Value) CDS view. It shows all materials even the one without stock quantity and value.
What if you want to get a count of materials that have stock. Yes, it is possible combining formula and SUM exception aggregation together. For comparison reasons, I added another measure using COUNT exception aggregation.
// Count
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: 'Material Count Not Zero'
@AnalyticsDetails.query.decimals: 0
@AnalyticsDetails: {
exceptionAggregationSteps: [
{ exceptionAggregationBehavior: #SUM,
exceptionAggregationElements: ['Product'] }
]
}
@AnalyticsDetails.query.formula:
'CASE WHEN MatlWrhsStkQtyInMatlBaseUnit <> 0 THEN 1 ELSE 0 END' 0 as MaterialCountNotZero,
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: 'Material Count'
@AnalyticsDetails.query.decimals: 0
@AnalyticsDetails: {
exceptionAggregationSteps: [
{ exceptionAggregationBehavior: #COUNT,
exceptionAggregationElements: ['Product'] }
]
}
@AnalyticsDetails.query.formula: '1' 0 as MaterialCount
VoilĂ
Thanks Pralat for sharing this blog .
Excellent example Uladzislau Pralat. One additional question, If I need to remove this lines in zero of the query result, what is the exception aggregation that I need to use ?
Hi Andres,
to suppress zeros please use following annotations:
@Analytics.settings.zeroValues.handling:#HIDE
@Analytics.settings.zeroValues.hideOnAxis: #ROWS
Regards, Uladzislau
Thanks Uladzislau for the great blog!
I have one question, Is it also possible to put Characteristic instead of the Key figure in the Case Statement in in query.formula?
I have a requirement where I need to count the distinct number of materials for a particular status.
Regards,
Sameer
Hi Sameer,
using exception aggregation is a last resort. Your requirements can be fulfilled using nested CDS views. Inner view selects distinct material and status and outer view selecting material and count.
Regards, Uladzislau