How to convert Hierarchy to Attribute in S/4HANA

Masaaki · ‎2023 Sep 22

Purpose and summary of the blog

This blog explains how to convert hierarchy to Attribute. Hierarchy is Set Hierarchy or hierarchy created with “Manage Global Hierarchies” in S/4HANA. Any hierarchies are the target, e.g. Profit Center / Profit Center Group, Company and Financial Statement Version etc.

By converting hierarchy nodes to attribute, much more flexible operation in analytical app is possible, e.g. drill-down path does not have to be predefined but can be changed flexibly.

This bring solutions to use hierarchy node in standard and custom SAP Fiori apps like Fiori Elements List Report as converted attributes can be added in OData Service.

The attributes are also available in analytical app in Web Dynpro Data Grid, SAC Live Connection and Analysis Office (but not possible to use in SAP GUI UI).

For G/L Account or Financial Statement Version, it is possible to do the same as Semantic Tag by with Attribute, not with Restricted Measure, e.g. attribute G/L Account LV1 has attribute value Sales, GP, but more complex CDS View (union views for each grouped account) has to be created as the leaf levels of grouped account, e.g. GP, OP are different in hierarchy (the other way is to create restricted measure or restricted characteristics).

I believe it would make especially Margin Analysis, FI/CO world difference, so would bring change in Management Reports, the most important value in ERP or S/4HANA. Organization Structure or the design of Profit Center Group / Cost Center Group is the key for KPI, and solution in this blog should have big impact on the design as much more flexible reporting is possible by using attributes.

How to convert:

By creating custom CDS View to convert hierarchy data in HRRP* tables to attribute and add the view to Standard VDM with Extend View (The blog). The VDMs are released VDM so it should not have impact on upgrade so aligned to Core Clean policy.

Prerequisites:

The lowest level of Profit Centers as leaves in a hierarchy must be fixed, e.g. Profit Centers as leaves must be the hierarchy level >= 4 in the sample code.

To convert Set hierarchy, it has to be replicated in case of referring the sample code in this blog. To use Set hierarchy in Web Dynpro Data Grid app, hierarchy data in SETHEADER/SETNODE/g as to be replicated to HRRP* tables with Transaction HRY_REPRELEV and HRRP_REP (SAP Help).

Another way to use attribute:

Another way to use attribute is to add fields directly to Profit Center with Custom Table (Blog), and add them with Data Source Extension of Custom Fields (Blog). The extended fields will then be available in Manage Flexible Hierarchies, where you can update the field values by editing in a spreadsheet and uploading it to the app. it would be more standard way and recommended way especially for new implementation. See Extensibility: Manage Flexible Hierarchies | SAP Help Portal and Manage your hierarchies in FIORI 3/3 | SAP Blogs

Comparison – What are possible by converted to Attribute?

	Hierarchy	Attribute
Add, remove, change the order of nodes/attributes	Not possible. Drill-down path has been predefined.	Possible *1
Filtering	By selecting nodes/leaves (single).	By selecting attribute values (multiple values can be selected)
Several versions of hierarchy	Possible	Not possible, but possible to have attributes from hierarchies instead. *2
Publish as OData Service to be used in SAP Fiori app	Not Possible	Possible.
Response time	Worse especially when having many leaves/nodes	Better in common
Authorization	By leaves only	By attributes (nodes and leaves)
Export data to external system	Relations between parents and child, and it might have to be converted to attribute in the target system	Possible to export data as attribute (easy to use)

*1, *2: By converting to attributes, navigations like below are possible. There are 2 hierarchies. A) Organization hierarchy (BD/BU/Product Group), B) Region (Region/Country/Company). As the case of “By BD/Region/Company”, by having attributes from hierarchy A) and B), it is possible to mix the nodes/attributes of hierarchy A) and B).

How to convert Hierarchy to Attribute

As mentioned above, 1) firstly create the custom CDS View to convert hierarchy info in HRRP* table to attribute and 2) add the attributes in the CDS View to standard or custom Interface (I_*) View and Consumption View (C_*).

In the following case, Profit Center Group hierarchy nodes are converted to attributes and added to standard app F0956 Journal Entry Analyzer and VDM underneath (I_GLAccountLineItemCube and C_GLLineItemsQ0001).

