Present two different dimensions in MDX as hierarchy

Question

I have two different dimensions i want to present in MDX Query with drilldown

Dim 1:

Country, CountryId

Italy,1

Mexico,2

England,3

dim 2:

Product, ProductId

Shoes,1

Shirts,2

Bags,3

Fact_Data:

Country,Product,Amount

1,2,33

3,1,50

2,3,64

1,1,100

3,1,70

2,2,44

when i join the dimensions in MDX Query:

select [Measures].[SaleAmount] on 0, crossjoin([Country].[Country].[Country].[Italy],[Product].[Product].[Product].[Product]) on 1 from [cube]

the data presented will be:

italy   Shoes  100

        Shirts 33

        Bags   0

Mexico  Shoes  0

        Shirts 44

        Bags   64
...

And i want to present the data with drilldown option from country to product

+italy   133
+Mexico  108
+England 120

I'm using icCube

Sorry, can't understand. Could you create an example with in-memory data and add it as example ? — ic3

ic3 ic3 · Accepted Answer · 2016-12-05T16:50:39

The complicated part is solving this in the schema, two options there

Create a new dimension that is a crossjoin dim1*dim2, that defines a multiple value key for the last level + remove name unique on the last level. You could remove Dim1 and Dim2 and define them as hierarchies in Dim1Dim2 newly created dimension.
Use categories, for now it's a bit brute you've to create one by one member (contact us directly if you want to go this way).

The easy and recommended way, use the Navigation settings to define the drilldown strategy in the reporting at chart level.