sql-server-2008-r2 - 按层次结构的多个成员过滤

Question

假设我有这个 MDX 查询:

select 
    NON EMPTY {([Dim 1].[Member 1].[Member 1])} on columns
    ,NON EMPTY {([Dim 2].[Member 2].[Member 1])} on rows
from [Cube 1]
where
{(
    [Dim 3].[Hierarchy 3].[Member 3].&[Value 3]
    ,[Measures].[Measure 1]
)}

但现在我还需要过滤，就像我对 [Dim 3].[Hierarchy 3].[Member 3].&[Value 3] 所做的那样，但是有几个成员值来自不同维度的层次结构，让我们称它们为:

[Dim 4].[Hierarchy 4].[Member 4].&[Value 2]
[Dim 4].[Hierarchy 4].[Member 4].&[Value 4]
[Dim 4].[Hierarchy 4].[Member 4].&[Value 8]

我已经试过了:

select 
    NON EMPTY {([Dim 1].[Member 1].[Member 1])} on columns
    ,NON EMPTY {([Dim 2].[Member 2].[Member 1])} on rows
from [Cube 1]
where
{(
    [Dim 3].[Hierarchy 3].[Member 3].&[Value 3]
    ,[Dim 4].[Hierarchy 4].[Member 4].&[Value 2]
    ,[Dim 4].[Hierarchy 4].[Member 4].&[Value 4]
    ,[Dim 4].[Hierarchy 4].[Member 4].&[Value 8]
    ,[Measures].[Measure 1]
)}

明白了:

The 'Hierarchy 4' hierarchy appears more than once in the tuple.

也试过这个(我知道，这里没有多大意义):

select 
    NON EMPTY {([Dim 1].[Member 1].[Member 1])} on columns
    ,NON EMPTY {([Dim 2].[Member 2].[Member 1])} on rows
from [Cube 1]
where
{(
    [Dim 3].[Hierarchy 3].[Member 3].&[Value 3]
    ,[Dim 4].[Hierarchy 4].[Member 4].&[Value 2]
    :[Dim 4].[Hierarchy 4].[Member 4].&[Value 4]
    :[Dim 4].[Hierarchy 4].[Member 4].&[Value 8]
    ,[Measures].[Measure 1]
)}

得到这个:

The : function expects a member expression for the 2 argument. A tuple set expression was used.

我是 MDX 的新手(在 T-SQL 方面更有经验)，甚至不知道到底要谷歌什么，所以我的愚蠢。

欢迎任何帮助。

提前致谢。

Answer 1

知道了:http://msdn.microsoft.com/en-us/library/ms146047.aspx

If you want to use multiple members from the same hierarchy, you need to include a set in the WHERE clause

另一件事，在我的例子中 Dim 2 = Dim 3，所以为了得到我期望的结果我需要这个:

select 
    NON EMPTY {([Dim 1].[Member 1].[Member 1])} on columns
    ,NON EMPTY 
       {(
          [Dim 2].[Hierarchy 3].[Member 3].&[Value 3] 
          ,[Dim 2].[Member 2].[Member 1]
       )} on rows
from [Cube 1]
where
(
    {
       [Dim 4].[Hierarchy 4].[Member 4].&[Value 2]
       ,[Dim 4].[Hierarchy 4].[Member 4].&[Value 4]
       ,[Dim 4].[Hierarchy 4].[Member 4].&[Value 8]
    }
    ,([Measures].[Measure 1])
)