Source VDMs I_ProfitCenterHierarchyNode and I_ProfitCenterHierarchyNodeT whose sources are HRRP* tables, and they are released standard VDMs.

ZI_PROFITCENTERGROUP

// Prerequisite: Profit Centers as leaves must be LV>=4

@AbapCatalog.sqlViewName: 'ZIPRCTRGRP'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Profit Center Group Attributes'

@Analytics.dataCategory: #CUBE

define view ZI_PROFITCENTERGROUP

as select from I_ProfitCenterHierarchyNode as _ZLV1

left outer join I_ProfitCenterHierarchyNode as _ZLV2 on _ZLV2.ParentNode = _ZLV1.HierarchyNode

and _ZLV2.ProfitCenterHierarchy = _ZLV1.ProfitCenterHierarchy

and _ZLV2.ControllingArea = _ZLV1.ControllingArea

and _ZLV2.ValidityEndDate = _ZLV1.ValidityEndDate

left outer join I_ProfitCenterHierarchyNode as _ZLV3 on _ZLV3.ParentNode = _ZLV2.HierarchyNode

and _ZLV3.ProfitCenterHierarchy = _ZLV2.ProfitCenterHierarchy

and _ZLV3.ControllingArea = _ZLV2.ControllingArea

and _ZLV3.ValidityEndDate = _ZLV2.ValidityEndDate

left outer join I_ProfitCenterHierarchyNode as _ZLV4 on _ZLV4.ParentNode = _ZLV3.HierarchyNode

and _ZLV4.ProfitCenterHierarchy = _ZLV3.ProfitCenterHierarchy

and _ZLV4.ControllingArea = _ZLV3.ControllingArea

and _ZLV4.ValidityEndDate = _ZLV3.ValidityEndDate



  association [0..1] to I_ControllingArea            as _ControllingArea     on  $projection.ControllingArea = _ControllingArea.ControllingArea

  association [1..*] to I_ProfitCenterHierarchy      as _Hierarchy         on  $projection.ProfitCenterHierarchy = _Hierarchy.ProfitCenterHierarchy

                                                                             and $projection.ControllingArea = _Hierarchy.ControllingArea

  association [0..*] to I_ProfitCenterHierarchyNodeT as _ZTextLV3            on $projection.ControllingArea = _ZTextLV3.ControllingArea

                                                                             and $projection.ProfitCenterHierarchy = _ZTextLV3.ProfitCenterHierarchy

                                                                             and $projection.ZLV3       = _ZTextLV3.HierarchyNode

  association [0..*] to I_ProfitCenterHierarchyNodeT as _ZTextLV2            on $projection.ControllingArea = _ZTextLV2.ControllingArea

                                                                             and $projection.ProfitCenterHierarchy = _ZTextLV2.ProfitCenterHierarchy

                                                                             and $projection.ZLV2       = _ZTextLV2.HierarchyNode

  association [0..*] to I_ProfitCenterHierarchyNodeT as _ZTextLV1            on $projection.ControllingArea = _ZTextLV1.ControllingArea

                                                                             and $projection.ProfitCenterHierarchy = _ZTextLV1.ProfitCenterHierarchy

                                                                             and $projection.ZLV1       = _ZTextLV1.HierarchyNode





{

key    _ZLV1.ControllingArea as ControllingArea,

key   cast( case 

when _ZLV1.NodeType = 'L' then _ZLV1.HierarchyNodeVal

when _ZLV2.NodeType = 'L' then _ZLV2.HierarchyNodeVal

when _ZLV3.NodeType = 'L' then _ZLV3.HierarchyNodeVal

else _ZLV4.HierarchyNodeVal

end as profitcenter)

 as ProfitCenter,

key _ZLV1.ProfitCenterHierarchy,

key _ZLV1.HierarchyVersion,

key   _ZLV1.ValidityEndDate,

   _ZLV1.ValidityStartDate,

    @ObjectModel.text.association: '_ZTextLV1'

    @EndUserText.label: 'LV1'

    cast (case when _ZLV1.NodeType = 'N' then _ZLV1.HierarchyNode end as hrynode) as ZLV1,

    @ObjectModel.text.association: '_ZTextLV2'

    @EndUserText.label: 'LV2'

    cast (case when _ZLV2.NodeType = 'N' then _ZLV2.HierarchyNode end as hrynode) as ZLV2,

    @ObjectModel.text.association: '_ZTextLV3'

    @EndUserText.label: 'LV3'

    cast (case when _ZLV3.NodeType = 'N' then _ZLV3.HierarchyNode end as hrynode) as ZLV3,

    _ZTextLV3,

    _ZTextLV2,

    _ZTextLV1,

    _ControllingArea,

    _Hierarchy

}

