Enterprise Resource Planning Blogs by SAP
Get insights and updates about cloud ERP and RISE with SAP, SAP S/4HANA and SAP S/4HANA Cloud, and more enterprise management capabilities with SAP blog posts.
cancel
Showing results for 
Search instead for 
Did you mean: 
joerg_franke
Advisor
Advisor
6,861
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:




 
2 Comments