CODEWITHSUNDEEP
sql-serverssasmdx
Darek.K
Darek.K

Reputation: 128

MDX Columns as rows

I have a MDX query which gives me the list of different types of values (measures) over the years:

SELECT {
    [Calendar].[Year].[Year]    
} ON ROWS,
{
    Measures.ValueA,
    Measures.ValueB,
    Measures.ValueC
} ON COLUMNS
FROM [MyCube]

It produces the following result:

Year | ValueA | ValueB | ValueC 
2005 |   5    |   10   |   7
2006 |   7    |   12   |   3
(...)

I would like to treat the measures as columns to get the following result:

Year | Value  | Quantity
2005 | ValueA |    5
2005 | ValueB |   10
2005 | ValueC |    7
2006 | ValueA |    7
2006 | ValueB |   12
2006 | ValueC |    3

The set of values is constant, so I was trying the following approach:

WITH
SET [ValueIds] AS { [1], [2], [3] }

MEMBER [Quantity] AS
CASE 
    WHEN [ValueIds].CurrentMember.MEMBERVALUE = 1 THEN Measures.ValueA
    WHEN [ValueIds].CurrentMember.MEMBERVALUE = 2 THEN Measures.ValueB
    WHEN [ValueIds].CurrentMember.MEMBERVALUE = 3 THEN Measures.ValueC
END

SELECT {
    [Calendar].[Year].[Year]
    * [ValueIds]
} ON ROWS,
{
    [Quantity]
} ON COLUMNS
FROM [MyCube]

However there was no luck as "CurrentMember" was unrecognized in such context...

Upvotes: 1

Views: 1487

Answers (2)

whytheq
whytheq

Reputation: 35557

I've used an extra dimension in the same way as Danylo and I'm guessing you're wanting to rename some measures but I could be wrong:

WITH
  MEMBER Measures.[1] AS Measures.ValueA
  MEMBER Measures.[2] AS Measures.ValueB
  MEMBER Measures.[3] AS Measures.ValueC
SELECT 
    [ExtraDimension].[ExtraHierarchy].[ExtraLevel].[All]  ON 0,
    [Calendar].[Year].[Year]
    * {
       Measures.[1],
       Measures.[2],
       Measures.[3]
      } ON 1
FROM [MyCube];

Upvotes: 2

Danylo Korostil
Danylo Korostil

Reputation: 1484

You have to set anything into columns anyway, put there any All member from any other hierarchy:

select
 [ExtraDimension].[ExtraHierarchy].[ExtraLevel].[All] on 0,
 [Calendar].[Year].[Year].Member * {[Measures].[ValueA],[Measures.ValueB],[Measures.ValueC]} on 1    
from [MyCube]

Upvotes: 0

Related Questions