Search code examples
sql-serverssasmdxolap

(MDX) how to use current member name as another column?

Example MDX query from https://quartetfs.com/resource-center/mdx-query-basics:

SELECT
NON EMPTY {[ASIN].[ASIN].Members} ON ROWS,
NON EMPTY {[Category].[Category].[LCD]} ON COLUMNS 
FROM [Amazon] 
WHERE ( [Measures].[Gross.Profit], 
        [Time].[ALL].[AllMember].[2011].[5], 
        [Brand].[Brand].[LG])

results table

How could one repeat the ASIN field (pink column) in another column?

I tried adding [ASIN].[ASIN] to ON COLUMNS expression 

SELECT
NON EMPTY {[ASIN].[ASIN].Members} ON ROWS,
NON EMPTY {[Category].[Category].[LCD],[ASIN].[ASIN]} ON COLUMNS 
FROM [Amazon] 
WHERE ( [Measures].[Gross.Profit], 
        [Time].[ALL].[AllMember].[2011].[5],
        [Brand].[Brand].[LG])

Which resulted in Two sets specified in the function have different dimensionality. Adding .CurrentMember resulted in the same error.

I tried adding ASIN property through new measure:

MEMBER Measures.ASIN AS [ASIN].[ASIN].CurrentMember
SELECT
NON EMPTY {[ASIN].[ASIN].Members} ON ROWS,
NON EMPTY {[Category].[Category].[LCD],Measures.ASIN} ON COLUMNS 
FROM [Amazon] 
WHERE ( [Measures].[Gross.Profit],
        [Time].[ALL].[AllMember].[2011].[5], 
        [Brand].[Brand].[LG])

Which adds a new column with null values.

What I want to see is:

______________LCD________ASIN__________
B003D4WAVW      124,420.16      B003D4WAVW      
...

Is there a way to achieve this?

Solution

  • Try this one:

    MEMBER Measures.ASIN AS [ASIN].[ASIN].CurrentMember.Member_Name
MEMBER Measures.LCD AS ([Category].[Category].[LCD],[Measures].[Gross.Profit])
SELECT
NON EMPTY {[ASIN].[ASIN].Members} ON ROWS,
NON EMPTY {[Measures].[LCD],[Measures].[ASIN]} ON COLUMNS 
FROM [Amazon] 
WHERE ( [Time].[ALL].[AllMember].[2011].[5], 
        [Brand].[Brand].[LG])

    You tried to use dimension and measure member on the same axis. I've transformed this into two measures.

    Tested on my own data: DimensionAndMeasureOnOneAxis