where 

_ZLV1.HierarchyNodeLevel = '000001'

Extend View

For Interface View: I_GLAccountLineItemCube

@AbapCatalog.sqlViewAppendName: 'ZXCLALITM6'

@EndUserText.label: ' I_GLAccountLineItemCube ext'

extend view I_GLAccountLineItemCube with ZX_GLACCOUNTLINEITEMCUBE

association [0..1] to ZI_PROFITCENTERGROUP as _ProfitCenterGroup

on $projection.controllingarea = _ProfitCenterGroup.ControllingArea

and $projection.profitcenter = _ProfitCenterGroup.ProfitCenter

{

@ObjectModel.foreignKey.association: '_Hierarchy'

_ProfitCenterGroup.ProfitCenterHierarchy,

_ProfitCenterGroup.HierarchyVersion,

@Consumption.hidden: true

@ObjectModel.foreignKey.association: '_ZControllingArea'

_ProfitCenterGroup.ControllingArea as ZControllingArea,

@EndUserText.label: 'LV1'

@ObjectModel.text.association: '_ZTextLV1'

_ProfitCenterGroup.ZLV1,

 @EndUserText.label: 'LV2'

@ObjectModel.text.association: '_ZTextLV2'

_ProfitCenterGroup.ZLV2,

@EndUserText.label: 'LV3'

@ObjectModel.text.association: '_ZTextLV3'

_ProfitCenterGroup.ZLV3,

_ProfitCenterGroup._ZTextLV1,

_ProfitCenterGroup._ZTextLV2,

_ProfitCenterGroup._ZTextLV3,

_ProfitCenterGroup._Hierarchy,

_ProfitCenterGroup._ControllingArea as _ZControllingArea

}

it would take time to activate. To avoid it, add below, but it is not supported officially.

Consumption View View: C_GLLineItemsQ0001

ProfitCenterHierarchy and HierarchyVersion must be set to be mandatory filter with single value.

@AbapCatalog.sqlViewAppendName: 'ZXGLLITM01'

@EndUserText.label: 'C_GLLINEITEMSQ0001 ext'

extend view C_GLLineItemsQ0001 with ZX_GLLINEITEMSQ0001 {

    @Consumption.filter: {selectionType: #SINGLE, multipleSelections: false, mandatory: true}

    ProfitCenterHierarchy,

    @Consumption.filter: {selectionType: #SINGLE, multipleSelections: false, mandatory: true}

    HierarchyVersion,

    @AnalyticsDetails.query.display: #TEXT

    @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false}

    ZLV1,

    @AnalyticsDetails.query.display: #TEXT

    @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false}

    ZLV2,

    @AnalyticsDetails.query.display: #TEXT

    @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false}

    ZLV3

}

Profit Center Group in Transaction KCH2

Run the query C_GLLineItemsQ0001 with Transaction RSRT

Before converting hierarchy to attributes:

Select hierarchy for Profit Center. Then, Hierarchy node can be filtered in the context menu or navigation bar (Keep Filter Value).

Use Attributes

Hierarchy and version has to be selected in Filter bar. LV1~3 can be filtered as well and it is possible to filter with multiple values.

Let us bring change in key reports, the design of organization structure, FI/CO world together!

How to convert Hierarchy to Attribute in S/4HANA

Purpose and summary of the blog

Comparison – What are possible by converted to Attribute?

How to convert Hierarchy to Attribute

Useful documents on SCN

Evolution of ABAP

Analytics in S/4HANA - real shape of embedded analytics and beyond embedded analytics