Display upper level hierarchy values in one row with leafs

[robert_tullius]

Hello,
 
I am facing a hierarchy problem that I was not able to solve yet. I need to display the levels of a hierarchy in some kind of flat view.
 
First of all: we are using Webi 4.0 SP5 Patch 7 with BICS interface on SAP BW.
 
Here's an example of I try to achieve:
 
Hierarchy:

Europe

   Germany

      Frankfurt

      Berlin

      Stuttgart

   France

      Paris

Asia

   China

      Bejing

      Hong-Kong

Desired result as table in a Webi report:

City         Continent

Frankfurt    Europe

Berlin       Europe

Stuttgart    Europe

Paris        Europe

Bejing       Asia

Hong-Kong    Asia

My problem: "City" and "Continent" are just linked via the hierarchy. "Previous" does not help, as the distance between the hierarchy leafs and level 1 is not constant. ".parent" is only available to change the context of a calculation.
 
My perfect solution would be "CITY.parent.parent"...

Is there any solution for this request?
 
Regards,
Robert

[Former Member · Accepted solution]

Hi,

Im not optimistic that you can achieve that.

I remember that had a very similar requirement and ended displaying separate InfoObjects in the Web Intelligence report, but I cannot find that particular post right now, hopefully he will drop by.

Best regards,

Victor

PS: Skipping levels of a Hierarchy is a feature that Analysis for Office can do out of the box.

[Former Member · Accepted solution]

Hi Victor

Here is the Link, Please check it out:

http://scn.sap.com/thread/3288822

@Robert As i mentioned in the thread, Create separate objects and link them using ABAP code (if then else). This way you can display the Level 1 with Level 2(parent node with Children).

Thanks,

Parthiban

[robert_tullius · Accepted solution]

Hi,

I remember this thread. I read it earlier. However, this is a requirement for just one single report and I was hoping to find a solution outside the backend system. The infoobjects for Country and Continent are not part of the staging in our system.

However, I might add "Country" and "Continent" to the attributes of the City-Infoobject and fill it from the DSO that our hierarchy is dynamically created from.

@Victor: thanks for the AA Office advice. Unfortunately we only provide this tool for selected users.

Regards,

Robert

[robert_tullius · Accepted solution]

Hi again,

I managed to calculate the distance between a leaf and its level 1 hierarchy item. That got me a table like this:

[Geography]       [Distance]

Europe

  Germany

     Frankfurt    2

     Berlin       3

     Stuttgart    4

  France

     Paris        6

Asia

  China

     Bejing       2

     Hong-Kong    3

Now I would have used this value as offset inside previous to get the continent of a city:

=Previous([Geography]; [Distance])

Unfortunately the previous-function seems not to allow a variable as offset parameter

Any ideas?

Regards,

Robert

[Former Member · Accepted solution]

Try the below and see if it works

RelativeValue([Geography];([Distance]);-1)

Thanks

Mallik

[robert_tullius · Accepted solution]

Hi,

RelativeValue does not work as well. It has the same problem and does not allow a variable to be used for the offset-parameter.

Regards,

Robert

[james_batchelor2]

I have struggled with this as well.  It is unfortunate Webi doesn't have a Hierarchy Level n filter ability like Analysis for Office.  I ended up building individual tables filtered to the lowest level of each node and arranging them to look like one table. The users wanted the top nodes under the children and that row be a different color than the children rows.  These are individual tables filtered on the  parent node.  Problem there is any additions/changes made on the hierarchy will not come through to the report without manually adjust each tables' filters.  But it gave the ability to have custom formatting for each node level and bring in just the level needed for the one section.

jim

[Former Member]

Hello Robert,

U can use class cl_gui_alv_tree_simple and in fieldcat u can give position of node and its sub nodes

or if u want simple u can use 'REUSE_ALV_HIERSEQ_LIST_DISPLAY'

I am sending u code for this , u can check it

*---------------------------------------------------------------------*
*     Type-POOL
**---------------------------------------------------------------------*
TYPE-POOLS: slis.
**---------------------------------------------------------------------*
*     Internal Tables
**---------------------------------------------------------------------*
DATA: BEGIN OF it_vbak OCCURS 0,
       vbeln LIKE vbak-vbeln,     " Sales Document
       ernam LIKE vbak-ernam,     " Name of Person who Created the Object
       erdat LIKE vbak-erdat,     " Date on Which Record Was Created
       audat LIKE vbak-audat,     " Document Date (Date Received/Sent)
       var1,
       END OF it_vbak.

DATA: BEGIN OF it_vbap OCCURS 0,
       vbeln LIKE vbap-vbeln,     " Sales Document
       posnr LIKE vbap-posnr,     " Sales Document Item
       matnr LIKE vbap-matnr,     " Material Number
       charg LIKE vbap-charg,     " Batch Number
       END OF it_vbap.

DATA: fldcat TYPE slis_t_fieldcat_alv WITH HEADER LINE,
       layout TYPE slis_layout_alv,
       key    TYPE slis_keyinfo_alv.

DATA: it_header1 TYPE slis_t_listheader.
DATA: wa_header1 TYPE slis_listheader.
DATA: it_eve1    TYPE slis_t_event,
       wa_eve1    TYPE slis_alv_event.

**---------------------------------------------------------------------*
*     Main Program
**---------------------------------------------------------------------*
SELECT vbeln
        ernam
        erdat
        audat FROM vbak
              INTO CORRESPONDING FIELDS OF TABLE it_vbak
                UP TO 20 ROWS.

if it_vbak[] is not initial.

SELECT vbeln
        posnr
        matnr
        charg FROM vbap
              INTO CORRESPONDING FIELDS OF TABLE it_vbap
                UP TO 20 ROWS.

endif.

