Count Not Zero Exception Aggregation with ABAP CDS View

[uladzislau_pralat]

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à