Wednesday, 30 May 2018

ABAP CDS Consumption View Features Ultimate Test: Part 4

In first part, second part and third part of my blog I put ABAP CDS Consumption View to test and fulfilled some of most typical BW Query requirements. In fourth part of my blog I will challenge ABAP CDS Consumption View again. This time demonstrating percentage share calculation (similar to BW Query %CT function) and filtering key figures structure.

I created table function, table function implementation class, also created FACT, 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 Consumption Views capabilities.

As you will see from screenshot below I calculate payment (dc) percentage share  for year 1 and year 2 (last two columns).

SAP ABAP Tutorials and Materials, SAP ABAP Learning, SAP ABAP CDS, SAP ABAP Develoment

Percentage share is calculated dividing payment (dc) by payment (dc) total for respective year. There are two columns -payment (dc) total year 1 and payment (dc) total year 2 that are hidden by default and displayed on screenshot below

SAP ABAP Tutorials and Materials, SAP ABAP Learning, SAP ABAP CDS, SAP ABAP Develoment

TABLE FUNCTION View:


ZSAPBC_FLIGHT_TABL_FUNC

zsapbc_flight_tabl_func

@EndUserText.label: ‘Flight Table Function’

define table function zsapbc_flight_tabl_func

with parameters

 p_disp_currency: s_currcode

returns

{

mandt: abap.clnt;

carrid: s_carr_id;

connid: s_conn_id;

fldate: s_date;

@Semantics.currencyCode: true 

currency: s_currcode;

@Semantics.currencyCode: true 

disp_currency: s_currcode;

@Semantics.amount.currencyCode: ‘currency’   

paymentsum: s_sum;

@Semantics.amount.currencyCode: ‘disp_currency‘ 

payment_disp_curr: s_sum;

@Semantics.amount.currencyCode: ‘disp_currency‘

payment_disp_curr_total: s_sum;

flyear: abap.char(4);

}

implemented by method

  zcl_sapbc_flight_tabl_func=>function;

TABLE FUNCTION IMPLEMENTATION CLASS:


ZCL_SAPBC_FLIGHT_TABL_FUNC

class zcl_sapbc_flight_tabl_func definition

public

final

create public .

public section.

interfaces if_amdp_marker_hdb.

class-methods function for table function zsapbc_flight_tabl_func.

protected section.

private section.

endclass.

class zcl_sapbc_flight_tabl_func implementation.


method function by database function

                   for hdb language sqlscript

                   options read-only

                   using sflight.


it_data =

      select mandt, carrid, connid, fldate,

             currency, :p_disp_currency as disp_currency,

             paymentsum,

             CONVERT_CURRENCY(amount=>paymentsum,

               “SOURCE_UNIT” =>currency,

               “SCHEMA” => ‘SAPA4H’,

               “CONVERSION_TYPE” => ‘M’,

               “TARGET_UNIT” => :p_disp_currency,

               “REFERENCE_DATE” =>fldate,

               “ERROR_HANDLING”=>‘set to null’,

               “CLIENT” => ‘000’) as payment_disp_curr,

               substr( fldate, 1, 4) as flyear

from sflight;



return

      select mandt, carrid, connid, fldate, currency, disp_currency,

             paymentsum , payment_disp_curr,

             sum( payment_disp_curr  ) over ( partition by flyear ) as                     payment_disp_curr_total,

             flyear

      from :it_data;

endmethod.

endclass.

FACT View:


ZSAPBC_FLIGHT_FACT4

@AbapCatalog.sqlViewName: ‘zsflight_fact4’

@AbapCatalog.compiler.compareFilter: true

@Analytics.dataCategory: #FACT

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Flight Fact 4’

define view
zsapbc_flight_fact4 with parameters

    @Consumption.defaultValue: ‘CAD’

    p_display_currency : s_currcode

    as select from sflight inner join zsapbc_carr as scarr

                                 on sflight.carrid = scarr.carrid

                          inner join zsapbc_flight_tabl_func( p_disp_currency:
                                  $parameters.p_display_currency ) as tabl_func

                                   on sflight.carrid = tabl_func.carrid

                                  and sflight.connid = tabl_func.connid

                                  and sflight.fldate = tabl_func.fldate  {

key sflight.carrid,

key sflight.connid,

key sflight.fldate,

scarr.region, 

cast(substring(sflight.fldate,1,6) as abap.numc( 6 )) as flmonth,

cast(substring(sflight.fldate,1,4) as abap.numc( 4 )) as flyear, 

sflight.currency,

sflight.paymentsum as payment,

$parameters.p_display_currency as disp_curr,

currency_conversion(

    amount             => sflight.paymentsum,

    source_currency    => sflight.currency,

    target_currency    => $parameters.p_display_currency,

    exchange_rate_date => sflight.fldate,

    exchange_rate_type => ‘M’,

    error_handling     => ‘SET_TO_NULL’ 

) as payment_disp_curr,

tabl_func.payment_disp_curr_total, 

cast(‘EA’ as abap.unit(3) ) as unit,

sflight.seatsmax,

sflight.seatsmax_b,

sflight.seatsmax_f,

seatsmax + seatsmax_b + seatsmax_f as seatsmax_total,

sflight.seatsocc,

sflight.seatsocc_b,

sflight.seatsocc_f,

seatsocc + seatsocc_b + seatsocc_f as seatsocc_total   

}

Cube View:


ZSAPBC_FLIGHT_CUBE4

@AbapCatalog.sqlViewName: ‘ZSFLIGHT_CUBE4’

@AbapCatalog.compiler.compareFilter: true

@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }

@AccessControl.authorizationCheck:#CHECK

@EndUserText.label: ‘Flight Cube 4’

