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).
- When creating or updating the hierarchy with Tcd KCH1/2, Run HRY_REPRELEV and HRRP_REP to replicate data. Then new hierarchy is available or the hierarchy is updated.
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!
- SAP Managed Tags:
6 Comments
