IcCube - Treemap with Parent/Child dimension

Question

I currently try to build a google treemap chart in IcCube. Do I do this using a Multi Level dimension and defining every Level of drilldown separately (as shown here and here), everything works fine. What I would like to do is using a Parent/Child dimension and telling the treemap, just to go down this hierarchy when drilling into the treemap chart.

Is that possible?

Here is the (not working) MDX I used, where [categories] is the Parent/Child dimension:

WITH
  MEMBER [category_name] as [categories].[categories].currentmember.name 
SELECT
  {[category_name],[Measures].[count_clicks]} on 0,
  non empty [categories].[categories] on 1
FROM
  [Cube]

Answer 1

Treemap is a tricky visualization. You need to define the two first columns as defining the parent/child relation. For example :

'Global',    null 
'America',   'Global' 
'Europe',    'Global'
'Brazil',    'America'

The first column is by construction the Axis(1) name, so the seconds has to be the name of the parent and not the name of the member. Something like :

MEMBER [parent_name] as IIF( [categories].[categories].currentmember is [categories].[categories].[ALL],
                            NULL,
                            [categories].[categories].currentmember.parent.name )

That you can use as :

WITH
  MEMBER [parent_name] as IIF( [categories].[categories].currentmember is [categories].[categories].[ALL],NULL,[categories].categories].currentmember.parent.name )
SELECT
  {[parent_name],[Measures].[count_clicks]} on 0,
  non empty [categories].[categories] on 1
FROM
  [Cube]

This should work better.

Hope it helps.

Answer 2

After struggling for a while with my own data, I took the IcCube example of the parent/child Dimension (http://www.iccube.com/support/documentation/user_guide/schemas_cubes/dim_parentchild.php). Then I tried your example:

WITH
  MEMBER [parent_name] as IIF( [dim (ALL)].[Hierarchy].currentmember 
  is  [dim (ALL)].[Hierarchy].[ALL],'',
  [dim (ALL)]. [Hierarchy].currentmember.parent.name )
SELECT
  {[parent_name],[Measures].[value]} on 0,
  non empty [dim (ALL)].[Hierarchy] on 1
FROM
  [Cube]  

(The NULL value had to be replaced by '')

The first dimension works, but if I try to drill into the map, the system tells me, that there is no row with id 'World'. Do you have any ideas, how to fix that?

EDIT: After using real names in the parent/child dimension instead of ids (child: Spain; parent: Europe, value: 3) and replacing [dim (ALL)].[Hierarchy] with [dim (ALL)].[Hierarchy].members() in the code, I got a treemap working so far:

WITH
  MEMBER [parent_name] as IIF( [dim (ALL)].[Hierarchy].currentmember 
  is [dim (ALL)].[Hierarchy],'',
  [dim (ALL)].[Hierarchy].currentmember.parent.name )
SELECT
  {[parent_name],[Measures].[value]} on 0,
  non empty [dim (ALL)].[Hierarchy].members() on 1
FROM
  [Cube]

Now after drilling down to the next level, the is an error message 'failed to find row with id "Europe"'. And I added a 3 Level into my data (child: Madrid; parent: Spain, value: 5), but I am unable to go to this level. Clicking on "Spain" doesn't change anything.