Thursday, 23 February 2017

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.

How to consume a hierarchy in CDS view analytical query

How to consume a hierarchy in CDS view analytical query

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:

How to consume a hierarchy in CDS view analytical query

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

How to consume a hierarchy in CDS view analytical query

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

How to consume a hierarchy in CDS view analytical query

No comments:

Post a Comment