开发者

Can I reformulate this MDX query to use sets instead of an "And"?

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]),
            开发者_如何学C0 , 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]
;

The object is to show all the items purchased by customers who also bought either of the two dispensers (011069 and 011074).

I based the calculated member on a query I found to do basket analysis. I feel like there should be a way to write it with the set {[Item].[ItemNumber].&[011074], [Item].[ItemNumber].&[011069]} instead of the two IsEmpty tests. Everything I've tried ended up having every Customer in the result.

My environment is SQL Server Analysis Services 2005.


Yes I can! It just required a slightly different approach to the calculated member:

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]
;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