Analytical Query Design with CDS - Derivation Functions

[joerg_franke]

Analytical Queries based on CDS are a powerful way to model analytical data sources that can be used in a variety of user interfaces (SAP Analytics Cloud, Analysis for Office, Smart Business KPIs, Web Dynpro Grid, …). Just by reading the technical documentation it is not always easy to transform the business use case into actual coding. Read this blog post in case you are interested to get a rather use case driven access to Analytical Query Design.

(all coding samples are based on CDS Analytical Projection Views - but they should work as well for CDS View (Entities) with slight adoptions )

Contents:

• Use Case Description


• Used Syntax Elements


• Sample Query


• Runtime Preview

Use Case Description

For many business-related reports, it can be advantageous to tailor a report to the context of the user who executes the report. With derivation functions, you can derive context-specific values to restrict data in CDS queries.  Here are some examples of common derivation functions:

• Time-related functions, such as Today or Current Fiscal Period


• Responsibility-related functions, such as My Cost Centers


• Business semantic-related functions, such as Leading Ledger

To make it concrete - lets define a query that selects the following Finance Data:

• Only data of the leading ledger


• Data of the rolling year


• All revenue postings based on Semantic Tags

Used Syntax Elements

• Annotation: "@AnalyticsDetails.variable:"
  In general, a CDS parameter represents a single value which has to be determined at runtime either by user input or derivation. But in Analytics, this concept is too strict. There is the need for multiple values, for intervals, for hierarchy-nodes. In some cases the input should be optional. This can be achieved with the AnalyticsDetails.variable annotations. If these annotations are used, the ODATA.publish: true is not supported.


• Annotation: "@Consumption.derivation:"
  This annotation can be used to select values from another CDS view. This can either be a single value, interval, list or a hierarchy. The values can then be used inside the query for a global selection or a measure. In combination with the annotation "@Consumption.filter.hidden: true", this leads to a dynamic filter at runtime without user interaction.


• CASE WHEN THEN END AS
  Standard way to define restricted measures.

Sample Query

@EndUserText.label: 'Simple Derivation Query'

@AccessControl.authorizationCheck: #NOT_ALLOWED

define transient view entity ZJF_C_Derivation_Q01

  provider contract analytical_query

  with parameters



    @Consumption.hidden :true

    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'PostingDate', mandatory: true, selectionType: #INTERVAL, multipleSelections: false }

    @Consumption.derivation: {

       lookupEntity: 'F_FsclDteFuncRngeVal',

       resultElement: 'DateFunctionStartDate',

       resultElementHigh: 'DateFunctionEndDate',

       binding : [ { targetParameter : 'P_DateFunction', type: #CONSTANT, value: 'ROLLINGFISCALYEARCLOSED' },

                   { targetParameter : 'P_FiscalYearVariant', type: #CONSTANT, value: 'K4' } ] }



    P_RollingYear : fis_budat,





    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'GLAccount', mandatory: true, selectionType: #SINGLE, multipleSelections: true }

    @Consumption.hidden :true

    @Consumption.derivation: {

       lookupEntity: 'F_GLACCOUNTBYSEMANTICTAG',

       resultElement: 'GLAccount',

       binding : [ { targetParameter : 'P_GLAccountHierarchy', type: #CONSTANT, value: 'FPA1' },

                   { targetParameter : 'P_SemanticTag', type: #CONSTANT, value: 'RECO_REV' } ] }

    P_Revenue     : fis_racct,





    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'Ledger', mandatory: true, selectionType: #SINGLE, multipleSelections: false }

    @Consumption.hidden :true

    @Consumption.derivation: {

       lookupEntity: 'F_LeadingLedger',

       resultElement: 'Ledger'}

    

    P_Ledger : fins_ledger





  as projection on I_GLAccountLineItem

{



  @AnalyticsDetails.query.axis: #ROWS

  @UI.textArrangement: #TEXT_LAST

  CompanyCode,



  @AnalyticsDetails.query.axis: #FREE

  GlobalCurrency,

  

  @AnalyticsDetails.query.axis: #FREE

  FiscalYearPeriod,



  @AnalyticsDetails.query.axis: #FREE

  FiscalYearVariant,

  



  @AnalyticsDetails.query.axis: #FREE

  GLAccount,



  @AnalyticsDetails.query.axis: #COLUMNS

  @Semantics.amount.currencyCode: 'GlobalCurrency'

  @Aggregation.default: #SUM

  @EndUserText.label: 'Revenue'

  case

    when ( PostingDate      = $parameters.P_RollingYear )

     and ( GLAccount        = $parameters.P_Revenue )  then curr_to_decfloat_amount( AmountInGlobalCurrency )

  end as RevenueRollingYear





} 

  where Ledger = $parameters.P_Ledger

    and FiscalYearVariant = 'K4'

Runtime Preview

This preview is done with the Web Dynpro Data Grid that I started via the backend transaction RSRT with the technical query name "2CZJF_C_Derivation_Q01".

Data Analysis

Here you can see the initial drill-down of the sample query with Company Code in the rows.

Data Analysis

Query Information

In the query information you can see which values have been derived for the defined variables.

Query Information

 

Background regarding (Fiscal) Date Functions

Date Functions and Fiscal Date Functions are a strong concept to filter dates based on predefined Date Function Configurations. As the names indicate two different calendars are supported: Gregorian and Fiscal Calendar. Whereas the Fiscal Date Functions depend on the Fiscal Year Variant in addition. Based on the current date and the configuration any kind of single date or date interval can be calculated (e.g.: Last Day of Previous Month, Current Fiscal Quarter). With the application Manage Date Functions it is easily possible to define own date functions.

• For the Gregorian Calendar the CDS View "C_SGLGREGORIANCALDATEFUNCTION" can be used in a "@Consumption.Derivation".


• For the Fiscal Calendar you find the relevant CDS Views under the link "Derivation Functions for Finance"

Links

The following sources provide further information:

• CDS Analytical Projection Views:
  https://blogs.sap.com/2022/02/18/cds-analytical-projection-views-the-new-analytical-query-model/


• CDS Annotations: https://help.sap.com/docs/SAP_S4HANA_CLOUD/e5522a8a7b174979913c99268bc03f1a/130e02a697e14bf8b05dd667...


• Derivation Functions for Finance: https://help.sap.com/docs/SAP_S4HANA_CLOUD/0f69f8fb28ac4bf48d2b57b9637e81fa/dc8ffaaa141e4d5f87dbae05...


• Manage Date Functions: https://help.sap.com/docs/SAP_S4HANA_CLOUD/a630d57fc5004c6383e7a81efee7a8bb/6164ef91c48240d4959b63fe...