36

Count Not Zero Exception Aggregation with ABAP CDS View

 2 years ago
source link: https://blogs.sap.com/2021/04/22/count-not-zero-exception-aggregation-with-abap-cds-view/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Technical Articles
Posted on April 22, 2021 Less than a 1 minute read

Count Not Zero Exception Aggregation with ABAP CDS View

0 Likes 44 Views 0 Comments

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.

ABAP-CDS-View-Exception-Aggregation-Count-Not-Zero-1.jpg

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à

ABAP-CDS-View-Exception-Aggregation-Count-Not-Zero-2-1.jpg


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK