如何在 MDX 中使用 UNION

我想要UNION下面MDX询问。对于这两个查询，同一日期范围的度量和维度是不同的。请帮助我摆脱困境。

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] )

MDX 不支持 Union，但是有一种解决方法。

1）您可以编写一个查询，该查询将在第一个“es2”列上显示“Pra atus”的所有值和名称的“In”值。然后它将显示“Pra atus”的“we”值和“Name”的所有值“es1”。这些列将并排放置。查询如下所示。所有值均表示默认值。

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] ) 

上述示例

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]  ) 

2）现在，如果您希望它显示在单列中，以模拟 SQL 联合的行为，那么您将需要一个计算度量，该度量将从两个度量之一中选择值。但是请注意，行上的日期除外您还会在行上看到“Name”和“Pra atus”。查询如下所示。

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]  ) 

上述示例

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]  )