我可以重新编写此 MDX 查询以使用集合而不是“And”吗？

Question

with member [Measures].[BoughtDispenser] as
    Sum(Descendants([Customer].[Customer].CurrentMember, [Customer].[Customer]),
        Iif(
            (IsEmpty(([Item].[ItemNumber].&[011074], [Measures].[Sale Amount]))
            And IsEmpty(([Item].[ItemNumber].&[011069], [Measures].[Sale Amount]))
            )
            Or IsEmpty([Measures].[Sale Amount]),
            0 , 1
        )
    )
select 
{[Measures].[Sale Amount]} on columns,
non empty filter([Customer].[Customer].children, [Measures].[BoughtDispenser])
    * {[Item].[ItemNumber].members}
on rows
from [Sales]
where [EnteredDate].[Quarter].&[2010-01-01T00:00:00]
;

目的是显示同时购买了两个分配器（011069 和 011074）之一的客户所购买的所有商品。

我根据我发现进行购物篮分析的查询来计算成员。我觉得应该有一种方法可以用集合 {[Item].[ItemNumber].&[011074], [Item].[ItemNumber].&[011069]} 来编写它，而不是两个 IsEmpty 测试。我所尝试的一切最终都得到了每个客户的结果。

我的环境是 SQL Server Analysis Services 2005。

Answer 1

是的，我可以！它只需要对计算成员采取稍微不同的方法：

with member [Measures].[BoughtDispenser] as
    Sum(Descendants([Customer].[Customer].CurrentMember, [Customer].[Customer])
        * {[Item].[ItemNumber].&[011069], [Item].[ItemNumber].&[011074]},
        [Measures].[Quantity Shipped]
    )
select 
{[Measures].[Sale Amount]} on columns,
non empty filter([Customer].[Customer].children, [Measures].[BoughtDispenser])
    * {[Item].[ItemNumber].members}
on rows
from [Sales]
where [EnteredDate].[Quarter].&[2010-01-01T00:00:00]
;