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 )

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%20Analysis

Data Analysis

Query Information

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

Query%20Information

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”

Contents:

Use Case Description

Used Syntax Elements

Sample Query

Runtime Preview

Data Analysis

Query Information

Background regarding (Fiscal) Date Functions

Links

Sara Sampaio

Company

Explore More