How to consume a hierarchy in CDS view analytical query

[former_member205880]

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!