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 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.
@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’
@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
}
@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:
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.
Hi Uladzislau Pralat!
very informative! this helped immensely for my requirement. Question though, do you know if its possible to use @consumption.derivation to pass 2 dates to #INTERNAL or #RANGE selection type? As of now, I am only able to pass to multiple values of #SINGLE parameters. Hoping for your insight. Thanks!
Cheers!
Rowie
Hi Rowie,
you will need to parameters to model a range.
Regards, Uladzislau