How to read child hierarchy entries in DTP

Scenario: There is a requirement to load data with a filter on a certain infoObject.

This filter is a parent node of a hierarchy.

The data in the source is having rows with leaf nodes only for this infoObject.

See below diagram to get clarity:

From the above diagrams, observe that the data in the source cube is having only leaf nodes for the Region ZREG1.

Also the hierarchy for ZREG1 is uneven.i.e. some text nodes have a leaf node whereas some text nodes have another text node as next parent.

In BW system, this hierarchy will be maintained in the table /BIC/HZREG1 with following structure:

Here, hierarchy ID is a system generated alphanumeric character, hierarchy version is ‘A’ (Active), hierarchy node is a distinct number assigned to each node.

InfoObject is the technical name of the InfoObject on which the hierarchy is defined (ZREG1), Node name is the name of the node in the hierarchy, Level is how much deep the node is from the main parent (Region), Parent is the node number of the parent node, child is the node number of the child node, next is the node number of the node in the same level.

Observe the hierarchy diagram and the table carefully to understand the entries in the hierarchy table.

Coming back to our requirement, the DTP should read all the leaf nodes of the region hierarchy from the H table and compare it with the source cube data. If the matching entries are found, then the data is loaded, else it is discarded.

Hence the source data having region as (US, Mexico, Brazil, India, China, Italy, UK) is only loaded. Any data having region value apart from above set will be filtered out and should  not get loaded.

Solution:

The DTP filter will work in the following way: Since an InfoObject can have multiple hierarchies (each having multiple parents), the required hierarchy name and parent node name is stored in a custom table. This custom table is read in the DTP filter and a program will read all the parents and child in that hierarchy and store them in internal table. These internal table values will then be passed to the L_T_RANGE table of the DTP.

Custom table name: ZREG1_P

Create this table in tcode SE11 in the system with appropriate data elements and domains.

• INFO_OBJ for field infoObject
• HIENAME for field Hierarchy Name
• PARENT_ID for field Parent node
• IDENTIFIER_KEY for Identifier

You can find the hierarchy names and their technical IDs in the table RSHIEDIR. Also the DTP program accesses this table to get the Largest Node Id. The program then loops through every parent node ids until the leaf level node is reached and stores this value in an internal table.

Identifier key is used to assign a value to each hierarchy name and parent combination to easily use this value in multiple DTPs.

DTP Program:

form /BIC/0IZREG1
   tables l_t_range structure rssdlrange
   using i_r_request type ref to IF_RSBK_REQUEST_ADMINTAB_VIEW
         i_fieldnm type RSFIELDNM
   changing p_subrc like sy-subrc.
 * Insert source code to current selection field
 *$*$ begin of routine - insert your code only below this line   *-*
 
 TYPES: BEGIN OF TYPE_ZREG1_P,
         INFO_OBJ    TYPE ZREG1_P-INFO_OBJ,
         HIENAME     TYPE ZREG1_P-HIENAME,
         PARENTID     TYPE ZREG1_P-PARENTID,
       END OF TYPE_ZREG1_P.
 
 DATA: IT_TYPE_ZREG1_P TYPE TABLE OF TYPE_ZREG1_P,
       FS_TYPE_ZREG1_P TYPE TYPE_ZREG1_P.
 
 SELECT INFO_OBJ                "SELECT Query Custom Table
        HIENAME
        PARENTID
        FROM ZREG1_P
        INTO TABLE IT_TYPE_ZREG1_P WHERE IDENTIFIER_KEY = 1.
 ***********************************************************************
 DATA: P_NODENAME TYPE /BIC/HZREG1-NODENAME .
 DATA: COUNT TYPE N.
 DATA: COUNTER TYPE /BIC/HZREG1-TLEVEL.
  
 TYPES: BEGIN OF TY_HIE,                "Hierarchy table Structure
                HIEID TYPE /BIC/HZREG1-HIEID,
                OBJVERS TYPE /BIC/HZREG1-OBJVERS,
                NODEID TYPE /BIC/HZREG1-NODEID,
                IOBJNM TYPE /BIC/HZREG1-IOBJNM,
                NODENAME TYPE /BIC/HZREG1-NODENAME,
                TLEVEL TYPE /BIC/HZREG1-TLEVEL,
                LINK TYPE /BIC/HZREG1-LINK,
                PARENTID TYPE /BIC/HZREG1-PARENTID,
                CHILDID TYPE /BIC/HZREG1-CHILDID,
                NEXTID TYPE /BIC/HZREG1-NEXTID,
              END OF TY_HIE.
 
 TYPES: BEGIN OF TY_NODEID,
              NODEID TYPE /BIC/HZREG1-NODEID,
        END OF TY_NODEID.
 
 DATA: WA_HIE TYPE  TY_HIE.
 DATA: WA_HIE1 TYPE TY_HIE.
 DATA: WA_HIE2 TYPE TY_HIE.
 DATA: WA_HIE5 TYPE TY_HIE.
 DATA: IT_HIE TYPE TABLE OF TY_HIE.
 DATA: IT_HIE1 TYPE TABLE OF TY_HIE.
 DATA: IT_HIE2 TYPE TABLE OF TY_HIE.
 DATA: IT_HIE5 TYPE TABLE OF TY_HIE.
 DATA: WA_NODEID1 TYPE TY_NODEID.
 DATA: IT_NODEID1 TYPE TABLE OF TY_NODEID.
 
 DATA: FS_HIE1 TYPE  TY_HIE,
       IT_HIE1_TEMP TYPE TABLE OF  TY_HIE.
 
 DATA: T_NODEID TYPE RANGE OF /BIC/HZREG1-NODEID.
 DATA: R_NODEID LIKE LINE OF T_NODEID.
 DATA: LV_IDX TYPE SY-TABIX.
 
 ********************************** For Hierarchy ID ***********************
    TYPES: BEGIN OF X_HIEDIR,
            HIEID    TYPE RSHIEDIR-HIEID,
            OBJVERS  TYPE RSHIEDIR-OBJVERS,
            HIENM    TYPE RSHIEDIR-HIENM,
            IOBJNM   TYPE RSHIEDIR-IOBJNM,
          END OF X_HIEDIR.
 
  DATA V_HIEID TYPE RSHIEDIR-HIEID.
 *Fetching the data from RSHIEDIR table and inserting into V_HIEID
 *variable Based on HIENM & INFO_OBJ
  SELECT HIEID
         FROM RSHIEDIR
         INTO V_HIEID
         WHERE OBJVERS = 'A'
         AND HIENM = 'Hier1'
         AND IOBJNM = '/BIC/ZREG1'.
  ENDSELECT.
 ************************************************************************
 SELECT MAX( TLEVEL )  "SELECT query for top N level and place in COUNTER
        INTO (COUNTER)
        FROM /BIC/HZREG1.


