Sunday 22 April 2018

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.

SAP ABAP Tutorials and Materials, SAP ABAP Certifications, SAP ABAP Guides

SAP ABAP Tutorials and Materials, SAP ABAP Certifications, SAP ABAP Guides

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

}

No comments:

Post a Comment