How to consume a hierarchy in CDS view analytical query

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.

@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.