How to use SQL window functions in ABAP CDS views

I would like to consider a case of using Windows Fuctions in ABAP CDS views based on data in S/4HANA system.

I also take case of some features like:

• Main CDS view syntax and semantics
• Parameters in CDS views
• Consumption options of CDS views

Introduction

Currently SQL functions like LEAD, LAG, NTILE and other are not supported  in CDS views.

A roundabout solution is to use a CDS view based on Table Functions where you could use all power of SQL Script language.

My demo example is based on SFLIGHT data model, that is commonly used in ABAP courses and always available in ERP and S/4HANA system. ABAP CDS Views could be created in ABAP perspective of HANA studio.

1.Creating CDS view (Data definition) in our package with template:

Define return structure and name of class and method with logic.

@EndUserText.label: 'CDS with TF'

define table function Z05_Cds_Tf

with parameters 

    @Environment.systemField: #CLIENT 

    p_clnt   :abap.clnt,

    p_carrid :s_carr_id,

    p_currency : s_currcode

returns {

    client   :s_mandt; 

    carrid: s_carr_id;

    carrname :s_carrname; 

    connid   :s_conn_id;

    fldate : s_date;

    flmonth: /BI0/OICALMONTH; 

    paymentsum: s_sum;

    currency: s_currcode;

    paymentsumnew: s_sum;

  } 

  implemented by method 

    Z05_CL_DEMO_CDS=>GET_DATA_Z05_CDS_TF; 

        

2. Create class/method defined in definition of table function.

In my example I use window function to calculate running total by amount fields. Also you could use all other functions like LEAD, LAG, etc.

Note that:

• You could include one select in another, in example I need to calculate calendar month for flight date first and use it in subsequent select in partition by statement.
• I am using tables sflight and scarr, but it is also possible to use ABAP views generated by standard or customer CDS view as an entry point for calculation.
• CDS view with table function could not be consumed directly.
• Semantics @VDM.viewType: #CONSUMPTION is forbidden there.

class Z05_CL_DEMO_CDS definition

  public

  final

  create public .

public section.

 "Include interface

 INTERFACES if_amdp_marker_hdb.

 CLASS-METHODS GET_DATA_Z05_CDS_TF

                  FOR TABLE FUNCTION Z05_Cds_Tf.

protected section.

private section.

ENDCLASS.

CLASS Z05_CL_DEMO_CDS IMPLEMENTATION.

METHOD GET_DATA_Z05_CDS_TF

         BY DATABASE FUNCTION FOR HDB

         LANGUAGE SQLSCRIPT

         OPTIONS READ-ONLY

         USING sflight scarr.

RETURN    SELECT  client,

                  carrid,

                  carrname,

                  connid,

                  fldate,

                  flmonth,

                  paymentsum,

                  currency,

                  sum(paymentsum) over (partition by carrid, connid,flmonth

                                        order by carrid, connid, fldate) as paymentsumnew

    FROM

    (

    SELECT sf.mandt as client,

                  sf.carrid,

                  sc.carrname,

                  sf.connid,

                  sf.fldate,

                  left(sf.fldate,6) as flmonth,

                  sf.paymentsum,

                  sf.currency

                  FROM sflight AS sf

                    INNER JOIN scarr AS sc ON sf.mandt = sc.mandt AND

                                              sf.carrid = sc.carrid

                    WHERE sf.mandt = :p_clnt and

                          sf.carrid = :p_carrid and

                          sf.currency = :p_currency

             )

     order by

          carrid,

          connid,

          fldate;

  ENDMETHOD.

ENDCLASS.

3. Create a consumption CDS view.

Note:

• I am using CUBE type for creating a analytical query later.
• Look at how parameters transferred between CDS views. They must be filled and unfortunately could not be optional now.
• You could include another join or associations if needed at this step. I include 2 assosiations to demonstrate a syntax. Fields from CDS views Z00_I_Airline and Z00_I_FlightConnection will be available in subsequent analytical query CDS views.
• Look at semantics of linking code field (carrid) and description field (carrname)
• Look at semantics of describing amount with currency fields.
• This CDS of CUBE type is generating transient provider. Which is visible for Analysis for Excel and BEx Query Designer for example.

@AbapCatalog.sqlViewName: 'Z05_CCDS'

@AbapCatalog.compiler.compareFilter: true

@VDM.viewType: #CONSUMPTION

@Analytics.dataCategory: #CUBE

@Analytics.dataExtraction.enabled: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Demo consume CDS'

define view Z05_C_Cds

with parameters 

    p_clnt   :abap.clnt,

    p_carrid :s_carr_id,

    p_currency : s_currcode

as select from z05_cds_tf (p_clnt:$parameters.p_clnt,p_carrid:$parameters.p_carrid,p_currency:$parameters.p_currency) 

  association [0..1] to Z00_I_Airline as _Airline             

      on $projection.carrid = _Airline.Airline

  association [0..1] to Z00_I_FlightConnection as _FlightConnection       

    on $projection.carrid = _FlightConnection.Airline and               

       $projection.connid = _FlightConnection.FlightConnection    

