Skip to Content

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!

 

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply