Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
uladzislau_pralat
Contributor
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.

 

BASIC Views:

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


}








 

CONSUMPTION View:

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


}








 

CDS Views can be downloaded from here:

zsapbc_curr_date

zsapbc_curr_year_add_n

zsapbc_flight_query2

 

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.
2 Comments