Can Hierarchy Nodes be summarized in Bex?

[Former Member]

Hello experts,

We have a query the uses a CostElement (CE) hierarchy and CostCenter (CC) in the Rows area. The CE hierarchy has 3 levels; the CEs that are posted to are all on level 3. Levels 1 and 2 are used for grouping purposes and always end with "99".

Upon execution, the results are as follows:

Lvl | CE | CC | $

1 | 689999 | 2001 | 25

1 | 689999 | 2002 | 7

1 | 689999 | 2003 | 5

1 | 689999 | Result | 37

2 | 680099 | 2001 | 25

2 | 680099 | 2002 | 7

2 | 680099 | 2003 | 5

2 | 680099 | Result | 37

3 | 680020 | 2001 | 25

3 | 680020 | 2002 | 7

3 | 680020 | 2003 | 5

3 | 680020 | Result | 37

The desired result is:

Lvl | CE | CC | $

1 | 689999 | Result | 37

2 | 680099 | Result | 37

3 | 680020 | 2001 | 25

3 | 680020 | 2002 | 7

3 | 680020 | 2003 | 5

3 | 680020 | Result | 37

In other words, we want to summarize the nodes at levels 1 and 2 since they are not posted to directly. This will eliminate redundant entries of CostCenters at levels 1 and 2.

We have tried several options using Structures vs. Hierarchy and workbook formating but are unsuccessful so far.

Any suggestions are greatly appriciated!

Regards,

Adam

[Former Member · Accepted solution]

Maybe you want to display the rows as hierarchies. Right click on the grey bar that contains the word Rows. Choose Display as hierarchy. You may want to choose a starting expand level.

Best regards

Dirk

[Former Member · Accepted solution]

Dirk,

This worked perfectly! Thanks for the help!

As a follow up, are you aware of a way to make the CE Hierarchy display with Position of Lower-Level Node = Up (same as Node Position = Below) whenit is included in a row hierarchy?

We want lower level nodes to display first. This setting is typically set in the properties for the characteristic (Position of Lower-Level Node = Up). But when displaying rows as a hierarchy, results are displayed with higher level nodes first by default (like Position of Lower-Level Node = Down). Using the right click to change node position of CE hierarchy has no affect.

This may be a limitation when combining a Characteristic Hierarchy with a Rows hierarchy, but any suggestions are appreciated.

Thanks,

Adam

[Former Member · Accepted solution]

You may try Query Properties -> Display -> Position of Results -> Up/Right (or Up/left).

Best regards

Dirk