如何在MDX中使用UNION [英] How to use UNION in MDX

问题描述

我要UNION下面的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] )

推荐答案

MDX不支持Union，但是周围没有.

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

1)您可以编写一个查询，该查询将在列"es2"的第一个字段中显示"Pra atus"和"In"值的所有值.然后，它将为"Pra atus"的"we"值和"Name"的所有值显示"es1".列将并排.查询将如下所示.所有值表示默认值.

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

以上样本

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 Union的行为，则需要一个计算得出的度量，该度量将从这两个度量之一中选择一个值.在行上显示日期时，行上还会显示名称"和"Pra atus".该查询将如下所示.

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

以上样本

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

这篇关于如何在MDX中使用UNION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！