Skip to Content

Applies to: SAP ECC 6.0, SAP BI 7.3.

Author: Sanchit Gupta

Company: Accenture Services Pvt. Ltd.

Created on: 22nd October 2013

Author Bio:  Sanchit is an Associate Software Engineer in Accenture Services Private Limited. He has been involved in SAP BW Implementation Projects.

Business Scenario:

The standard hierarchy datasource provides hierarchies in the SAP BI system in the Hierarchy format, but sometimes we need the hierarchies in the flat structure as per the business requirements, e.g.  to support other reporting interfaces or incase we need specific level objects to be explicitly used. The Hierarchy is in the form a nodes/Folders structure which can be flatenned with all its levels.

Illustration:

The following steps illustrate how the above mentioned workaround can be utilized.

In our case, we will be using the InfoObject 0PROFIT_CTR for flattening its defined levels.

Pre-requisites:

We need to have pre-defined levels of the hierarchy and the Hierarchy should be already  uploaded and acitvated for the corresponding InfoObject (e.g. 0PROFIT_CTR).

Creating Self Transformation:

First, we need to create self-transformation for the InfoObject 0PROFIT_CTR. Select 0PROFIT_CTR (attribute) and click create transformation:

Create transformation.png

Now either we can map all the InfoObjects from the source to Target, or we can map only levels which we need to populate via the routine.

In this case, we have pre-defined 9 Levels (where 9th level corresponds to Profit Center itself).

Create transformation2.png

In order to get the hierarchies in the Flat format we need to write End/Start Routine.

In this case, we are using the End Routine.

  METHOD end_routine.
*=== Segments ===

FIELD-SYMBOLS:
      <RESULT_FIELDS>    TYPE _ty_s_TG_1.

    DATA:
      MONITOR_REC     TYPE rstmonitor.

*$*$ begin of routine – insert your code only below this line        *-*
    “insert your code here
*–  fill table “MONITOR” with values of structure “MONITOR_REC”
*-   to make monitor entries
    “to cancel the update process
*    raise exception type CX_RSROUT_ABORT.
TYPES: BEGIN OF lty_final,
        profitctr   
TYPE rsnodename,
        lvl1_nodname
TYPE rsnodename,
        lvl2_nodname
TYPE rsnodename,
        lvl3_nodname
TYPE rsnodename,
        lvl4_nodname
TYPE rsnodename,
        lvl5_nodname
TYPE rsnodename,
        lvl6_nodname
TYPE rsnodename,
        lvl7_nodname
TYPE rsnodename,
        lvl8_nodname
TYPE rsnodename,
      
END OF lty_final.

    DATA: lw_profit  TYPE /bi0/hprofit_ctr,
          lw_profit1 TYPE /bi0/hprofit_ctr,
          lw_final   TYPE lty_final,

lt_final   TYPE STANDARD TABLE OF lty_final,
    lv_hieid  
TYPE rshieid.

DATA: lv_parentid TYPE rsparent,
     lv_strlen   TYPE n LENGTH 2.

    IF    lt_profit[] IS INITIAL
      AND lt_final[] IS INITIAL
      AND lt_profit1[] IS INITIAL
      AND lv_hieid IS INITIAL.

      SELECT SINGLE hieid
        FROM /bi0/hprofit_ctr
        INTO lv_hieid
        WHERE nodename = ’<Input the Hierarchy technical Name>’. “Enter the hierarchy technical name which you want to flatten.      IF sysubrc NE 0.
        CLEAR lv_hieid.
      ELSE.

        SELECT * FROM /bi0/hprofit_ctr
          INTO TABLE lt_profit
          WHERE hieid EQ lv_hieid.
        IF sysubrc EQ 0.
          lt_profit1[] = lt_profit[].
          SORT lt_profit1 BY nodeid.
          SORT lt_profit BY iobjnm DESCENDING tlevel DESCENDING.

          LOOP AT lt_profit INTO lw_profit WHERE iobjnm =
          ‘0PROFIT_CTR’.

            CLEAR lv_parentid.
            lv_parentid        = lw_profitparentid.
            lv_strlen          = strlen( lw_profitnodename ).
            lv_strlen          = lv_strlen 10.
            lw_finalprofitctr = lw_profitnodename+lv_strlen(10).

            CLEAR lv_strlen.

            DO.
              READ TABLE lt_profit1 INTO lw_profit1 WITH KEY nodeid =
              lv_parentid.
              IF sysubrc EQ 0.
                lv_strlen = strlen( lw_profit1nodename ).
                lv_strlen = lv_strlen 4.

                CASE lw_profit1tlevel.
                  WHEN ’01’.
                    lw_finallvl1_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                  WHEN ’02’.
                    lw_finallvl2_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                  WHEN ’03’.
                    lw_finallvl3_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                  WHEN ’04’.
                    lw_finallvl4_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                  WHEN ’05’.
                    lw_finallvl5_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                  WHEN ’06’.
                    lw_finallvl6_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                  WHEN ’07’.
                    lw_finallvl7_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                  WHEN ’08’.
                    lw_finallvl8_nodname   =
                    lw_profit1nodename+4(lv_strlen).
                ENDCASE.

                CLEAR lv_parentid.
                IF lw_profit1parentid IS NOT INITIAL.
                  lv_parentid = lw_profit1parentid.
                ELSE.
                  EXIT.
                ENDIF.
              ENDIF.
            ENDDO.

            APPEND lw_final TO lt_final.
            CLEAR: lw_final, lv_strlen.
          ENDLOOP.
        ENDIF.
      ENDIF.
    ENDIF.

    LOOP AT RESULT_PACKAGE ASSIGNING <result_fields>.

      READ TABLE lt_final INTO lw_final
      WITH KEY profitctr = <result_fields>profit_ctr.

      IF sysubrc EQ 0.
        <result_fields>/bic/zlevel1 = lw_finallvl1_nodname.
        <result_fields>/bic/zlevel2 = lw_finallvl2_nodname.
        <result_fields>/bic/zlevel3 = lw_finallvl3_nodname.
        <result_fields>/bic/zlevel4 = lw_finallvl4_nodname.
        <result_fields>/bic/zlevel5 = lw_finallvl5_nodname.
        <result_fields>/bic/zlevel6 = lw_finallvl6_nodname.
        <result_fields>/bic/zlevel7 = lw_finallvl7_nodname.
        <result_fields>/bic/zlevel8 = lw_finallvl8_nodname.

      ENDIF.
    ENDLOOP.

    CLEAR: lw_profit1, lw_profit, lw_final, lv_parentid, lv_strlen.

*$*$ end of routine – insert your code only before this line         *-*
  ENDMETHOD.                    “end_routine

Now after activation of the transformation, we can run the DTP for self-transformation to upload 0PROFIT_CTR with its corresponding levels.

To report this post you need to login first.

8 Comments

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

  1. Brian Patterson

    This is very helpful.  Do you also have an example of how to attach the Master data text to your custom Info Objects (ZLEVEL1 – ZLEVEL7)?  I’d like to get the Short, Medium and Long text but don’t know where to pull from.  I’m assuming that I’d setup a Master Data Text Transformation with custom code in the End Routine and query the RSTHIERNODE table. 

    (0) 
  2. Coskun Istanbulluoglu

    Hello,

    Is there a way to apply this method for a time dependent hierarchy?

    In Hr module, we have 0orgunit hierarchy.

    It would be very good to transform it, It could resolve report formatting issues of our Hr users.

    (0) 
  3. rajesh kumar

    Hi All,

    i have a requirement to convert the client file into Hierarchy formate.

    is there any ABAP code to convert the XL file in to Hierarchy model like

    NODEID     NODENAME     IOBJNAME   …. etc

    please help me with code

    Really appriciated

    Regards

    Raj

    (0) 

Leave a Reply