define view zsapbc_flight_cube4 with parameters

p_display_currency : s_currcode,

p_year_1: rscalyear,

p_year_2: rscalyear    

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

    association [1..1] to zsapbc_carr_dimension as _carr

                      on $projection.carrid  = flight.carrid

    association [0..*] to zsapbc_region_dimension3 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,

@Semantics.quantity.unitOfMeasure: ‘Currency’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Booking (bc) Year 1’

case when flyear = :p_year_1 then payment

else 0

end as payment_year_1,

@Semantics.quantity.unitOfMeasure: ‘Currency’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Booking (bc) Year 2’

case when flyear = :p_year_2 then payment

else 0

end as payment_year_2, 

@EndUserText.label: ‘Display Currency’ 

$parameters.p_display_currency as disp_curr,

@Semantics.amount.currencyCode: ‘disp_curr’

@EndUserText.label: ‘Booking (dc)’

@DefaultAggregation: #SUM

flight.payment_disp_curr,

@Semantics.quantity.unitOfMeasure: ‘disp_curr’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Booking (dc) Year 1’

case when flyear = :p_year_1 then payment_disp_curr

else 0

end as payment_disp_curr_year_1,

@Semantics.quantity.unitOfMeasure: ‘disp_curr’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Booking (dc) Year 2’

case when flyear = :p_year_2 then payment_disp_curr

else 0

end as payment_disp_curr_year_2,

@Semantics.quantity.unitOfMeasure: ‘disp_curr’ 

@DefaultAggregation: #MAX

@EndUserText.label: ‘Booking (dc) Ttl Year 1’

case when flyear = :p_year_1 then payment_disp_curr_total

else 0

end as payment_disp_curr_total_year_1, 

@Semantics.quantity.unitOfMeasure: ‘disp_curr’ 

@DefaultAggregation: #MAX

@EndUserText.label: ‘Booking (dc) Ttl Year 2’

case when flyear = :p_year_2 then payment_disp_curr_total

else 0

end as payment_disp_curr_total_year_2,

@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 Max Year 1’

case when flyear = :p_year_1 then seatsmax_total

else 0

end as seatsmax_total_year_1,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Seats Max Year 2’

case when flyear = :p_year_2 then seatsmax_total

else 0

end as seatsmax_total_year_2,

@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’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Seats Occ Year 1’

case when flyear = :p_year_1 then seatsocc_total

else 0

end as seatsocc_total_year_1,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM

@EndUserText.label: ‘Seats Occ Year 2’

case when flyear = :p_year_2 then seatsocc_total

else 0

end as seatsocc_total_year_2,

@Semantics.quantity.unitOfMeasure: ‘unit_percent’

@DefaultAggregation: #MAX

@EndUserText.label: ‘100%’

100 as hundred 

}

Consumption View:


ZSAPBC_FLIGHT_QUERY4

@AbapCatalog.sqlViewName: ‘ZFLIGHT_QUERY4’

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck:#CHECK

@VDM.viewType: #CONSUMPTION

@Analytics.query: true

@EndUserText.label: ‘Flight Query 4’

define view zsapbc_flight_query4 with parameters

     @Consumption.hidden: false

     @Environment.systemField: #SYSTEM_DATE

     @EndUserText.label: ‘Key Date’

     p_keydate: abap.dats,

     @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_cube4(

       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.display: #TEXT

    @AnalyticsDetails.query.displayHierarchy: #ON

    @Consumption.filter: { selectionType: #HIERARCHY_NODE,

                          multipleSelections: true,

                          mandatory: false }  

    @AnalyticsDetails.query.hierarchyInitialLevel: 3

    zsapbc_flight_cube4._region[1:datefrom >= $parameters.p_keydate and

                                  dateto <= $parameters.p_keydate].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

    payment_year_1,   

    @AnalyticsDetails.query.axis: #COLUMNS

    payment_year_2,   

    @AnalyticsDetails.query.axis: #COLUMNS

    payment_disp_curr_year_1,       

    @AnalyticsDetails.query.axis: #COLUMNS

    payment_disp_curr_year_2,

    @AnalyticsDetails.query.axis: #COLUMNS

    @AnalyticsDetails.query.hidden

    payment_disp_curr_total_year_1,

    @AnalyticsDetails.query.axis: #COLUMNS

    @AnalyticsDetails.query.hidden

    payment_disp_curr_total_year_2,

    @AnalyticsDetails.query.axis: #COLUMNS

    @AnalyticsDetails.query.decimals: 1

    @EndUserText.label: ‘(%) Payment (dc) Year 1’

    @AnalyticsDetails.query.formula:

      ‘NDIV0( NODIM( payment_disp_curr_year_1 ) /                                       NODIM( payment_disp_curr_total_year_1 ) ) * hundred ‘

    1 as payment_dc_ttl_prc_year_1, 

    @AnalyticsDetails.query.axis: #COLUMNS

    @AnalyticsDetails.query.decimals: 1

    @EndUserText.label: ‘(%) Payment (dc) Year 2’

    @AnalyticsDetails.query.formula:

      ‘NDIV0( NODIM( payment_disp_curr_year_2 ) /

       NODIM( payment_disp_curr_total_year_2 ) ) * hundred ‘

    1 as payment_dc_ttl_prc_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

}

3 comments:


  1. Thanks for sharing great post !!
    This information you provided in the blog that is superb.It is amazing. Keep continue.
    bmc software vendors

    ReplyDelete

  2. Thank you sharing the info very well explained.
    SAP Training in Delhi

    ReplyDelete
  3. Hi, thanks for sharing the nice information. For more information please visit our website.
    SAP S4 HANA Training In AMeerpet

    ReplyDelete