"Fetching the data from Hierachy table and inserting into Internal table

 SELECT * FROM /BIC/HZREG1
 INTO CORRESPONDING FIELDS OF TABLE IT_HIE
 WHERE HIEID = V_HIEID AND OBJVERS = 'A'.
 
        IT_HIE1[] = IT_HIE[].
 
 
 LOOP AT IT_TYPE_ZREG1_P INTO FS_TYPE_ZREG1_P.
 
   LOOP AT  IT_HIE1 INTO FS_HIE1
                    WHERE NODENAME EQ FS_TYPE_ZREG1_P-PARENTID.
 
       APPEND FS_HIE1 TO IT_HIE1_TEMP .
       CLEAR FS_HIE1.
 
   ENDLOOP.
       CLEAR  FS_TYPE_ZREG1_P-PARENTID.
  ENDLOOP.
       CLEAR IT_HIE1.
 
  IT_HIE1 =  IT_HIE1_TEMP.
 
 LOOP AT IT_HIE1 INTO WA_HIE1.
 
   R_NODEID-SIGN = 'I'.
   R_NODEID-OPTION = 'EQ'.
   R_NODEID-LOW = WA_HIE1-NODEID.
   APPEND R_NODEID TO T_NODEID .
   APPEND WA_HIE1-NODEID TO IT_NODEID1.
 
   ENDLOOP.
 
 DESCRIBE TABLE T_NODEID LINES COUNT.        "Describing the Count
 
 IF COUNT > 0. 
   DO COUNTER TIMES. 
   IT_HIE2 = IT_HIE. 
   DELETE IT_HIE2 WHERE PARENTID NOT IN T_NODEID.
 
   LOOP AT IT_HIE2 INTO WA_HIE2.
 
   R_NODEID-SIGN = 'I'.
   R_NODEID-OPTION = 'EQ'.
   R_NODEID-LOW = WA_HIE2-NODEID.
   APPEND R_NODEID TO T_NODEID.
 
   ENDLOOP.
 
   REFRESH: IT_HIE2.
 
 ENDDO.
 
   ENDIF.
 
 ****Fetching the data from Hierachy table and inserting into Internal
 *table IT_HIE5
 ****with HIEID Variable and  RRANGE of T_NODEID table

 SELECT * FROM /BIC/HZREG1
          INTO CORRESPONDING FIELDS OF TABLE IT_HIE5
          WHERE HIEID = V_HIEID
                AND OBJVERS = 'A'
                AND NODEID IN T_NODEID .
 
  DATA: L_IDX LIKE SY-TABIX.
   READ TABLE L_T_RANGE WITH KEY
        FIELDNAME = I_FIELDNM.
   L_IDX = SY-TABIX.
 
 DATA: L_S_RANGE TYPE RSSDLRANGE.
 DATA V1 TYPE /BIC/HZREG1-NODENAME.
 
 LOOP AT IT_HIE5 INTO WA_HIE5.
 
 CLEAR V1.


 V1 = WA_HIE5-NODENAME.
 
 L_S_RANGE-IOBJNM = '/BIC/ZREG1'.
 L_S_RANGE-FIELDNAME = I_FIELDNM.
 L_S_RANGE-SIGN = 'I'.
 L_S_RANGE-OPTION = 'EQ'.
 L_S_RANGE-LOW = V1.
 
 APPEND L_S_RANGE TO L_T_RANGE.
 ENDLOOP.
 
   IF L_IDX <> 0.
   MODIFY L_T_RANGE INDEX L_IDX.
 ELSE.
  APPEND  L_T_RANGE.
 ENDIF.
 P_SUBRC = 0.