Search code examples
ssasmdxolap

MDX to show Measure property

I'm familiar with how to get member properties into an MDX result-set: create a calculated member using WITH.

The problem is when the member whose properties I want is a measure, not a dimension member. Because the calculated member is created on the Measures hierarchy, I get the dreaded "The Measures hierarchy already appears in the Axis0 axis" error. Here's the query I'm running:

WITH MEMBER Measures.MeasureType AS  
Measures.CurrentMember.Properties('MEMBER_TYPE')
SELECT
MeasureType ON 0,
Measures.Members on 1
FROM TheCube

What I'm after is simply a list of all the measures ON 1 (this works, in itself); but with the measure's MEMBER_TYPE showing as the one column ON 0

Solution

  • You can't have members from the same hierarchy on both axes. There are two ways to get rid of this error.

    1. Create the calculated member on some other dimension

    WITH MEMBER [SomeDimension].[SomeHierarchy].MeasureType AS  
Measures.CurrentMember.Properties('MEMBER_TYPE')

SELECT
[SomeDimension].[SomeHierarchy].MeasureType ON 0,
Measures.Members on 1
FROM [TheCube]

    2. Have them in a set and not on different axes.

    WITH MEMBER Measures.MeasureType AS  
Measures.CurrentMember.Properties('MEMBER_TYPE')

SELECT
{Measures.MeasureType, Measures.Members} ON 0
FROM [TheCube]

    Discarding the second method as it gives a static value. What's really needed is a cross-tab value. So sticking with first method.