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 [email protected]: '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.