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
Employee
Employee
2,763
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


Hierarchies are an easy and flexible way to group values. S/4HANA provides already many hierarchy models out of the box: GL Account, Cost Center, Profit Center, Company Code - to name just a few. Plus there is an easy way to maintain those hierarchies via the application Manage Global Hierarchies.
Using hierarchy nodes inside CDS Queries enable the possibility to decouple the CDS Query definition from the actual definition of the hierarchy.

Lets take the following concrete use case:

  • Show GL Accounts grouped by a GL Account Hierarchy that can be selected on the UI

  • Show all Company Codes that belong to region EMEA in one column

  • Show all Company Codes that belong to a comparison region that can be selected on the UI


As preparation work we need to create a Company Code Hierarchy with the following structure in the App Manage Global Hierarchies:

  • ZTESTCC (root node)

    • AMER

      • <some company codes>



    • EMEA

      • <some company codes>



    • APAC

      • <some company codes>






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.

    • "hierarchyBinding:"
      The hierarchy binding allows to specify the actual hierarchy. You can either set a constant value, or make it input enabled.

    • "defaultHierarchyNode:"
      The defaultHierarchyNode specifies the ID of the hierarchy node which must fit to the hierarchyBinding.



  • CASE WHEN THEN END AS
    Standard way to define restricted measures in which we can use the hierarchy node variable


Sample Query



@EndUserText.label: 'Query with hierarchy selection'
@AccessControl.authorizationCheck: #NOT_ALLOWED
define transient view entity ZJF_C_HIERARCHIES_Q01
provider contract analytical_query
with parameters

@Consumption.hidden :true
@AnalyticsDetails.variable: {
usageType: #FILTER, referenceElement: 'CompanyCode', mandatory: true, selectionType: #HIERARCHY_NODE, multipleSelections: false,
defaultHierarchyNode: { nodeType: 'HierarchyNode', node: [{ element: 'HierarchyNode', value: '0EMEA' }] },
hierarchyBinding: [{ type: #CONSTANT , value: 'HCCD/ZTESTCC' }]
}

P_CompanyCode_EMEA : fis_bukrs,

@Consumption.hidden :false
@AnalyticsDetails.variable: {
usageType: #FILTER, referenceElement: 'CompanyCode', mandatory: true, selectionType: #HIERARCHY_NODE, multipleSelections: false,
defaultHierarchyNode: { nodeType: 'HierarchyNode', node: [{ element: 'HierarchyNode', value: '0AMER' }] },
hierarchyBinding: [{ type: #CONSTANT , value: 'HCCD/ZTESTCC' }]
}
@EndUserText.label: 'Comparison Region'
P_CompanyCode_Comp : fis_bukrs,

@AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'GLAccount', mandatory: true, selectionType: #SINGLE, multipleSelections: false, defaultValue: 'FPA1'}
@Consumption.valueHelpDefinition: [{ entity: { name: 'I_GLAccountHierarchyStdVH', element: 'GLAccountHierarchy' }}]
P_GLAccountHierarchy : fis_glaccthier


as projection on I_GLAccountLineItem
{

@AnalyticsDetails.query.axis: #FREE
@UI.textArrangement: #TEXT_LAST
CompanyCode,

@AnalyticsDetails.query.axis: #FREE
GlobalCurrency,

@AnalyticsDetails.query.axis: #FREE
FiscalYearPeriod,

@AnalyticsDetails.query.axis: #FREE
FiscalYearVariant,

@UI.textArrangement: #TEXT_ONLY
@AnalyticsDetails.query: {
axis: #ROWS,
displayHierarchy: #ON,
hierarchyInitialLevel: 2,
hierarchyBinding: [ {type: #PARAMETER, value: 'P_GLAccountHierarchy' } ],
hierarchySettings: { childNodePosition: #ABOVE }
}

GLAccount,

@AnalyticsDetails.query.axis: #FREE
virtual VirtualCurrency : abap.cuky,

@AnalyticsDetails.query.axis: #COLUMNS
@Semantics.amount.currencyCode: 'VirtualCurrency'
@Aggregation.default: #SUM
@EndUserText.label: 'EMEA Region'
case
when ( CompanyCode = $parameters.P_CompanyCode_EMEA ) then curr_to_decfloat_amount( AmountInGlobalCurrency )
end as EMEAAmount,

@AnalyticsDetails.query.axis: #COLUMNS
@Semantics.amount.currencyCode: 'VirtualCurrency'
@Aggregation.default: #SUM
@EndUserText.label: 'Comparison Region'
case
when ( CompanyCode = $parameters.P_CompanyCode_Comp ) then curr_to_decfloat_amount( AmountInGlobalCurrency )
end as CompRegionAmount

}
where
Ledger = '0L'
and FiscalYearVariant = 'K4'

Runtime Preview


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

Data Analysis


In the following screenshot of the query you can see:

  • The two visible variables (the variable for EMEA Region is hidden and can not be changed during runtime)

  • The two restricted region measures defined based on the hierarchy node variables

  • GL Accounts are displayed as a hierarchy based on the selected GL Account Hierarchy



Data Analysis



Tipp: How to find the right Hierarchy ID and Hierarchy Node ID for the CDS Query definition


It is not always easy to find the correct Ids that you need to use in your CDS Query View. The easiest way is if you can switch on the display hierarchy of the related dimension in an already existing report and pic the Ids from the drilldown.

Links


The following sources provide further information:
1 Comment