PERFORM fieldcatalog.
PERFORM layout.
PERFORM list.
PERFORM header.
*&---------------------------------------------------------------------*
*&      Form  fieldcatalog
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fieldcatalog .
   fldcat-fieldname = 'VBELN'.
   fldcat-tabname = 'IT_VBAK'.
   fldcat-ref_fieldname = 'VBELN'.
   fldcat-ref_tabname = 'VBAK'.
   fldcat-seltext_m = 'SALES DOC.'.
   fldcat-col_pos = 0.
   fldcat-key = 'X'.
   APPEND fldcat.
   CLEAR fldcat.

   fldcat-fieldname = 'ERNAM'.
   fldcat-tabname = 'IT_VBAK'.
   fldcat-ref_fieldname = 'ERNAM'.
   fldcat-ref_tabname = 'VBAK'.
   fldcat-seltext_m = 'NAME OF PERSON'.
   fldcat-col_pos = 1.
   APPEND fldcat.
   CLEAR fldcat.

   fldcat-fieldname = 'ERDAT'.
   fldcat-tabname = 'IT_VBAK'.
   fldcat-ref_fieldname = 'ERDAT'.
   fldcat-ref_tabname = 'VBAK'.
   fldcat-seltext_m = 'RECORD DATE'.
   fldcat-col_pos = 2.
   APPEND fldcat.
   CLEAR fldcat.

   fldcat-fieldname = 'AUDAT'.
   fldcat-tabname = 'IT_VBAK'.
   fldcat-ref_fieldname = 'AUDAT'.
   fldcat-ref_tabname = 'VBAK'.
   fldcat-seltext_m = 'DOCUMENT DATE'.
   fldcat-col_pos = 3.
   APPEND fldcat.
   CLEAR fldcat.

   fldcat-fieldname = 'POSNR'.
   fldcat-tabname = 'IT_VBAP'.
   fldcat-ref_fieldname = 'POSNR'.
   fldcat-ref_tabname = 'VBAP'.
   fldcat-seltext_m = 'SALES DOC ITEM'.
   fldcat-col_pos = 4.
   APPEND fldcat.
   CLEAR fldcat.

   fldcat-fieldname = 'MATNR'.
   fldcat-tabname = 'IT_VBAP'.
   fldcat-ref_fieldname = 'MATNR'.
   fldcat-ref_tabname = 'VBAP'.
   fldcat-seltext_m = 'MATERIAL NO'.
   fldcat-col_pos = 5.
   APPEND fldcat.
   CLEAR fldcat.

   fldcat-fieldname = 'CHARG'.
   fldcat-tabname = 'IT_VBAP'.
   fldcat-ref_fieldname = 'CHARG'.
   fldcat-ref_tabname = 'VBAP'.
   fldcat-seltext_m = 'BATCH NUMBER'.
   fldcat-col_pos = 6.
   APPEND fldcat.
   CLEAR fldcat.
ENDFORM.                    " fieldcatalog
*&---------------------------------------------------------------------*
*&      Form  layout
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM layout .
   layout-expand_fieldname = 'VAR1'.

   key-header01 = 'VBELN'.
   key-item01 = 'VBELN'.

   CLEAR wa_eve1.
   wa_eve1-name = 'TOP_OF_PAGE'.
   wa_eve1-form = 'TOP-OF-PAGE1'.
   APPEND wa_eve1 TO it_eve1.

ENDFORM.                    " layout
*&---------------------------------------------------------------------*
*&      Form  LIST
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM list .
   CALL FUNCTION 'REUSE_ALV_HIERSEQ_LIST_DISPLAY'
     EXPORTING
       i_callback_program = sy-repid
       is_layout          = layout
       it_fieldcat        = fldcat[]
       it_events          = it_eve1[]
       i_tabname_header   = 'IT_VBAK'
       i_tabname_item     = 'IT_VBAP'
       is_keyinfo         = key
     TABLES
       t_outtab_header    = it_vbak
       t_outtab_item      = it_vbap
     EXCEPTIONS
       program_error      = 1
       OTHERS             = 2.
*IF sy-subrc 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
*ENDIF.

ENDFORM.                    " LIST
*&---------------------------------------------------------------------*
*&      Form  header
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form header .
   wa_header1-typ = 'H'.
   wa_header1-info = 'Hierarchical Display'.
   APPEND wa_header1 TO it_header1.
   CLEAR wa_header1.

   CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
     EXPORTING
       it_list_commentary = it_header1[].
   REFRESH it_header1.
endform.                    " header

[robert_tullius]

Hi,

I want to solve this issue in Webi, not in SAP BW.

Regards,

Robert

[Former Member]

Hi Robert,

Agree with Victor that you may not be able to do this using Hierarchy object and set functions, but can  acheive it with City and Continent seperate  Infoobjects.

One idea I can think of is try using Descendants([Geography].[Continent];[Geography].[City],Self_Before) and try to extract the first value using Substr and Pos functions.

Thanks

Mallik

[jyothirmayee_s]

Hi,

If you have any corresponding key figures which need to show Parent as well as the sum(Parent) then Victor suggestion on using Parthiban solution might work well.

But if you know that you always wanted to show the level 0 whcih would be Parents of a hierarchy then you can try

if([hiername].depth =0) then

[hiername]

Thanks,

Jothi

[robert_tullius]

Hi,

thanks for the Descendants-idea, but as far as I understand it, it can only be used to change the calculation context for functions like sum, max, ... .

I don't know whether this is relevant or not, but our hierarchy for this case is quite large and also changes it's structure from time to time. Therefore the solution needs to work dynamically.

I also tried to calculate the distance between a leaf and its next "level 1 hierarchy node" to use the previous function, but it didn't work out.

Regards,

Robert