{

    @ObjectModel.text.element:  [ 'carrname' ] 

    @ObjectModel.foreignKey.association: '_Airline' 

    key z05_cds_tf.carrid,

     @ObjectModel.foreignKey.association: '_FlightConnection'  

    key z05_cds_tf.connid, 

    key z05_cds_tf.fldate, 

    

    @Semantics.text: true

    z05_cds_tf.carrname, 

    z05_cds_tf.flmonth,

    @Semantics.amount.currencyCode: 'currency'

    @DefaultAggregation: #SUM

    Z05_Cds_Tf.paymentsum, 

    @Semantics.amount.currencyCode: 'currency'

    @DefaultAggregation: #SUM

    Z05_Cds_Tf.paymentsumnew, 

    @Semantics.currencyCode: true  

    z05_cds_tf.currency,

    

     /* Associations */

    _Airline,

    _FlightConnection

    

}

4. Create a analytical query CDS view:

Note:

• OData service will be using for opening data of this query in Fiori Query Browser and Smart Business KPI Tool.
• Look at semantics definition of rows, columns and free characteristics of the query. Variables are also supported, but I didn’t include in example.
• Look at exception aggregation semantics, that is useful, when flight date is excluded fro drill-down by user. It works only for formulas, that’s why i define it like this.
• Look at totals semantics example.
• Last field show that fields from association CDS could be included is needed.
• Look at renaming of amount field with annotation @EndUserText. I need it to distinguish between my new running total field and standard amount field. This is also usefull think, cause this label could be translated at different languages (by SE63 t-code).

@AbapCatalog.sqlViewName: 'Z05_CCDSQ'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'CDS Demo Query'

@VDM.viewType: #CONSUMPTION

@Analytics.query: true                                          

@OData.publish: true  

define view Z05_C_Cds_Query 

with parameters 

    --@Consumption.defaultValue: '400'

    @Environment.systemField: #CLIENT

    p_clnt   :abap.clnt,

    @Consumption.defaultValue: 'AA'

    p_carrid :s_carr_id,

    @Consumption.defaultValue: 'USD'

    p_currency : s_currcode

    as select from Z05_C_Cds (p_clnt:$parameters.p_clnt,

                              p_carrid:$parameters.p_carrid,

                              p_currency:$parameters.p_currency) {

              @AnalyticsDetails.query.axis: #ROWS 

              @ObjectModel.text.element:  [ 'AirLineName' ] 

              Z05_C_Cds.carrid, 

              @AnalyticsDetails.query.axis: #ROWS 

              Z05_C_Cds.connid,

              @AnalyticsDetails.query.axis: #ROWS 

              Z05_C_Cds.flmonth, 

              @AnalyticsDetails.query.axis: #ROWS

              @AnalyticsDetails.query.totals: #SHOW

              Z05_C_Cds.fldate, 

              

              @AnalyticsDetails.query.axis: #COLUMNS

              Z05_C_Cds.paymentsum, 

              

              @AnalyticsDetails.query.axis: #COLUMNS

              @EndUserText.label: 'Booking Total New'

              @DefaultAggregation: #FORMULA

              @AnalyticsDetails: { exceptionAggregationSteps: 

             [{exceptionAggregationBehavior: #MAX, 

             exceptionAggregationElements: ['carrid','connid','fldate']}]}  

              Z05_C_Cds.paymentsumnew + 0 as paymentsumnew, 

              

              @AnalyticsDetails.query.axis: #COLUMNS

              Z05_C_Cds.currency,

              

              @Semantics.text: true

              Z05_C_Cds._Airline.AirlineName as AirLineName

    

}

5. Consume query in RSRT:

Note:

• Booking total New is our running total key figure. Remember that it was calculated in CDS view with table function:

sum(paymentsum) over (partition by carrid, connid,flmonth

                                        order by carrid, connid, fldate) as paymentsumnew

• Airline and Airline Name are like BW characterictic key with text, because of correct semantic definition in previous point (point 4).
• Look at query name 2CZ05_CDSQ. 2С + sql view name from point 4.

If we delete flight date from drill-down we will see correct aggregation in Booking Total New. For this is become true we use exception aggregation in point 4.

Important:  You could say that it is possible to create things like this BEx query by Query Designer.

In most cases the answer is “Yes”, but:

• BEx Query Designer could not be used at all with S/4HANA installation, and in S/4HANA Cloud scenarious.
• We couldn’t consume the results very easy and simple by S/4HANA Fiori Interface (later will be shown)

6. Consumption in Analysis for Excel is similar:

Note:

• Association CDS view fields are shown as attributes which is important and usefull.
• At the variables screen (not shown) parameters we filed by default values we define earlier (point 4).

7. Consumption in S/4HANA Fiori interface Query Browser.

Note: 

• First of all needed basic setting of configuration back/front servers are done.
• OData service for Analytical Query is active. For activation go to (/IWFND/MAINT_SERVICE) at Front Server.

Open a Query Browser App and find our Query Z05_ and go to open in Design Studio. You could create a tile from this.

8. Consumption in S/4HANA Smart Business KPI Modeler.

You could create a KPI, evaluation, tile and drill-downs. I’m not going into details, because there are already good blogs on this thema. Only one thing parameters of this CDS view must be defined once more time, during creation of evaluation.

The Tile is:

After clicking it (just simple table as example) there could by bars, lines and so on.