Analysis hierarchy: compact view in rows but different columns for two characteristics

[Former Member]

Dear all,

we have a requirement to show a hierarchy in combination with one or two characteristics. The standard view in Bex and Analysis for Office (Excel) is like this:

Country (Hierarchy)	Customer	Revenue
Europe	A	10
	B	20
	C	30
DE	A	10
	B	20
FR	C	30

With the option "compact view in rows" it looks like this:

Country (Hierarchy)	revenue
Europe	60
DE	30
A	10
B	20
FR	30
C	30

But our requirement is to show a combination of those options. The reason is to both eliminate the characteristic values for top level nodes and show the second characteristic in an own column.

Country (Hierarchy)	Customer	revenue
Europe		60
DE	A	10
	B	20
FR	C	30

Is it possible to realize the last table in Analysis for Office (Excel) in combination with a standard Bex Query.

Thanks for your help.

Kind regards

Thomas

[Former Member]

Hi Thomas,

what I understand from your example, I would say: Check the AO help once, path:

"Analyzing Data" - "Working with hierarchies" - "To display single dimensions as hierarchy"

If you use multiple dimensions in your analysis, you can display them as flat presentation or as hierarchy in the crosstab.

Procedure
1) Move at least two dimensions to the Rows section in the design panel.
The dimensions are displayed side by side in the crosstab.

2) Change to hierarchical display.
Select a crosstab cell and choose Start of the navigation path Hierarchy Next navigation step Compact Display in Rows End of the navigation path.
The dimensions in the crosstab are displayed as hierarchy. The first dimension in the Rows section is the first level in the hierarchy. Nodes with a + contain subnodes.

....

Note: Dimensions can also be moved to the Columns section. This procedure describes dimension in the Rows section. Dimensions in columns behave accordingly.

At the beginning you need to experiment a little bit, but you will find out soon.

If this is not what you want, then you probably need to determine the characteristics & values via VBA code and build your own hierarchical display on a second sheet.

Best regards,

Martin

[TammyPowlas]

Thomas - which version of Analysis for Excel are you using?

[Former Member]

We are using Analysis for Office 1.4 SP11.

BEx: SAP GUI BI Addon 7.3 SP9.

[TammyPowlas]

Hi Thomas - thank you for this

I am a little confused; in the first image you have Customer A at the Europe level; is that possible? Would you please clarify?

[Former Member]

This is standard behaviour in Bex and Afo. If you have customer "A" a part of country "DE" it is also shown on the top level node Europe if you drill down the characterisitc 0CUSTOMER after 0COUNTRY.