Custom hierarchy extension in SAP Fiori app based on CDS Query(step-by-step)
This blog will show you how to create custom hierarchy using CDS view and integrate it into CDS Query based Fiori application.
Prerequisite:
You have understanding of how to extend analytical query based Fiori app by adding custom field to analytical query and cube. If not, find out how by following my blog at Extending SAP Fiori app based on CDS Query.
The Setup
This blog will demonstrate how to create profit center hierarchy using CDS view and integrate it to custom CDS cube and CDS Query. By the end of this blog, you will be able to show profit center as filter and dimension in the Fiori analytical report.
Data modeling
Creating hierarchy view that can be used for CDS Query requires to follow certain data modeling framework. The green part of the image is minimum views required to create hierarchy in CDS views. Hierarchy Directory contains hierarchy registry information such as hierarchy ID, validity date and hierarchy type. Hierarchy Node contains the hierarchy node information such as which node belongs to which parent node and which level in the structure it belongs to. Both Hierarchy Directory and Node view will create association with their text information. This is so that user will see text of each node rather than just node ID in the Fiori application. Finally, Master data view is the view that contains the master data of the entity. In our case, master data will be profit center so it will this view will fetch data from CEPC.
Define profit center hierarchy
Go to Fiori app Manage Global Hierarchies and create profit center hierarchy. Define node structures, assign profit center under nodes and activate the hierarchy.
Hierarchy directory views
Create hierarchy directory text views. This view will use HRRP_DIRECTORYT as datasource. After activating your profit center hierarchy, you can view this table content by specifying Hierarchy Type(HRYTYPE) as ‘0106’(Profit Center) and you will see your hierarchy.
Now having checked this, let’s create hierarchy directory text views in ADT(ABAP Development Tool). As profit center hierarchy can be assigned to different controlling area, we must add hryclas(controlling area) as key and associate I_ControllingArea. As you can see in the where clause, hierarchy type is restricted with 0106(profit center).
@EndUserText.label: 'Profit Center Hierarchy - Text'
@ObjectModel.representativeKey: 'ProfitCenterHierarchy'
@Analytics: { dataExtraction.enabled: true }
@ObjectModel.dataCategory: #TEXT
@VDM.viewType: #BASIC
@ObjectModel.modelingPattern: #LANGUAGE_DEPENDENT_TEXT
@ObjectModel.usageType.serviceQuality: #A
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MASTER
define view entity YI_ProfitCenterHierarchyText
as select from hrrp_directoryt
association [0..1] to I_ControllingArea as _ControllingAreaText on $projection.ControllingArea = _ControllingAreaText.ControllingArea
association [0..1] to I_ControllingArea as _ControllingArea on $projection.ControllingArea = _ControllingArea.ControllingArea
association [0..1] to I_Language as _Language on $projection.Language = _Language.Language
{
@Consumption.valueHelpDefinition: [
{ entity: { name: 'I_ControllingArea',
element: 'ControllingArea' }
}]
@ObjectModel.text.association: '_ControllingAreaText'
@ObjectModel.foreignKey.association: '_ControllingArea'
key hrrp_directoryt.hryclas as ControllingArea,
key cast(hrrp_directoryt.hryid as fis_hryid_prctr ) as ProfitCenterHierarchy,
@Semantics.businessDate.to: true
key hrrp_directoryt.hryvalto as ValidityEndDate,
@Semantics.language
key hrrp_directoryt.spras as Language,
@Semantics.businessDate.from: true
hrrp_directoryt.hryvalfrom as ValidityStartDate,
@Semantics.text: true
hrrp_directoryt.hrytxt as ProfitCenterHierarchyName,
_ControllingArea,
_Language,
_ControllingAreaText
}
where
hrrp_directoryt.hrytype = '0106';
Save and activate the CDS view.
Create hierarchy directory views. This view will use HRRP_DIRECTORY as data source. HRRP_NODE is contains more registry information such as creation date and user and it just does not have text information. That’s why we created text view for hierarchy directory first in the previous step. Same as HRRP_NODET, you can view this table content by specifying Hierarchy Type(HRYTYPE) as ‘0106’(Profit Center).
Create hierarchy directory views in ADT.
@ObjectModel.representativeKey: 'ProfitCenterHierarchy'
@EndUserText.label: 'Profit Center Hierarchy'
@Analytics: { dataCategory: #DIMENSION }
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #CHECK
@Metadata.ignorePropagatedAnnotations:true
@ObjectModel.usageType.serviceQuality: #A
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MASTER
define view entity YI_PROFITCENTERHIERARCHY
as select from hrrp_directory
association [0..1] to I_ControllingArea as _ControllingAreaText on $projection.ControllingArea = _ControllingAreaText.ControllingArea
association [*] to YI_ProfitCenterHierarchyText as _Text on $projection.ProfitCenterHierarchy = _Text.ProfitCenterHierarchy
and $projection.ControllingArea = _Text.ControllingArea
association [0..1] to I_ControllingArea as _ControllingArea on $projection.ControllingArea = _ControllingArea.ControllingArea
{
@Consumption.valueHelpDefinition: [
{ entity: { name: 'I_ControllingArea',
element: 'ControllingArea' }
}]
@ObjectModel.text.association: '_ControllingAreaText'
@ObjectModel.foreignKey.association: '_ControllingArea'
key cast( hrrp_directory.hrycls as fis_kokrs ) as ControllingArea,
@ObjectModel.text.association: '_Text'
key cast(hrrp_directory.hryid as fis_hryid_prctr ) as ProfitCenterHierarchy,
@Semantics.businessDate.to: true
key cast(hrrp_directory.hryvalto as fis_datbi ) as ValidityEndDate,
@Semantics.businessDate.from: true
cast(hrrp_directory.hryvalfrom as fis_datab ) as ValidityStartDate,
_Text,
_ControllingArea,
_ControllingAreaText
}
where
hrrp_directory.hrytyp = '0106';
Save and activate the CDS view.
Hierarchy node views
Create hierarchy node text views. This view will use HRRP_NODET as data source and this contains child parent relationship and description for each node defined in the hierarchy. You can view your hierarchy in this table content by specifying Hierarchy Type(HRYTYPE) as ‘0106’(Profit Center).
Create hierarchy node text views in ADT.
@ObjectModel.dataCategory: #TEXT
@ObjectModel.representativeKey: 'HierarchyNode'
@EndUserText.label: 'Profit Center Hierarchy Node - Text'
@VDM.viewType: #BASIC
@ObjectModel.usageType.serviceQuality: #A
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MASTER
define view entity YI_PROFITCENTERHIERNODET
as select from hrrp_nodet
association [0..1] to I_ControllingArea as _ControllingAreaText on $projection.ControllingArea = _ControllingAreaText.ControllingArea
association [0..1] to I_ControllingArea as _ControllingArea on $projection.ControllingArea = _ControllingArea.ControllingArea
association [0..1] to I_Language as _Language on $projection.Language = _Language.Language
association [1..*] to YI_PROFITCENTERHIERARCHY as _Hierarchy on $projection.ProfitCenterHierarchy = _Hierarchy.ProfitCenterHierarchy
and $projection.ControllingArea = _Hierarchy.ControllingArea
{
@Consumption.valueHelpDefinition: [
{ entity: { name: 'I_ControllingArea',
element: 'ControllingArea' }
}]
@ObjectModel.text.association: '_ControllingAreaText'
@ObjectModel.foreignKey.association: '_ControllingArea'
key cast( hrrp_nodet.nodecls as fis_kokrs ) as ControllingArea,
@ObjectModel.foreignKey.association: '_Hierarchy'
key cast(hrrp_nodet.hryid as fis_hryid_prctr ) as ProfitCenterHierarchy,
@ObjectModel.text.element: [ 'HierarchyNodeText' ]
key hrrp_nodet.hrynode as HierarchyNode,
@Semantics.businessDate.to: true
key cast(hrrp_nodet.hryvalto as fis_datbi ) as ValidityEndDate,
@Semantics.language: true
key hrrp_nodet.spras as Language,
@Semantics.text: true
hrrp_nodet.nodetxt as HierarchyNodeText,
@Semantics.text: true
substring(hrrp_nodet.nodetxt, 1, 20) as HierarchyNodeShortText,
@Semantics.businessDate.from: true
cast(hrrp_nodet.hryvalfrom as fis_datab ) as ValidityStartDate,
_ControllingArea,
_Hierarchy,
_Language,
_ControllingAreaText
}
where
hrrp_nodet.hrytyp = '0106';
Save and activate.
Create hierarchy node views. This view will use HRRP_NODE as data source and this contains all the node and leaf information defined in the hierarchy including the node level, node sequence and parent child relationship between each node and leaf. You can view your hierarchy in this table content by specifying Hierarchy Type(HRYTYPE) as ‘0106’(Profit Center).
Create hierarchy node text views in ADT. Few things to note in this view:
- @Hierarchy.parentChild annotation defines which field to be used for child and parent in order to create hierarchy
- @Hierarchy.parentChild.directriy annotation defines which hierarchy directory is used. That’s why we are associating YI_PROFITCENTERHIERARCHY created in the previsous steps.
- Associate YI_PROFITCENTERHIERNODET to extract text information for hierarchy node.
- Associate YI_PROFITCENTERHIERARCHY to extract hierarchy directory information.
- Associate master data view YI_PROFITCENTER which we will create in the next step.
@EndUserText.label: 'Profit Center Hierarchy Node'
@ObjectModel.representativeKey: 'HierarchyNode'
@ObjectModel: { dataCategory: #HIERARCHY }
@VDM.viewType: #BASIC
@Hierarchy.parentChild:[
{ name: 'ProfitCetner_Hierarchy',
label: 'ProfitCetner_Hierarchy',
recurse: { parent:[ 'ParentNode'], child:[ 'HierarchyNode'] },
siblingsOrder: [{ by: 'HierarchyNodeSequence', direction: #ASC }],
directory: '_Hierarchy' }]
@ObjectModel.usageType.serviceQuality: #A
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MASTER
define view entity YI_PROFITCENTERHIERARCHYNODE
as select from hrrp_node
association [0..1] to I_ControllingArea as _ControllingAreaText on $projection.ControllingArea = _ControllingAreaText.ControllingArea
association [0..*] to YI_PROFITCENTERHIERNODET as _Text on $projection.ProfitCenterHierarchy = _Text.ProfitCenterHierarchy
and $projection.HierarchyNode = _Text.HierarchyNode
and $projection.ControllingArea = _Text.ControllingArea
and $projection.ProfitCenter = '' // just to show that this association is only to be followed if profitcenter is blank
association [0..*] to YI_PROFITCENTER as _ProfitCenter on $projection.ProfitCenter = _ProfitCenter.ProfitCenter
and $projection.ControllingArea = _ProfitCenter.ControllingArea
association [1..1] to YI_PROFITCENTERHIERARCHY as _Hierarchy on $projection.ProfitCenterHierarchy = _Hierarchy.ProfitCenterHierarchy
and $projection.ControllingArea = _Hierarchy.ControllingArea
and $projection.ValidityEndDate = _Hierarchy.ValidityEndDate
association [0..1] to I_ControllingArea as _ControllingArea on $projection.ControllingArea = _ControllingArea.ControllingArea
{
@Consumption.valueHelpDefinition: [
{ entity: { name: 'I_ControllingArea',
element: 'ControllingArea' }
}]
@ObjectModel.text.association: '_ControllingAreaText'
@Consumption.filter: {mandatory : true, selectionType : #SINGLE, multipleSelections : false }
@ObjectModel.foreignKey.association: '_ControllingArea'
key cast( hrrp_node.nodecls as fis_kokrs ) as ControllingArea,
@Consumption.filter: {mandatory : true, selectionType : #SINGLE, multipleSelections : false }
@ObjectModel.foreignKey.association: '_Hierarchy'
key cast(hrrp_node.hryid as fis_hryid_prctr) as ProfitCenterHierarchy,
@ObjectModel.text.association: '_Text'
key hrrp_node.hrynode as HierarchyNode,
@Semantics.businessDate.to: true
@Consumption.filter: {mandatory : true, selectionType : #SINGLE, multipleSelections : false }
key cast(hrrp_node.hryvalto as fis_datbi preserving type ) as ValidityEndDate,
hrrp_node.parnode as ParentNode,
hrrp_node.hryver as HierarchyVersion,
@Semantics.businessDate.from: true
cast(hrrp_node.hryvalfrom as fis_datab preserving type ) as ValidityStartDate,
@Consumption.valueHelpDefinition: [
{ entity: { name: 'I_ProfitCenterStdVH',
element: 'ProfitCenter' },
additionalBinding: [{ localElement: 'ControllingArea',
element: 'ControllingArea' }]
}]
@ObjectModel.foreignKey.association: '_ProfitCenter'
cast ( hrrp_node.leafvalue as fis_prctr ) as ProfitCenter,
hrrp_node.hryseqnbr as HierarchyNodeSequence,
hrrp_node.hrylevel as HierarchyNodeLevel,
hrrp_node.nodetype as NodeType,
hrrp_node.nodevalue as HierarchyNodeVal,
_Text,
_ProfitCenter,
_Hierarchy,
_ControllingArea,
_ControllingAreaText
}
where
nodetype <> 'D'
and hrrp_node.hrytype = '0106';
Save but at this point YI_PROFITCENTER is not created so we cannot activate it yet.
Master data entity view
Create custom master data view that consumes all the CDS views we created so far. Since we are using profit cetner hierarchy, CEPC(profit center master) will be used as data source. The most important part is to associate YI_PROFITCENTERHIERARCHYNODE and use it for field ProfitCenter.
@EndUserText.label: 'Profit Center'
@VDM.viewType: #BASIC
@ObjectModel.representativeKey: 'ProfitCenter'
@ObjectModel.usageType: {
dataClass: #MASTER,
serviceQuality: #A,
sizeCategory: #M
}
@Analytics:{
dataCategory: #DIMENSION,
dataExtraction: {
enabled: true,
delta.changeDataCapture: {
automatic: true
}
}
}
@Search.searchable: true
@Consumption.filter.businessDate.at: true
define view entity YI_PROFITCENTER
as select distinct from cepc
association [1..1] to I_ControllingArea as _ControllingAreaText on $projection.ControllingArea = _ControllingAreaText.ControllingArea
association [1..1] to I_ControllingArea as _ControllingArea on $projection.ControllingArea = _ControllingArea.ControllingArea
association [0..*] to I_ProfitCenterText as _Text on $projection.ControllingArea = _Text.ControllingArea
and $projection.ProfitCenter = _Text.ProfitCenter
and $projection.ValidityEndDate = _Text.ValidityEndDate
association [0..*] to YI_PROFITCENTERHIERARCHYNODE as _ProfitCenterHierarchyNode on $projection.ControllingArea = _ProfitCenterHierarchyNode.ControllingArea
and $projection.ProfitCenter = _ProfitCenterHierarchyNode.ProfitCenter
{
@Consumption.valueHelpDefinition: [
{ entity: { name: 'I_ControllingArea',
element: 'ControllingArea' }
}]
@ObjectModel.text.association: '_ControllingAreaText'
@ObjectModel.foreignKey.association: '_ControllingArea'
key kokrs as ControllingArea,
@ObjectModel.text.association: '_Text'
@ObjectModel.hierarchy.association: '_ProfitCenterHierarchyNode'
@Search.defaultSearchElement: true
@Search.fuzzinessThreshold: 0.8
key prctr as ProfitCenter,
@Semantics.businessDate.to: true
key datbi as ValidityEndDate,
verak as ProfitCtrResponsiblePersonName,
bukrs as CompanyCode,
verak_user as ProfitCtrResponsibleUser,
@Semantics.businessDate.from: true
datab as ValidityStartDate,
_Text,
_ControllingArea,
_ProfitCenterHierarchyNode,
_ControllingAreaText
}
Save and activate the view. After that activate YI_ProfitCetnerHierNode as well. Testing hierarchy
Go to transaction code RSH1 and enter 2C+’your master data view that uses hierarchy view’ so in our case it is 2CYI_PROFITCENTER. Double click on the hierarchy displayed and you will see the hierarchy defined in the app Manage Global Hierarchy. If there is no hit when entering 2CYI_PROFITCENTER, there is something wrong with the view defined. Double check all the views we created so far to see if you missed anything.
Create CDS Cube and Query
To test our hierarchy views, we will create a sample CDS cube and query.
CDS Cube. This is a simple cube using ACDOCA as data source to present accounting document data. Make sure to add profit center and controlling area add associate to our custom CDS YI_ProfitCenter. We are adding controlling area because as explained above, same hierarchy can be created for multiple controlling area therefore it must be specified in the association join condition.
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Test analytical cube'
@Analytics: { dataCategory: #CUBE }
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType: { sizeCategory: #XXL, dataClass: #MIXED, serviceQuality: #D }
define view entity YANALYTICAL_CUBE
as select from acdoca
association [0..1] to YI_ProfitCenter as _ProfitCenter on $projection.ControllingArea = _ProfitCenter.ControllingArea
and $projection.ProfitCenter = _ProfitCenter.ProfitCenter
association [0..1] to I_ControllingArea as _ControllingArea on $projection.ControllingArea = _ControllingArea.ControllingArea
{
key rldnr as Ledger,
key rbukrs as CompanyCode,
key rbukrs as FiscalYear,
key belnr as AccountingDocument,
key docln as LedgerGLLineItem,
@ObjectModel.foreignKey.association: '_ControllingArea'
kokrs as ControllingArea,
@ObjectModel.foreignKey.association: '_ProfitCenter'
prctr as ProfitCenter,
rhcur as CompanyCodeCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
hsl as AmountInCompanyCodeCurrency,
_ProfitCenter,
_ControllingArea,
}
CDS Query. Use Cube YANALYTICAL_CUBE as data source to create CDS Query. Make sure to add highlighted annotation to make profit center available as hierarchy for filter as well.
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Test analytical Query'
@Analytics.query: true
@VDM: {viewType: #CONSUMPTION}
@ObjectModel.usageType: { dataClass: #MIXED, sizeCategory: #XXL, serviceQuality: #D }
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_QUERY ]
define view entity YANALYITICAL_QUERY
as select from YANALYTICAL_CUBE
{
Ledger,
@Consumption.filter: { selectionType: #SINGLE, multipleSelections: true, mandatory: true}
@AnalyticsDetails.query.display: #KEY_TEXT
CompanyCode,
FiscalYear,
AccountingDocument,
LedgerGLLineItem,
@AnalyticsDetails.query.displayHierarchy: #FILTER
@Consumption.filter: { selectionType: #HIERARCHY_NODE, multipleSelections: true}
ProfitCenter,
CompanyCodeCurrency,
AmountInCompanyCodeCurrency
}
Save and activate CDS Cube and Query.
Testing hierarchy in analytical query
Go to transaction code RSRT and enter 2C+’Your CDS query’ so in our case 2CYANALYITICAL_QUERY. Choose WD Grid if you want to test with Fiori UI. You can see profit center in the filter and search help should be displayed as hierarchy view. The hierarchy view is applied also in the report as dimension.
