ABAP CDS Consumption View Features Ultimate Test: Part 2

[uladzislau_pralat]

In first part of my blog I put ABAP CDS Consumption View to test and fulfilled some of most typical BW Query requirements. In second part of my blog I will challenge ABAP CDS Consumption View even more. This time setting variable value dynamically (similar to BW OLAP Variable user-exit functionality).

I created BASIC Views to derive current and previous years and used them in CONSUMPTION view to set variable values dynamically.

As a result, current and previous year are calculated dynamically every time user run query.





You can create demonstrated ABAP CDS Consumption View and all other underlying view using source code below.

 

BASIC Views:

ZSAPBC_CURR_DATE

---

@AbapCatalog.sqlViewName: 'ZCURR_DATE'

define view zsapbc_curr_date as select from tadir

{

$session.system_date as system_date

}

where obj_name = 'ZSAPBC_CURR_DATE'

  and object   = DDLS'

---

 

ZSAPBC_CURR_YEAR_ADD_N

---

@AbapCatalog.sqlViewName: 'ZCURR_YR_ADD_N'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Current Year
Plus Offset'

define view zsapbc_curr_year_add_n with parameters

  p_offset : abap.int2

as select from zsapbc_curr_date

{

( cast(cast(substring(system_date,1,4) as abap.numc(4)) as abap.int2) + :p_offset )  as p_year

}

---

 

CONSUMPTION View:

ZSAPBC_FLIGHT_QUERY2

---

@AbapCatalog.sqlViewName: 'ZFLIGHT_QUERY2'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck:#CHECK

@VDM.viewType: #CONSUMPTION

@analytics.query: true

@EndUserText.label: 'Flight Query'

define view zsapbc_flight_query2 with parameters

  @EndUserText.label: 'Display Currency'    

  @Consumption.defaultValue: 'CAD'

  p_display_currency : s_currcode,

  EndUserText.label: 'Year 1'    

  @Consumption.derivation: {lookupEntity: 'ZSAPBC_CURR_YEAR_ADD_N',                                                                                                     resultElement: 'p_year' ,

  binding: [{ targetParameter: 'p_offset',

             type: #CONSTANT,

             value: -1 }]}

  p_year_1: rscalyear,

  @EndUserText.label: 'Year 2'    

  @Consumption.derivation: {lookupEntity: 'ZSAPBC_CURR_YEAR_ADD_N',                                                                                                     resultElement: 'p_year',

  binding: [{ targetParameter: 'p_offset',

             type:#CONSTANT ,

             value: 0 }] }    

  p_year_2: rscalyear    

  as select from zsapbc_flight_cube(
p_display_currency:  $parameters.p_display_currency,

  p_year_1: $parameters.p_year_1,

  p_year_2: $parameters.p_year_2 ) {

  @AnalyticsDetails.query.axis: #ROWS

  @AnalyticsDetails.query.displayHierarchy: #ON

  @Consumption.filter:

  { selectionType: #HIERARCHY_NODE,

                  multipleSelections: true,

                  mandatory: false }  

  @AnalyticsDetails.query.hierarchyInitialLevel: 3

  region,

  @AnalyticsDetails.query.axis: #COLUMNS

  seatsocc_total_year_1,   

  @AnalyticsDetails.query.axis: #COLUMNS

  seatsmax_total_year_1,

  @AnalyticsDetails.query.axis: #COLUMNS

  @AnalyticsDetails.query.decimals: 1

  @EndUserText.label: 'Seats Occ (%) Year 1'

  @AnalyticsDetails.query.formula: 'NDIV0( NODIM( seatsocc_total_year_1 ) /

                                   NODIM( seatsmax_total_year_1 ) ) *                                               hundred '

  1 as seats_occ_prc_year_1,   

  @AnalyticsDetails.query.axis: #COLUMNS

  seatsocc_total_year_2,   

  @AnalyticsDetails.query.axis: #COLUMNS

  seatsmax_total_year_2,       

  @AnalyticsDetails.query.axis: #COLUMNS

  @AnalyticsDetails.query.decimals: 1

  @EndUserText.label: 'Seats Occ (%) Year 2'

  @AnalyticsDetails.query.formula: 'NDIV0( NODIM( seatsocc_total_year_2 ) /

                                   NODIM( seatsmax_total_year_2 ) ) *                                               hundred '

  1 as seats_occ_prc_year_2,

  @AnalyticsDetails.query.axis: #COLUMNS

  zsapbc_flight_cube.payment_year_1,   

  @AnalyticsDetails.query.axis: #COLUMNS

  zsapbc_flight_cube.payment_year_2,   

  @AnalyticsDetails.query.axis: #COLUMNS

  zsapbc_flight_cube.payment_disp_curr_year_1,       

  @AnalyticsDetails.query.axis: #COLUMNS

  zsapbc_flight_cube.payment_disp_curr_year_2,

  @AnalyticsDetails.query.axis: #FREE   

  connid,         

  @AnalyticsDetails.query.axis: #FREE

  @Consumption.filter: {selectionType: #SINGLE,

                       multipleSelections: true,

                       mandatory: false }

  carrid,

  @AnalyticsDetails.query.axis: #FREE

  fldate,

  @AnalyticsDetails.query.axis: #FREE

  flmonth,

  @AnalyticsDetails.query.axis: #FREE

  flyear,

  @AnalyticsDetails.query.axis: #FREE

  currency,

  @AnalyticsDetails.query.axis: #FREE

  disp_curr,

  @AnalyticsDetails.query.axis: #FREE

  unit

}

---

 

CDS Views can be downloaded from here:

zsapbc_curr_date

zsapbc_curr_year_add_n

zsapbc_flight_query2

 

In third part of my blog I will challenge ABAP CDS Consumption View one more time. This time demonstrating time dependent dimensions / hierarchies and langauge dependent texts.