Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
uladzislau_pralat
Contributor
4,675
In first partsecond part, third part and fourth part 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       


}








 

CDS Views can be downloaded from here:

zsapbc_flight_cube5

zsapbc_flight_query5
2 Comments
Labels in this area