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

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: