This blog is about – how to provide static or dynamic default value for hierarchy node variable in analytical queries.

Static Default values

Hierarchy node variable is defined using @AnalyticsDetails.variable annotation. Static default value for this variable can be provided using @AnalyticsDetails.variable.defaultHierarchyNode annotation.

In @AnalyticsDetails.variable annotation, selectionType need to set as #HIERRARCHY_NODE, also hierarchyBinding is specified along with referenceElement, MultipleSelections.

Example

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Example of static default value’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define transient view entity ZLR_RES_HIERNODE2
provider contract analytical_query
with parameters

@EndUserText.label: ‘Connection Hierarchy’
p_hienm : char5,

@AnalyticsDetails.variable: {
selectionType: #HIERARCHY_NODE,
referenceElement: ‘connid’,
hierarchyBinding: [{ type:#PARAMETER, value: ‘p_hienm’}],
multipleSelections: true,
defaultHierarchyNode:
{
nodeType: ‘carrid’,
node: [{element: ‘carrid’, value:’AZ’ }]
}

}

@Consumption.hidden: true
p_hier_node : s_conn_id

as projection on ZOQ_FLIGHT
{

@AnalyticsDetails.query.displayHierarchy: #FILTER
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.totals: #SHOW
connid,

currency,

@Semantics.amount.currencyCode: ‘CURRENCY’
@AnalyticsDetails.query.axis: #COLUMNS
paymentsum

}
where
currency = ‘EUR’
and connid = $parameters.p_hier_node

Output

Dynamic Default Values 

Dynamic default values for hierarchy node variables should be derived from another CDS view (lookup view) at runtime.

@Consumption.derivation annotation enables derivation of the value for a parameter or a filter automatically at runtime by selecting a row from a given entity.

At first, a separate hierarchy node lookup view is defined.

Then hierarchy node variable is defined using @AnalyticsDetails.variable annotation.

To specify a default value for the previously defined node variable @Consumption.derivation.resultHierarchyNode is used.

@Consumption.derivation annotation contains,

lookupEntity, which provide the name of hierarchy node lookup view to get the hierarchy node.

resultHierachyNode annotation is used to provide hierarchy node value to a parameter.

resultHierarchyNode.nodeTypeElement must point to a field in the lookup entity and this field must return a fieldname. This fieldname (in the data of the lookup view) must be an existing fieldname in the lookup view.  If the value of field NodeTypeElement in the result of the lookup is initial, the row represents a leaf.

resultHierarchyNode.Mapping describes the mapping of the elements in the hierarchy view and the elements in the lookup entity.

Consumption.derivation.binding is possible to add filters to the lookup. This is generic and not specific to hierarchy nodes.

Example

Step 1: Defining Hierarchy node lookup view

Hierarchy node look up view is use as a lookup entity in the @Consumption.derivation annotation.

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Derivation for node parameter-resultHierarchyNode’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define view entity ZLR_RES_DERIVE_BASE as select from t000
{
cast( ‘carrid1’ as fieldname ) as nodeType,
cast( ‘LH’ as s_carr_id ) as carrid1,
cast( ‘ ‘ as s_conn_id ) as connid1,
cast( ‘ ‘ as rsshnodename ) as nodename1,
$session.user as userName
}
where mandt = ‘000’

Step 2: Defining hierarchy node variable and use Consumption.derivation annotation in analytical query

In this example, ZLR_RES_DERIVE_BASE view is used as lookupEntity- which provides default value to the parameter

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Example of dynamic default value’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define transient view entity ZLR_RES_HIERNODE1
provider contract analytical_query
with parameters

@EndUserText.label: ‘Connection Hierarchy’
p_hienm : char5,

@AnalyticsDetails.variable: {
selectionType: #HIERARCHY_NODE,
referenceElement: ‘connid’,
hierarchyBinding: [{ type:#PARAMETER, value: ‘p_hienm’}],
multipleSelections: true

}

@Consumption.derivation: {
lookupEntity: ‘ZLR_RES_DERIVE_BASE’,
resultHierarchyNode: {
nodeTypeElement: ‘nodetype’,
mapping: [
{ hierarchyElement: ‘carrid’, lookupElement: ‘carrid1’ },
{ hierarchyElement: ‘connid’, lookupElement: ‘connid1’ },
{ hierarchyElement: ‘nodename’, lookupElement: ‘nodename1’ }
]
}
}
@Consumption.hidden: true
p_hier_node : s_conn_id

as projection on ZOQ_FLIGHT
{

@AnalyticsDetails.query.displayHierarchy: #FILTER
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.totals: #SHOW
connid,

currency,

@Semantics.amount.currencyCode: ‘CURRENCY’
@AnalyticsDetails.query.axis: #COLUMNS
paymentsum

}
where currency = ‘EUR’ and connid = $parameters.p_hier_node

Output

Please note that in the older release instead of using @AnalyticsDetails.variable annotation, @Consumption.hierarchyNodeSelection is used.

@Consumption.hierarchyNodeSelection: { 

  hierarchyElement: ‘connid’,

  hierarchyBinding: [{ type:#PARAMETER, value: ‘p_hienm’}],

  multipleSelections: true

}

​ This blog is about – how to provide static or dynamic default value for hierarchy node variable in analytical queries.Static Default valuesHierarchy node variable is defined using @AnalyticsDetails.variable annotation. Static default value for this variable can be provided using @AnalyticsDetails.variable.defaultHierarchyNode annotation.In @AnalyticsDetails.variable annotation, selectionType need to set as #HIERRARCHY_NODE, also hierarchyBinding is specified along with referenceElement, MultipleSelections.Example @AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Example of static default value’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define transient view entity ZLR_RES_HIERNODE2
provider contract analytical_query
with parameters

@EndUserText.label: ‘Connection Hierarchy’
p_hienm : char5,

@AnalyticsDetails.variable: {
selectionType: #HIERARCHY_NODE,
referenceElement: ‘connid’,
hierarchyBinding: [{ type:#PARAMETER, value: ‘p_hienm’}],
multipleSelections: true,
defaultHierarchyNode:
{
nodeType: ‘carrid’,
node: [{element: ‘carrid’, value:’AZ’ }]
}

}

@Consumption.hidden: true
p_hier_node : s_conn_id

as projection on ZOQ_FLIGHT
{

@AnalyticsDetails.query.displayHierarchy: #FILTER
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.totals: #SHOW
connid,

currency,

@Semantics.amount.currencyCode: ‘CURRENCY’
@AnalyticsDetails.query.axis: #COLUMNS
paymentsum

}
where
currency = ‘EUR’
and connid = $parameters.p_hier_node OutputDynamic Default Values Dynamic default values for hierarchy node variables should be derived from another CDS view (lookup view) at runtime.@Consumption.derivation annotation enables derivation of the value for a parameter or a filter automatically at runtime by selecting a row from a given entity.At first, a separate hierarchy node lookup view is defined.Then hierarchy node variable is defined using @AnalyticsDetails.variable annotation.To specify a default value for the previously defined node variable @Consumption.derivation.resultHierarchyNode is used.@Consumption.derivation annotation contains,lookupEntity, which provide the name of hierarchy node lookup view to get the hierarchy node.resultHierachyNode annotation is used to provide hierarchy node value to a parameter.resultHierarchyNode.nodeTypeElement must point to a field in the lookup entity and this field must return a fieldname. This fieldname (in the data of the lookup view) must be an existing fieldname in the lookup view.  If the value of field NodeTypeElement in the result of the lookup is initial, the row represents a leaf.resultHierarchyNode.Mapping describes the mapping of the elements in the hierarchy view and the elements in the lookup entity.Consumption.derivation.binding is possible to add filters to the lookup. This is generic and not specific to hierarchy nodes.ExampleStep 1: Defining Hierarchy node lookup viewHierarchy node look up view is use as a lookup entity in the @Consumption.derivation annotation. @AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Derivation for node parameter-resultHierarchyNode’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define view entity ZLR_RES_DERIVE_BASE as select from t000
{
cast( ‘carrid1’ as fieldname ) as nodeType,
cast( ‘LH’ as s_carr_id ) as carrid1,
cast( ‘ ‘ as s_conn_id ) as connid1,
cast( ‘ ‘ as rsshnodename ) as nodename1,
$session.user as userName
}
where mandt = ‘000’ Step 2: Defining hierarchy node variable and use Consumption.derivation annotation in analytical queryIn this example, ZLR_RES_DERIVE_BASE view is used as lookupEntity- which provides default value to the parameter @AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Example of dynamic default value’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define transient view entity ZLR_RES_HIERNODE1
provider contract analytical_query
with parameters

@EndUserText.label: ‘Connection Hierarchy’
p_hienm : char5,

@AnalyticsDetails.variable: {
selectionType: #HIERARCHY_NODE,
referenceElement: ‘connid’,
hierarchyBinding: [{ type:#PARAMETER, value: ‘p_hienm’}],
multipleSelections: true

}

@Consumption.derivation: {
lookupEntity: ‘ZLR_RES_DERIVE_BASE’,
resultHierarchyNode: {
nodeTypeElement: ‘nodetype’,
mapping: [
{ hierarchyElement: ‘carrid’, lookupElement: ‘carrid1’ },
{ hierarchyElement: ‘connid’, lookupElement: ‘connid1’ },
{ hierarchyElement: ‘nodename’, lookupElement: ‘nodename1’ }
]
}
}
@Consumption.hidden: true
p_hier_node : s_conn_id

as projection on ZOQ_FLIGHT
{

@AnalyticsDetails.query.displayHierarchy: #FILTER
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.totals: #SHOW
connid,

currency,

@Semantics.amount.currencyCode: ‘CURRENCY’
@AnalyticsDetails.query.axis: #COLUMNS
paymentsum

}
where currency = ‘EUR’ and connid = $parameters.p_hier_node OutputPlease note that in the older release instead of using @AnalyticsDetails.variable annotation, @Consumption.hierarchyNodeSelection is used.@Consumption.hierarchyNodeSelection: {   hierarchyElement: ‘connid’,  hierarchyBinding: [{ type:#PARAMETER, value: ‘p_hienm’}],  multipleSelections: true}   Read More Technology Blogs by SAP articles 

#SAP

#SAPTechnologyblog