ABAP CDS Consumption View Features Ultimate Test: Part 2

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 dynamically setting variable values (similar to BW OLAP Variable user-exit functionality).

I extend zsapbc_flight_query ABAP CDS Consumption view to derive previous and current years dynamicallly using derivation annotation.

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.

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

}

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

}