Search code examples
sql-serverssasmdxolap-cube

How to use UNION in MDX

I want to UNION the below MDX query. For these two queries measures and dimensions are different for the same date range. Please help me to get out of this.

SELECT NON EMPTY { [Measures].[Number of es2] } ON COLUMNS, 
 NON EMPTY { ([Date].[Year].[Year].ALLMEMBERS * [Date].[Month].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, 
 MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Date].[Date Hierarchy].[Date].&[20170101] : [Date].[Date Hierarchy].[Date].&[20180101] ) ON COLUMNS 
 FROM ( SELECT ( { [PracHistory].[Name].&[In] } ) ON COLUMNS FROM [Cube])) 
 WHERE ( [PracHistory].[Name].&[In] ) 



SELECT NON EMPTY { [Measures].[Number of es1] } ON COLUMNS, 
  NON EMPTY { ([Date].[Year].[Year].ALLMEMBERS * [Date].[Month].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, 
  MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Date].[Date Hierarchy].[Date].&[20170101] : [Date].[Date Hierarchy].[Date].&[20180101] ) ON COLUMNS 
  FROM ( SELECT ( { [Prac].[Pra atus].&[ We] } ) ON COLUMNS FROM [Cube])) 
  WHERE ( [Prac].[Pra atus].&[ We] )
Solution

  • MDX doesnt support Union, However there is away around it.

    1)You can write a query will show you on columns first "es2" for all values of "Pra atus" and "In" value for name. Then it will display "es1" for " we" value of "Pra atus" and all values of "Name". The columns will be side by side. The query will look as below. All values means the default value.

    SELECT NON EMPTY 
{ 
([Measures].[Number of es2],[PracHistory].[Name].&[In],[Prac].[Pra atus].[All]),
([Measures].[Number of es1],[PracHistory].[Name].[All],[Prac].[Pra atus].&[ We])
 } ON COLUMNS, 
NON EMPTY { ([Date].[Year].[Year] * [Date].[Month].[Month] ) } ON ROWS 
FROM [Cube]     
WHERE ( [Date].[Date Hierarchy].[Date].&[20170101] : [Date].[Date Hierarchy].[Date].&[20180101] )

    Sample for above 

    SELECT NON EMPTY 
{ 
([Measures].[Internet Sales Amount],[Product].[Category].&[1],[Customer].[Country].[All]),
([Measures].[Internet Order Quantity],[Product].[Category].[All],[Customer].[Country].&[United States])
} ON COLUMNS, 
non empty
{ ([Date].[Calendar Year].[Calendar Year], [Date].[Calendar Quarter of Year].[Calendar Quarter of Year]) } 
ON ROWS 
FROM [Adventure Works]   
WHERE ([Date].[Date].&[20130101]:[Date].[Date].&[20140101]  )

    enter image description here

    2)Now if you want it to Display in a single column, to emulate the behavior of SQL union then you will need a calculated measure that will select the value from one of the two measures .However please note that apart of the Date on rows you will also see "Name" and "Pra atus" on rows. The query will look like below.

    with member [Measures].[Number of esunion]
as 
case 
when 
[Product].[Category].currentmember is [Product].[Category].defaultmember then [Measures].[Internet Order Quantity]
when 
[Customer].[Country].currentmember is [Customer].[Country].defaultmember then [Measures].[Internet Sales Amount]
else null
end
SELECT NON EMPTY 
{ 
[Measures].[Number of esunion]
} ON COLUMNS,
non empty
{ 
([PracHistory].[Name].&[In],[Prac].[Pra atus].[All], [Date].[Year].[Year] , [Date].[Month].[Month]) ,
([PracHistory].[Name].[All],[Prac].[Pra atus].&[ We],[Date].[Year].[Year] , [Date].[Month].[Month])
} 
ON ROWS 
FROM [Cube]   
WHERE ([Date].[Date Hierarchy].[Date].&[20170101] : [Date].[Date Hierarchy].[Date].&[20180101]  )

    Sample for above 

    with member [Measures].[Number of esunion]
as 
case 
when 
[Product].[Category].currentmember is [Product].[Category].defaultmember then [Measures].[Internet Order Quantity]
when 
[Customer].[Country].currentmember is [Customer].[Country].defaultmember then [Measures].[Internet Sales Amount]
else null
end
SELECT NON EMPTY 
{ 
[Measures].[Number of esunion]
} ON COLUMNS,
non empty
{ 
([Product].[Category].&[1],[Customer].[Country].[All],[Date].[Calendar Year].[Calendar Year], [Date].[Calendar Quarter of Year].[Calendar Quarter of Year]) ,
([Product].[Category].[All],[Customer].[Country].&[United States],[Date].[Calendar Year].[Calendar Year], [Date].[Calendar Quarter of Year].[Calendar Quarter of Year])
} 
ON ROWS 
FROM [Adventure Works]   
WHERE ([Date].[Date].&[20130101]:[Date].[Date].&[20140101]  )

    enter image description here