Technical Articles
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).
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
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
TABLE FUNCTION IMPLEMENTATION CLASS:
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.
@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
}
@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
}
@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
}
CDS Views can be downloaded from here:
You might also find helpful following CDS Table Function blogs:
Concatenate multiple records in a single field using ABAP CDS Table Function
Delete duplicate entries in ABAP CDS views using Table Function and SQL Window Function
Select TOP N / UP TO N rows / ORDER BY in a ABAP CDS
In fifth part of my blog I will challenge ABAP CDS Consumption View again. This time demonstrating exception aggregation using formula and count.