CDS View: Hierarchy node sign reversal

[LinaRaut]

This blog is about how to use @Semantics.signReversal in CDS Views to revert the sign of a measure in dependence of the settings of a hierarchy node. Analytic query offers the functionality to reverse a sign after aggregation based on a hierarchy node attribute.  

This functionality shall be made accessible from a CDS Query, for this it is necessary to determine the hierarchy node attribute which stores the sign reverse indicator.  

To make use of the sign reversal feature in an analytical query, the following field needs to be added to the related hierarchy view: 

@Semantics.signReversalIndicator 

      signref as signReversalFlag, 

Example: Hierarchy view 

@AbapCatalog.sqlViewName: 'ZOQ_CONNIDHIER'
@EndUserText.label: 'Connection Hierarchy View'
@ObjectModel.dataCategory: #HIERARCHY
@Hierarchy.parentChild : {
  name : 'ConnectionHierarchy',
  label : 'Connection',
  siblingsOrder: [{ by: 'seqno' , direction: 'ASC' }] ,
  recurseBy : '_parent',
  directory: '_dir'
}

define view ZOQ_CONNECTION_HIERARCHY
  as select from zoq_connid_h

  association [0..1] to ZOQ_CONNECTION_HIERARCHY     as _parent     on  $projection.parentid = _parent.nodeid

  association [1]    to ZOQ_CONNECTION_HIERARCHY_DIR as _dir        on  $projection.hieid = _dir.hieid

  association [1]    to ZOQ_CONNECTION_NODENAME as _node       on       $projection.hieid   = _node.hieid
                                                                    and $projection.nodename = _node.nodeName
                                                                    and $projection.carrid   = ''
                                                                    and $projection.connid   = '0000'

  association [1]    to ZOQ_AIRLINE                  as _airline    on  $projection.carrid = _airline.carrid
                                                                    and $projection.connid = '0000'

  association [1]    to ZOQ_CONNECTION               as _connection on  $projection.carrid = _connection.carrid
                                                                    and $projection.connid = _connection.connid

{
      @Consumption.filter: { mandatory : true, selectionType : #SINGLE, multipleSelections : false }
      @ObjectModel.foreignKey.association: '_dir'
  key hieid,
  key nodeid,
      parentid,

      seqno,

      @ObjectModel.foreignKey.association: '_airline'
      carrid,
      @ObjectModel.foreignKey.association: '_connection'
      connid,
      @ObjectModel.foreignKey.association: '_node'
      nodename,
      
      @Semantics.signReversalIndicator
      signref as signReversalFlag,

      _parent,
      _dir,
      _node,
      _airline,
      _connection

}   

Preview: 

In CDS views with "Analytics.query : true" or define as "transient view entity", function "HRY_NODE_SIGN_VALUE(elemRef)" is used in a formula (AnalyticsDetails.query.formula). This function works in the following way, At first the path from the element to its hierarchy view is evaluated. In the hierarchy view there should exist exactly one field with semantics signReversal. For each hierarchy node for which the value of the sign reversal element returns space, the function HRY_NODE_SIGN_VALUE will return +1. If the value is not initial, the function returns -1. With this,

for example- the formula $projection.paymentsum * hry_node_sign_value( dimension => $projection.connid) as paymentsumSignRef

will return the key figure paymentsum for all nodes/leaves in the hierarchy with initial sign reversal indicator and It will return "- paymentsum" for all nodes/leaves with not initial sign reversal indicator. 

If there is no field with semantics signReversal in the hierarchy view, the function HRY_NODE_SIGN_VALUE always returns +1. 

Example: Analytical query 

@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'SIGN REVERSAL'
@Metadata.ignorePropagatedAnnotations: true
define transient view entity ZLR_SIGN_REV_FLIGHT_1 
   provider contract analytical_query 
     with parameters
     @EndUserText.label: 'Hierarchy'
     @Consumption.defaultValue: 'CNTRY' 
     p_hienm : char5 
     
  as projection on ZOQ_FLIGHT
    
 {   
    @AnalyticsDetails.query.axis: #ROWS
    @AnalyticsDetails.query: {
      displayHierarchy: #ON,
      hierarchyBinding: [{ type : #PARAMETER , value : 'p_hienm' }]
    }  
    connid,
    @AnalyticsDetails.query.axis: #FREE
    currency,
    
    @Semantics.amount.currencyCode: 'CURRENCY'
    @Aggregation.default: #SUM
    @AnalyticsDetails.query.axis: #COLUMNS
    curr_to_decfloat_amount( paymentsum ) as paymentsum,
    
    @AnalyticsDetails.query.axis: #COLUMNS
    @EndUserText.label: 'HierSignRev'
    @Aggregation.default: #FORMULA
    $projection.paymentsum * hry_node_sign_value( dimension => $projection.connid) as paymentsumSignRef
  
}

Result:

In this example for Hierarchy ZOQ_CONNECTION_HIERARCHY (which is defined in the ZOQ_FLIGHT), signReversalFlag is set for Carried id 'A', 'JL', 'UA'. Therefore, in result of analytical query HierSignRev field is showing the –ve result of the booking total. 

SAP Delivered Hierarchies: 

There are few SAP delivered hierarchies which have this feature.

Following fields name: FinancialStatementItem, CnsldtnFinancialStatementItem, FinancialStatementLeafItem, GLAccount, etc. 

Can have this feature because they have foreign key association with dimension view which has hierarchy association to hierarchy view supporting this feature. 

For example: Field with name ‘FinancialStatementItem’ typically have a foreign key association to dimension view I_CnsldtnFinStmntItem and corresponding hierarchy view I_FinStmntItmHier will support this feature. 

Constraints: 

Only one element in a view of data category HIERARCHY can be marked with semantics signReversal. The data type of the element should be CHAR of length 1. Allowed values are X or space. 

Related Note: 

1385580 - How does a formula variable with a replacement path work? 

Availability: 

This feature is available from S/4HANA 1909 (ABAP Platform 7.54).