使用MDX除外不起作用

I have the following Problem:

我有以下问题:

Select
    {
        [Measures].[PerformanceTotalYtd]

    } on columns,

    Non Empty{  
       Except(([Desk].[DeskName].[Trade].Members,[Time].[Year-Month-Day].[Day].&[2012]&[1]&[10]),([Desk].[DeskName].[Trade].Members,[Time].[Year-Month-Day].[Day].&[2012]&[1]&[09]))
    } on rows

from [Cube]

where ([Entity].[Entity].&[9], [Audience].[View].&[GOD])

It exists a Dimension with the Name Desk. This Dimension has a Hierarchy with the name DeskName. The lowest Level ist Trade.

它与Name Desk一起存在一个Dimension。此Dimension具有名为DeskName的层次结构。最低级别是贸易。

Desk: -Total -Segment -BusinessArea -Department -4th Level Portfolio -Desk -Trade

服务台:-Total -Segment -BusinessArea -Department -4th Level Portfolio -Desk -Trade

With the Query showing below, i want to show all Trades, that have the Measure "PerformanceTotalYtd" != NULL on the Date of 2012/01/10 except the Trades with the Measure "PerformanceTotalYtd" != NULL on the Date of 2012/01/09 !

通过下面的查询,我想显示所有交易,在2012/01/10的日期具有度量“PerformanceTotalYtd”!= NULL,除了具有度量的交易“PerformanceTotalYtd”!= 2012年日期的NULL / 01/09!

Example:

Trades with Measure PerformanceTotalYtd on the 2012/01/10:

2012/01/10期间与Measure PerformanceTotalYtd交易:

ABC 12,99

DEF 3,22

GHI 55,60

Trades with Measure PerformanceTotalYtd on the 2012/01/09:

2012/01/09期间与Measure PerformanceTotalYtd交易:

ABC 80,00

DEF 8,78

I want the following Result because the Trade "GHI" doesn't exists on the 2012/01/09 and is new:

我想要以下结果,因为2012/01/09上不存在交易“GHI”并且是新的:

GHI 55,60

My Query showing below have this result:

我的查询显示如下:

ABC 12,99

DEF 3,22

GHI 55,60

It doesn't delete the existing Trades from the 2012/01/09.

它不会删除2012/01/09的现有交易。

I have a Solution in SQL but want to make it in MDX:

我在SQL中有一个解决方案,但想在MDX中实现它:

    SELECT DD.Code, Sum(PerformanceTotalYtd) as TOTAL
      FROM [Reporting_DB].[Star].[Fact_PerformanceTotal] FIS
            inner join Star.Dimension_Desk DD on FIS.DeskID = DD.DeskID
      WHERE FIS.TimeID = 20120110 and FIS.EntityID = 9 AND DD.Code not in ( SELECT DD.Code
FROM [Reporting_DB_HRE].[Star].[Fact_PerformanceTotal] FIS inner join Star.Dimension_Desk DD on FIS.DeskID = DD.DeskID WHERE FIS.TimeID = 20120109 and FIS.EntityID = 9 group by DD.Code)group by DD.Code

Can anybody help me please? I can't find a solution.

有人可以帮帮我吗?我找不到解决方案。

Sorry for my bad english!

对不起,我的英语不好!

Alex

2 个解决方案

#1

I have found a similar example in the Adventure Works cube: The set {[Customer].[City].&[Bell Gardens]&[CA], [Customer].[City].&[Bellevue]&[WA], [Customer].[City].&[Bellflower]&[CA]} has 3 values for [Measures].[Internet Sales Amount] in [Date].[Calendar Year].&[2002], and only 2 values in [Date].[Calendar Year].&[2004]. So we need to show measure value for member in 2002 where measure value == null in 2004. The next MDX query achieves the desired result:

我在Adventure Works多维数据集中找到了一个类似的例子:集合{[Customer]。[City]。&[Bell Gardens]&[CA],[Customer]。[City]。&[Bellevue]&[WA], [客户]。[城市]。&[贝尔弗劳尔]和[CA]} [测量]中有3个值。[日期]中的[互联网销售额]。[日历年]。&[2002],只有2个值[日期]。[日历年]。&[2004]。因此,我们需要在2002年显示成员的度量值,其中2004年度量值== null。下一个MDX查询实现了所需的结果:

with set S as '{[Customer].[City].&[Bell Gardens]&[CA], [Customer].[City].&[Bellevue]&[WA], [Customer].[City].&[Bellflower]&[CA]}'
select
[Measures].[Internet Sales Amount] on 0,
non empty { Filter(S, IsEmpty(([Date].[Calendar Year].&[2004], [Measures].[Internet Sales Amount]))) } on 1
from [Adventure Works]
where ([Date].[Calendar Year].&[2002])

I tried to modify your example accordingly, but can't test it. Here it is:

我试图相应地修改你的例子,但无法测试它。这里是:

select
{ [Measures].[PerformanceTotalYtd] } on 0,
non empty { Filter([Desk].[DeskName].[Trade].Members, IsEmpty(([Time].[Year-Month-Day].[Day].&[2012]&[1]&[09], [Measures].[PerformanceTotalYtd]))) } on 1
from [Cube]
where ([Entity].[Entity].&[9], [Audience].[View].&[GOD], [Time].[Year-Month-Day].[Day].&[2012]&[1]&[10])

In short: use Filter instead of Except.

简而言之:使用Filter而不是Except。

#2

I have the Solution for my Problem. Following Query shows the same Result as the Query of Dmitry Polyanitsa! Have a nice Day guys!

我有我的问题的解决方案。以下查询显示与Dmitry Polyanitsa查询相同的结果!祝你们玩得愉快!

with
    set [Trades Today] as NonEmpty([Desk].[DeskName].[Trade].Members,  ([Measures].[PerformanceTotalYtd], [Time].[Year-Month-Day].[Day].&[2012]&[1]&[10]))
    set [Trades Yesterday] as NonEmpty([Desk].[DeskName].[Trade].Members,  ([Measures].[PerformanceTotalYtd], [Time].[Year-Month-Day].[Day].&[2012]&[1]&[9]))
    set [Trades Difference] as Except([Trades Today], [Trades Yesterday])

Select
    {
        [Measures].[PerformanceTotalYtd]

    } on columns,

    Non Empty{  
       [Trades Difference]
    } on rows

from [Cube]

where ([Entity].[Entity].&[9], [Audience].[View].&[GOD])