Skip to Content

Hi!

In this blog 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.

There is a link to available CDS functions NW 7.51 documentation:

http://help-legacy.sap.com/abapdocu_751/en/abencds_language_elements.htm

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

There is a link to Windows Function documentation:

http://help-legacy.sap.com/saphelp_hanaplatform/helpdata/en/20/a353327519101495dfd0a87060a0d3/content.htm

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.

 

Summary:

So we went through a long way actually:

  • Creation of CDS based of Table Function.
  • Creation of Class/Method with SQL Script logic included using of windows functions expressions.
  • Creating of consumption Cube ABAP CDS view.
  • Creation of consumption analytical query ABAP CDS view.
  • Viewing results in RSRT, Analysis for Excel, S/4HAHA Fiori Query Browser.
  • Viewing result of KPI tile (SmartBusiness KPI Modeler App) based on the same analytical query CDS view.

Thank you for attention!

See you at my later blogs.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply