Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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:

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).

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 sy-subrc NE 0.
        CLEAR lv_hieid.
      ELSE.

        SELECT * FROM /bi0/hprofit_ctr
          INTO TABLE lt_profit
          WHERE hieid EQ lv_hieid.
        IF sy-subrc 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_profit-parentid.
            lv_strlen          = strlen( lw_profit-nodename ).
            lv_strlen          = lv_strlen -10.
            lw_final-profitctr = lw_profit-nodename+lv_strlen(10).

            CLEAR lv_strlen.

            DO.
              READ TABLE lt_profit1 INTO lw_profit1 WITH KEY nodeid =
              lv_parentid.
              IF sy-subrc EQ 0.
                lv_strlen = strlen( lw_profit1-nodename ).
                lv_strlen = lv_strlen - 4.

                CASE lw_profit1-tlevel.
                  WHEN '01'.
                    lw_final-lvl1_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                  WHEN '02'.
                    lw_final-lvl2_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                  WHEN '03'.
                    lw_final-lvl3_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                  WHEN '04'.
                    lw_final-lvl4_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                  WHEN '05'.
                    lw_final-lvl5_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                  WHEN '06'.
                    lw_final-lvl6_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                  WHEN '07'.
                    lw_final-lvl7_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                  WHEN '08'.
                    lw_final-lvl8_nodname   =
                    lw_profit1-nodename+4(lv_strlen).
                ENDCASE.

                CLEAR lv_parentid.
                IF lw_profit1-parentid IS NOT INITIAL.
                  lv_parentid = lw_profit1-parentid.
                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 sy-subrc EQ 0.
        <result_fields>-/bic/zlevel1 = lw_final-lvl1_nodname.
        <result_fields>-/bic/zlevel2 = lw_final-lvl2_nodname.
        <result_fields>-/bic/zlevel3 = lw_final-lvl3_nodname.
        <result_fields>-/bic/zlevel4 = lw_final-lvl4_nodname.
        <result_fields>-/bic/zlevel5 = lw_final-lvl5_nodname.
        <result_fields>-/bic/zlevel6 = lw_final-lvl6_nodname.
        <result_fields>-/bic/zlevel7 = lw_final-lvl7_nodname.
        <result_fields>-/bic/zlevel8 = lw_final-lvl8_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.

8 Comments
Labels in this area