CODEWITHSUNDEEP
sql-serverssasmdx
Josh Jay
Josh Jay

Reputation: 1250

Calculated Member Dimension Group

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:

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!

Upvotes: 0

Views: 72

Answers (1)

FrankPl
FrankPl

Reputation: 13315

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.

Upvotes: 1

Related Questions