ABAP CDS Consumption View Features Ultimate Test: Part 5

In first part, second part, third part and fourth of my blog I put ABAP CDS Consumption View to test and fulfilled some of most typical BW Query requirements. In fifth part of my blog I will challenge ABAP CDS Consumption View again. This time demonstrating exception aggregation using formula and count

I created CUBE views and finally created CONSUMPTION view. I will not go much into details of implementation because all that you can explore on your own. What is more important is to understand ABAP CDS views capabilities.

As you can see from screenshot below using exception formula aggregation count of low occupied flight count (occupancy below 96 %) and high occupied flights (occupancy is above 96 %) is calculated as well using exception count calculation overall flight count is calculated.

Drill down on Airline, Flight and Date level demonstrates how measures are calculated

Cube View:

ZSAPBC_FLIGHT_CUBE5

@AbapCatalog.sqlViewName: ‘ZSFLIGHT_CUBE5’

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@Analytics.dataCategory: #CUBE

@VDM.viewType: #COMPOSITE

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: ‘Flights’

define view zsapbc_flight_cube5 with parameters

  p_display_currency : s_currcode   

  as select from zsapbc_flight_fact( p_display_currency:  $parameters.p_display_currency ) as flight

    association [1..1] to zsapbc_carr_dimension as _carr on

$projection.carrid  = flight.carrid

    association [1..1] to zsapbc_region_dimension as _region on

$projection.region  = flight.region

      {

  @ObjectModel.foreignKey.association: ‘_carr’

  key flight.carrid,

  key flight.connid,

  @EndUserText.label: ‘Date’

  key flight.fldate,

  @EndUserText.label: ‘Region’

  @ObjectModel.foreignKey.association: ‘_region’

  flight.region,

  _carr,

  _region,

  @Semantics.calendar.yearMonth: true

  @EndUserText.label: ‘Month’

  flight.flmonth,

  @Semantics.calendar.year: true

  @EndUserText.label: ‘Year’

  flight.flyear,

  @Semantics.currencyCode: true

  @EndUserText.label: ‘Booking Currency’    

  flight.currency,

  @Semantics.amount.currencyCode: ‘Currency’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Booking (bc)’

  flight.payment,

  @EndUserText.label: ‘Display Currency’ 

  cast(‘CAD’ as abap.cuky( 5 )) as disp_curr,

  @Semantics.amount.currencyCode: ‘disp_curr’

  @EndUserText.label: ‘Booking (dc)’

  @DefaultAggregation: #SUM

  flight.payment_disp_curr,

  @Semantics.unitOfMeasure: true

  @EndUserText.label: ‘UOM’

  flight.unit,

  @Semantics.unitOfMeasure: true

  @EndUserText.label: ‘%’

  cast( ‘%’ as abap.unit( 3 ) ) as unit_percent,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Max Econ.’ 

  flight.seatsmax,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Max Bus.’ 

  flight.seatsmax_b,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Max 1st’

  flight.seatsmax_f,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Max Total’

  seatsmax_total,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Occ Econ.’

  flight.seatsocc,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Occ Bus.’ 

  flight.seatsocc_b,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Occ 1st’ 

  flight.seatsocc_f,

  @Semantics.quantity.unitOfMeasure: ‘unit’

  @DefaultAggregation: #SUM

  @EndUserText.label: ‘Seats Occ Total’

  seatsocc_total,

  @Semantics.quantity.unitOfMeasure: ‘unit_percent’

  @DefaultAggregation: #MAX

  @EndUserText.label: ‘100%’

  100 as hundred 

 }

Consumption View:

ZSAPBC_FLIGHT_QUERY5

ZSAPBC_FLIGHT_CUBE5

@AbapCatalog.sqlViewName: ‘ZFLIGHT_QUERY5’

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck:#CHECK

@VDM.viewType: #CONSUMPTION

@Analytics.query: true

@EndUserText.label: ‘Flight Query’

define view zsapbc_flight_query with parameters

  @EndUserText.label: ‘Display Currency’    

  @Consumption.defaultValue: ‘CAD’

  p_display_currency : s_currcode   

  as select from zsapbc_flight_cube5(

p_display_currency:

$parameters.p_display_currency ) {

  @AnalyticsDetails.query.axis: #ROWS

  @AnalyticsDetails.query.displayHierarchy: #ON

  @Consumption.filter: { selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false }  

  @AnalyticsDetails.query.hierarchyInitialLevel: 3

  @AnalyticsDetails.query.variableSequence: 1    

  region,

  @AnalyticsDetails.query.axis: #FREE   

  connid,         

  @AnalyticsDetails.query.axis: #FREE

  @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false }

  @AnalyticsDetails.query.variableSequence: 2     

  carrid,

  @AnalyticsDetails.query.axis: #FREE

  fldate,

  @AnalyticsDetails.query.axis: #FREE

  flmonth,

  @AnalyticsDetails.query.axis: #FREE

  @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false }

  @AnalyticsDetails.query.variableSequence: 3   

  flyear,

  @AnalyticsDetails.query.axis: #FREE

  currency,

  @AnalyticsDetails.query.axis: #FREE

  disp_curr,

  @AnalyticsDetails.query.axis: #FREE

  unit,   

  @AnalyticsDetails.query.axis: #COLUMNS

  seatsocc_total,

  @AnalyticsDetails.query.axis: #COLUMNS

  seatsmax_total,

  @AnalyticsDetails.query.axis: #COLUMNS

  @AnalyticsDetails.query.decimals: 1

  @EndUserText.label: ‘Seats Occ (%)’

  @AnalyticsDetails.query.formula: ‘NDIV0( NODIM( seatsocc_total

) /

                                           NODIM( seatsmax_total

) ) * hundred ‘

  1 as seats_occ_prc,

  @EndUserText.label: ‘Low Occupied Flight Count’

  @AnalyticsDetails: {

    exceptionAggregationSteps: [{

      exceptionAggregationBehavior: #SUM,

      exceptionAggregationElements: [‘carrid’, ‘connid’,’fldate’ ] }]

  } 

  @AnalyticsDetails.query.axis: #COLUMNS 

  @AnalyticsDetails.query.formula: ‘case when $projection.seats_occ_prc

< 96 then 1 else 0 end’

  0 as flight_cnt_low_occ,

  @EndUserText.label: ‘High Occupied Flight Count’   

  @AnalyticsDetails: {

    exceptionAggregationSteps: [{

      exceptionAggregationBehavior: #SUM,

      exceptionAggregationElements: [‘carrid’, ‘connid’,’fldate’ ] }]

  } 

  @AnalyticsDetails.query.axis: #COLUMNS 

  @AnalyticsDetails.query.formula: ‘case when $projection.seats_occ_prc

>= 96 then 1 else 0 end’

  0 as flight_cnt_high_occ,

  @EndUserText.label: ‘Flight Count’   

  @AnalyticsDetails: {

    exceptionAggregationSteps: [{

      exceptionAggregationBehavior: #COUNT,

      exceptionAggregationElements: [‘carrid’, ‘connid’,’fldate’ ] }]

  } 

  0 as flight_cnt,

  @AnalyticsDetails.query.hidden: true     

  @AnalyticsDetails.query.axis: #COLUMNS

  payment,   

  @AnalyticsDetails.query.hidden: true 

  @AnalyticsDetails.query.axis: #COLUMNS

  payment_disp_curr       

}