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
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Analytical Query Design with CDS - Restricted Key Figures - Hierarchy Nodes

joerg_franke
Employee
Employee
‎04-06-2023 12:41 PM
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
Popular Blog Posts