iccube create category member selecting from a hierarchy, excluding from another one

Question

Using IcCube, I need to create « pre-selections » that I’ll include in the reports so that my users they don’t need to go selecting many many parameters to get their most used selections.

I created a STATS Dimension « GroupMaladies » containing 1 Hierarchy « Maladies ».

Using the folowing commands, I can create category members (in advanced scripts of cube) that I can use after in my reports…

(multiple codes from same hierarchy, same level)

   CREATE CATEGORY MEMBER [GroupMaladies].[Maladies].[Toutes].[ group 1] as {
     [Classification GHM].[DA_GP_GA_GHM].[All-M].&[D09].&[X08].&[G078],
    [Classification GHM].[DA_GP_GA_GHM].[All-M].&[D19].&[X15].&[G079] }

(multiple codes from same hierarchy, different levels)

CREATE CATEGORY MEMBER [GroupMaladies].[Maladies].[Toutes].[ Group 2] as { 
[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D02].&[C09].&[G024],
[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D02].&[X05],
[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D01].&[X02]}

(multiple codes from multiple dimensions)

CREATE CATEGORY MEMBER [GroupMaladies].[Maladies].[Toutes].[ Group3 <18] as { 
[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D12].&[C17].&[G106]}  *  {[Tranche Age].[Tranche Age].[Tranche].[< 18 ans]}

(A whole hierarchy minus 1 or some of it’s components)

CREATE CATEGORY MEMBER [GroupMaladies].[Maladies].[Toutes].[Group 4] as 
except({[Classification GHM].[DA_GP_GA_GHM].[All-M]}, 
       {[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C19].&[G127].&[11C05]})

Now, I would like to use a whole hierarchy , but exclude from it some values from another hierarchy of the same Dimension :

CREATE CATEGORY MEMBER [GroupMaladies].[Maladies].[Toutes].[Group X] as 
[Classification GHM].[ASO].[All-M].&[C]
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C19].&[G127].&[11C05] 
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C19].&[G127].&[11C11] 
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C19].&[G127].&[11C13]
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C20].&[G128].&[12C04]

But this last scenario is not working… Anyone can help on this ?

Answer 1

In icCube you have a set of functions for building categories that allow for basic set operations on facts (or subcubes).

Your last MDX statement :

CREATE CATEGORY MEMBER [GroupMaladies].[Maladies].[Toutes].[Group X] as 
[Classification GHM].[ASO].[All-M].&[C]
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C19].&[G127].&[11C05] 
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C19].&[G127].&[11C11] 
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C19].&[G127].&[11C13]
-[Classification GHM].[DA_GP_GA_GHM].[All-M].&[D15].&[C20].&[G128].&[12C04]

is the same as

CREATE CATEGORY MEMBER [GroupMaladies].[Maladies].[Toutes].[Group X] as 
[Classification GHM].[ASO].[All-M].&[C]

Why ?

The minus is applied between a set of members - using the Except function - and not applied to facts or subcubes as we're looking for.

Solution

So you're looking for the Minus operation on subcubes :

Minus can be performed with the SubcubeMinus functions. Feel free to check other set operations as intersect, union, complement and symmetrical difference.