Using Hierarchies for Data Selection

When processing transactions one may have a need for selecting records belonging to certain hierarchy nodes or levels. For example, if we are processing transaction lines for Profit and Loss statements we want to select only those Cost Elements that belong to a certain hierarchy node. There is no standard way in SAP BW for selecting records in the DTP or in the ABAP start routine based on infoobject hierarchies. However, if we design a query based on the hierarchy masterdata we can save query results to a Direct Update DSO. Later we can use records from this DSO as a filter. Below I will go through the steps one has to follow in order to implement this approach.

Create Master Data Query with Relevant Filters

In SAP BW it is only in the query we can do selections by hierarchy nodes. If we set a filter by Cost Element hierarchy node we can load query result to a DSO. Let’s say we have to select all nodes for Cost Element node Z922X-270 with some Cost Elements to be used as exceptions. Here is how query filter will look like:

Create Direct Update DSO with an APD for Hierarchy Nodes

DSO for master data elements derived from the hierarchy may contain only one infoobject. In the case of cost element here is an example:

As soon as Cost Element hierarchy is refreshed we have to update the filter in the corresponding DSO: P&L Cost Elements. This can be automated with an APD. Analysis Process Designer may be very simple:

Apply Filter in the Start Routine

The following code can be used in the start routine of a transformation in order to filter out records by Cost Element hierarchy nodes:

  FIELD-SYMBOLS:  <ls> TYPE _ty_s_SC_1.

  TYPES: t_ce TYPE /BIC/AO_CEPNL00.

  DATA: ce TYPE HASHED TABLE OF t_ce WITH UNIQUE KEY COSTELMNT.

* Load list of Valid Cost Elements

  SELECT * FROM /BIC/AO_CEPNL00 INTO TABLE ce.

* Filter by Cost Element - leave only CE hierarchy node 270

  LOOP AT SOURCE_PACKAGE ASSIGNING <ls>.

    READ TABLE ce WITH TABLE KEY COSTELMNT = <ls>-COSTELMNT TRANSPORTING NO FIELDS.

    IF sy-subrc <> 0.

      DELETE SOURCE_PACKAGE.

    ENDIF.

  ENDLOOP.