Skip to Content

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!

 

2 Comments
You must be Logged on to comment or reply to a post.
  •  

    Hi,

    We tried to display hierarchy using Hierarchy CDS views and it is fine in "Analysis Office" but the expansion of nodes are not working if the same is consumed in Fiori Elements List report.

    The issue is detailed in (https://answers.sap.com/questions/581579/node-expand-not-working-in-hierarchy-cds-view-cons.html). Please can you let us know your comments.

    Regards,

    Vivek

  • Hi Maksim,

     

    How to make the hierarchy show  even the  non posted nodes too?. At present , we are seeing  only posted nodes.

    Regards,

    Rajesh