How to consume a hierarchy in CDS view analytical query

Hi!

In my previous post “How to build a Custom Hierarchy in ABAP CDS views” I create a custom CDS view with hierarchy based on HCM Area tables.

Now we will create a analytical query:

• Show how to enable hierarchy in a query
• Open it in RSRT to test purpose
• Find it Query Browser Fiori App and open with Disign Studio template (0ANALYSIS)

Let start.

1. Create a new consumption CDS Zc_Orgunit__Query

Notes:

• @VDM.viewType: #CONSUMPTION and @Analitics.query: true are needed for this view is visible is Query Browser App and for a transient analytical query 2CZC_ORGUNIT__QUERY is created.
• @AnalyticsDetails.query.displayHierarchy: #ON is needed for display hierarchy for Organisational unit.
• @AnalyticsDetails.query.hierarchyInitialLevel: 3 is neded for definition of default expand level.
• *Optional, not included in example. If there are several hierarchies, you should select a default one. Use hierarchy binding annotations for this. For example, the simplest case of constant definition of default hierarchy name:
  • @AnalyticsDetails.query.hierarchyBinding.type: #CONSTANT
  • @AnalyticsDetails.query.hierarchyBinding.value: ‘<HIERNAME>’

@AbapCatalog.sqlViewName: 'ZC_ORGUNITQ'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'ZC_ORGUNIT__QUERY'
@Analytics.query: true
@VDM.viewType: #CONSUMPTION  
@OData.publish: true
define view Zc_Orgunit__Query as select from Zi_Orgunit_Dim {
@AnalyticsDetails.query.displayHierarchy: #ON
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.hierarchyInitialLevel: 3
Zi_Orgunit_Dim.Orgunit, 
@AnalyticsDetails.query.axis: #ROWS
Zi_Orgunit_Dim.EndDate, 
@AnalyticsDetails.query.axis: #ROWS
Zi_Orgunit_Dim.StartDate
    
}

2. Open it in RSRT with option WD grid (embedded analytics). This is quite new option that was introduce (as I could found) from NW 7.4 on HANA.

We can also make some changes in query definition in CDS view to use hierarchy nodes in variable selection screen:

@AbapCatalog.sqlViewName: 'ZC_ORGUNITQ'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'ZC_ORGUNIT__QUERY'
@Analytics.query: true
@VDM.viewType: #CONSUMPTION  
@OData.publish: true
define view Zc_Orgunit__Query as select from Zi_Orgunit_Dim {

@AnalyticsDetails.query.displayHierarchy:#FILTER
@AnalyticsDetails.query.hierarchyInitialLevel: 3
@Consumption.filter.selectionType: #HIERARCHY_NODE
@AnalyticsDetails.query.axis: #ROWS
Zi_Orgunit_Dim.Orgunit, 
@AnalyticsDetails.query.axis: #ROWS
Zi_Orgunit_Dim.EndDate, 
@AnalyticsDetails.query.axis: #ROWS
Zi_Orgunit_Dim.StartDate
    
}

RSRT result with this option:

3. Open CDS view with Query Browser App in Fiori Launchpad of S/4HANA system:

The result is the same. Hierarchy nodes filtration is available in Filters button.

Thank you for attention!