How to pass a parameter via cube and dimension CDS to hierarchy CDS view

[sebastian_ta]

Hi all,

We need a solution to pass (two) parameter by the end user to our CDS data model incl. (most important!) via association to the hierarchy view, because without filtering the hierarchy view, the hierarchy doesn't get created due to child nodes been present multiple times with different parents.

Hierarchy view is based on standard table IMPR, the to-be parameters are fields PRNAM and GJAHR (data elements: IM_PRNAM and IM_GNJHR)

We tried e.g. putting the parameters in the hierarchy view, but we cannot maintain them in the corresponding dimension view associated to the hierarchy view, which results in error, when the hierarchy is called by the end user.

Hierarchy view, parameters not active:

@VDM.viewType: #BASIC
@ObjectModel: { dataCategory: #HIERARCHY }
@AbapCatalog.sqlViewName: 'ZI_IMPR_H3_SQL'
@hierarchy.parentChild.name: 'IMPR_01'
@hierarchy.parentChild.label: 'IM IMPR Hierarchy 01'
@hierarchy.parentChild: 
{ recurse:          {   parent: 'ParentNode',   child:  'HierarchyNode'   },
  siblingsOrder:    {   by: 'HierarchyNode',    direction: 'ASC'   },
  orphanedNode.handling: #ROOT_NODES,
 rootNode.visibility: #DO_NOT_ADD_ROOT_NODE
  }
@AccessControl.authorizationCheck: #NOT_ALLOWED  
  
 define view ZIBL_CON_XX_IMPR_HI 
 /*
with parameters
    p_IM_PRNAM: im_prnam,
   p_IM_GNJHR: im_gnjhr
 */
 as select from impr

     association [0..*] to ZIBL_CON_XX_IMPR_MD as _IMPR on $projection.HierarchyNode = _IMPR.posnr   
    
   {
@ObjectModel.foreignKey.association: '_IMPR'
   key posnr as HierarchyNode,

   parnr as ParentNode,

   gjahr as IM_GNJHR,
   prnam as IM_PRNAM,


//Make association public 
   _IMPR

   } // where gjahr = $parameters.p_IM_GNJHR and prnam = $parameters.p_IM_PRNAM

Dimension View:

@AbapCatalog.sqlViewName: 'ZI_IMPR_SQL2'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Investment Program Positions'
@Analytics.dataCategory: #DIMENSION
@Analytics.dataExtraction.enabled: true
@VDM.viewType: #BASIC
@ObjectModel.representativeKey: 'POSNR'

define view ZIBL_CON_XX_IMPR_MD 


as select  from impr as _impr

left outer join v_imak_impr as _mapp
    on _impr.posid = _mapp.posid


association [0..*] to ZIBL_CON_XX_IMPU_TT  as _Text on $projection.posnr = _Text.posnr
association [0..*] to ZIBL_CON_XX_IMPR_HI  as _Hier on $projection.posnr = _Hier.HierarchyNode


{
@ObjectModel.hierarchy.association: '_Hier'
@ObjectModel.text.element:  [ 'Hier_Text' ] 
    key _impr.posnr,
    _impr.parnr as IM_PARNR,
    _impr.vernr as IM_VERNR,
    _impr.objnr as IM_OBJNR,
    _impr.posid as IM_POSID,
    _mapp.posnr as IMA_POSNR,
    
    _Text.post1 as     Hier_Text,
 
   
//Make association public   
    _Text,
    _Hier
} 

Example of Cube/transaction CDS view, associating to the Dimension and Hierarchy CDS view:

@AbapCatalog.sqlViewName: 'ZC_IM_43_SQL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CUBE Reporting Investitionsprogramm 01'
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
define view ZICL_CON_XX_IMR1_ALV_43 

with parameters 
    p_IM_PRNAM: im_prnam,
    p_IM_GNJHR: im_gnjhr,
    p_cv_date: abap.dats,
    p_cv_type: kurst_curr

as select distinct from ZICL_CON_XX_IMR1_ALV_42 (p_IM_PRNAM:$parameters.p_IM_PRNAM, p_IM_GNJHR:$parameters.p_IM_GNJHR, p_cv_date:$parameters.p_cv_date, p_cv_type:$parameters.p_cv_type) as _32Prog

    association[0..*] to ZIBL_CON_XX_IMPR_MD as _IMPR on $projection.IM_POSNR = _IMPR.posnr
    association[1] to ZIBL_CON_XX_IMAK_FA as _IMAK on $projection.IMA_POSNR = _IMAK.posnr      
    association[1] to ZIBL_CON_XX_VERNR_MD as _VERNR on $projection.IM_VERNR = _VERNR.vernr
    association[1] to ZIBL_CON_XX_IZWEK_MD as _izwek on $projection.IZWEK = _izwek.izwek
    association[1] to ZIBL_CON_XX_TJ02T_TT as _sts_syst on $projection.J_STATUS_Syst = _sts_syst.istat
    association[1] to ZIBL_CON_XX_TJ30T_TT as _sts_user on $projection.J_STATUS_User = _sts_user.estat and $projection.IMA_STSAK = _sts_user.stsma
   
{
//Dimensions
key BP_OBJEKT,    
    IMA_POSNR,
    IMA_POSID,
    GJAHR,
        @Semantics.currencyCode: true
    TWAER,  
    IM_GNJHR,
    IM_PRNAM,
    IM_POSID,
    IM_POSNR,
    IM_VERNR,
    IZWEK,
    J_STATUS_User,
    J_STATUS_Syst,
    IMA_IVART,
    J_OBJNR,  
    IMA_STSAK,
    
        @Semantics.currencyCode: true
    cast('EUR' as abap.cuky(5)) as EUR_CURR,
           
//Measures / KPIs 
// KPIs in Transaction Currency calculated    
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'TWAER'  Prog_Plan,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'TWAER'  Prog_Budg,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'TWAER'  Prog_Budg_Plan,  
      
// KPIs in Group Currency EUR calculated    
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'EUR_CURR'  Prog_Plan_EUR,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'EUR_CURR'  Prog_Budg_EUR,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'EUR_CURR'  Prog_Budg_Plan_EUR,

//Measures / KPIs 
// KPIs in Transaction Currency calculated    
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'TWAER'  MANF_Plan,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'TWAER'  MANF_Inve,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'TWAER'  MANF_Plan_Inve,  
      
// KPIs in Group Currency EUR calculated    
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'EUR_CURR'  MANF_Plan_EUR,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'EUR_CURR'  MANF_Inve_EUR,
    @DefaultAggregation: #SUM   @Semantics.amount.currencyCode: 'EUR_CURR'  MANF_Plan_Inve_EUR,
         
//Make association public
    _IMPR,
    _IMAK,
    _VERNR,
    _izwek  , 
    _sts_syst,
    _sts_user 
} 

Thanks upfront for your support, dear SAP Gurus 😃

[Ewelina]

sebastian_ta did you manage to solve the issue maybe?

[sebastian_ta]

ewelina_sap no, unfortunately not.

Instead we use a workaround to create each year a new Hierarchy CDS view with hard coded year, so the end user in Fiori or Analysis for Office needs to select after the other prompts, which hierarchy (year) needs to be displayed based on the dimension hierarchies.