Search code examples
ssasmdxcross-join

MDX - transpose rows to columns

I'm trying to display details from two rows into one using MDX. If I execute the MDX below, it returns 2 rows, one containing the 998 Key and one containing the 999 Key

SELECT NON EMPTY { 
  [Measures].[FactTableCount] } ON COLUMNS, 
NON EMPTY { ([DimXXXX].[XXXXKey].[XXXXKey].ALLMEMBERS 
  * ([DimAAAA].[AAAAKey].[AAAAKey],{[DimBBBB].[Key].&[998],[DimBBBB].[Key].&[999]},[DimCCCC].[CCCCKey].[CCCCKey])
  ) } ON ROWS 
FROM ( SELECT ( { [DimXXXX].[XXXXKey].&[MyValue] } ) ON COLUMNS 
FROM [FactTable])

It returns something like this 

    (columns [DimXXXX].[XXXXKey], [DimAAAA].[AAAAKey], [DimBBBB].[Key], [DimCCCC].[CCCCKey], [Measures].[FactTableCount])

MyValue, MyAAAAKey1, 998, MyCCCCKey1, 1

MyValue, MyAAAAKey2, 999, MyCCCCKey2, 1

However I want to return one row like this 

`(columns [DimXXXX].[Key], [DimAAAA].[AAAAKey], [DimAAAA].[AAAAKey], [DimBBBB].[Key], [DimBBBB].[Key], [DimCCCC].[CCCCKey], [DimCCCC].[CCCCKey], [Measures].[FactTableCount])

MyValue, MyAAAAKey1, MyAAAAKey2, 998, 999, MyCCCCKey1, MyCCCCKey2, 1

Among other things (such as using SETs, putting the 998/999 logic after the ROWS/COLUMNS, etc) I've tried

SELECT NON EMPTY { 
  [Measures].[FactTableCount] } ON COLUMNS, 
NON EMPTY { ([DimXXXX].[XXXXKey].[XXXXKey].ALLMEMBERS 
  * ([DimAAAA].[AAAAKey].[Key],[DimBBBB].[Key].&[998],[DimCCCC].[CCCCKey].[CCCCKey])
  * ([DimAAAA].[AAAAKey].[Key],[DimBBBB].[Key].&[999],[DimCCCC].[CCCCKey].[CCCCKey])
  ) } ON ROWS 
FROM ( SELECT ( { [DimXXXX].[XXXXKey].&[MyValue] } ) ON COLUMNS 
FROM [FactTable])

...however because the AAAAKey hierarchy is repeated I receive the error message "The AAAAKey hierarchy is used more than once in the Crossjoin function"

Is there a way to do do this ?

Solution

  • Based on your comment below I have a sample.Let me know if it works.

    I think I can see what you're saying however the measures are one thing, but the dimension values are another - say Record1:MyValue, MyAAAAKey1, 998, MyCCCCKey1, 2 and Record2: MyValue, MyAAAAKey2, 999, MyCCCCKey2, 5 - I would like to output MyValue, MyAAAAKey1, MyAAAAKey2, 998, 999, MyCCCCKey1, MyCCCCKey2, 2, 5

    So in the query below I am trying to simulate your problem. 

    select
{[Measures].[Internet Sales Amount]}
on columns,
non empty
([Customer].[City].[City],{[Product].[Category].&[1],[Product].[Category].&[3]},[Product].[Subcategory].[Subcategory])
on rows 
from [Adventure Works]

    Result enter image description here

    Now onw way is to take the changing values to columns, "{[Product].[Category].&1,[Product].[Category].&2}" in my case and "{[DimBBBB].[Key].&[998],[DimBBBB].[Key].&[999]}" in your case 

    select
{
({[Product].[Category].&[1],[Product].[Category].&[3]},[Measures].[Internet Sales Amount]),
([Product].[Category].defaultmember,[Measures].[Internet Order Quantity])
}
on columns,
non empty
([Customer].[City].[City],[Product].[Subcategory].[Subcategory])
on rows 
from [Adventure Works]

    Result: enter image description here

    Note how the values are only repeated for the relevent column. This does add an extra column but you rows are now half the orignal count.

    Edit: to handle the requirement based on the comment

    1st row of the grid would be Ballard, Bikes, Mountain Bikes, Road Bikes. The 2nd: Ballard, Clothing, Caps, Gloves. The 3rd: Barstow, Bikes, Road Bikes, null. I want to merge/list the actual dimension values

    So to achieve above we have two options. But in either case some manipulation on UI would be required. 1)First option

    with member 
measures.t 
as (nonempty(existing([Customer].[City].currentmember,[Product].[Category].currentmember,[Product].[Subcategory].[Subcategory].members),[Measures].[Internet Sales Amount])).item(0).item(2).name

member measures.t1
as (nonempty(existing([Customer].[City].currentmember,[Product].[Category].currentmember,[Product].[Subcategory].[Subcategory].members),[Measures].[Internet Sales Amount])).item(1).item(2).name
select
{measures.t,measures.t1}
on columns,
nonempty(([Customer].[City].[City],{[Product].[Category].&[1],[Product].[Category].&[3]}),[Measures].[Internet Sales Amount])
on rows 
from [Adventure Works]

    enter image description here

    2)The second option, 

    with member 
measures.t1
as 
[Customer].[City].currentmember.name

member measures.t2
as 
[Product].[Category].currentmember.name

member measures.t3 
as (nonempty(existing([Customer].[City].currentmember,[Product].[Category].currentmember,[Product].[Subcategory].[Subcategory].members),[Measures].[Internet Sales Amount])).item(0).item(2).name

member measures.t4
as (nonempty(existing([Customer].[City].currentmember,[Product].[Category].currentmember,[Product].[Subcategory].[Subcategory].members),[Measures].[Internet Sales Amount])).item(1).item(2).name

select
{measures.t1,measures.t2,measures.t3,measures.t4}
on columns,
nonempty(([Customer].[City].[City],{[Product].[Category].&[1],[Product].[Category].&[3]}),[Measures].[Internet Sales Amount])
on rows 
from [Adventure Works]

    enter image description here