Calculated Member Dimension Group

Question

I'm pretty new to MDX, so this may be a simple thing...

I have a Dimension that has members like 'Type A', 'Type B', 'Type C', 'Type D'. I want to create a dimension group for these so that:

• 'Type A' and 'Type B' would be in a group of 'Type A/B'
• 'Type C' would be in a group of 'Type C'
• 'Type D' would be in a group of 'Other'

Here is what I have so far:

WITH MEMBER [NewHierarchy].[Type Group].[Type Group Name] AS
    CASE
        WHEN 
            [Type Hierarchy].[Types].CurrentMember = [Type Hierarchy].[Types].&[Type A]
        THEN
            'Type A/B'
        WHEN 
            [Type Hierarchy].[Types].CurrentMember = [Type Hierarchy].[Types].&[Type B]
        THEN
            'Type A/B'
        WHEN 
            [Type Hierarchy].[Types].CurrentMember = [Type Hierarchy].[Types].&[Type C]
        THEN
            'Type C'
        ELSE
            'Other'
    END

select
    NON EMPTY {
        [Measures].[Type Count],
    } on COLUMNS,
    NON EMPTY {
        [NewHierarchy].[Type Group].[Type Group Name] *
        [Type Hierarchy].[Types].[Types].AllMembers
    } on ROWS
from
    [MyCube]

I'm getting an error of:

Query (8, 6) The dimension '[NewHierarchy]' was not found in the cube when the string, [NewHierarchy].[Type Group].[Type Group Name], was parsed.

How can this be accomplished in an MDX query? Thanks in advance!

Answer 1

You cannot generate new hierarchies via MDX. But you can define new members on existing hierarchies like this:

WITH Member [Type Hierarchy].[Types].[Type A/B] AS
            Aggregate({
                       [Type Hierarchy].[Types].&[Type A],
                       [Type Hierarchy].[Types].&[Type B]
                     })
     Member [Type Hierarchy].[Types].[Other] AS
            [Type Hierarchy].[Types].&[Type D]
select
    NON EMPTY {
        [Measures].[Type Count],
    } on COLUMNS,
    NON EMPTY {
        [Type Hierarchy].[Types].[Type A/B],
        [Type Hierarchy].[Types].&[Type C],
        [Type Hierarchy].[Types].[Other]
    } on ROWS
from
    [MyCube]  

Please note that you need to apply Aggregate on a set if you want to have a single member.

And another remark: Never use = to compare for "is the same member as". = tests if the numeric values of the current measure are the same. You would use IS to compare for member identity.