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

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Ă 

 

 

 

 

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo SHUBHANGI DESHMUKH
      SHUBHANGI DESHMUKH

      Thanks Pralat for sharing this blog .

      Author's profile photo Andres Diaz Escobar
      Andres Diaz Escobar

      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 ?

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

      Hi Andres,

      to suppress zeros please use following annotations:

      @Analytics.settings.zeroValues.handling:#HIDE
      @Analytics.settings.zeroValues.hideOnAxis: #ROWS

      Regards, Uladzislau