How to go from rows to a hierarchy in icCube

Question

I want to create a one-column hierarchy from multiple columns using mdx. I got this result:

from the Sales cube with this mdx:

SELECT
{ {[Measures].[Amount]} } ON COLUMNS,
{ [Customers].[Geography].firstNotAllLevel().allmembers }*{ [Product].[Product].[Category].allmembers }*{ [Time].[Quarter].firstNotAllLevel().allmembers } ON ROWS
 FROM [Sales]

I want the first three columns to form a hierarchy that looks like this:

Is this possible in icCube using mdx? If it is, can you show me how?

Answer 1

Using standard MDX you can't solve the problem as you can not navigate across hierarchies on a single MDX statement.

What we're looking for is to define our own hierarchy for navigation. Using icCube you've two different ways :

1) You can use Categories to define at will a new hierarchy. Check documentation (categories-howto, categories-doc and categories-example ). Bear in mind that for the new hierarchy the parent / child relation is just visual and not data related (e.g. parent is the sum of his children)

2) You can define your navigation logic using the reporting tool. But this has his limitations in the current version 5.1 (most probably we will fix for 5.1.1)