Question

我有以下问题:

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

它与名称处存在一个方面。这个方面有一个等级,名称为服务台。最低水平的贸易。

服务台: 地区——省-4 级别组合 - Desk - Trade

如下文中所示,即要显示所有有“Performance TotalYtd”的行业! NUL, 日期:2012/01/10,但与“Performance TotalYtd”措施有关的行业除外。 NUL on the Annual of 2012/01/09 !

例:

计量行业 Ytd on the 2012/01/10:

ABC 12,99

DEF, 322

GHI 55,60

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

ABC 80,00

DEF 8,78

我想得出以下结果,因为“GHI”贸易在2012/01/09年就存在,并且是新的:

GHI 55,60

My Query showing below have this result:

ABC 12,99

DEF, 322

GHI 55,60

它没有从2012/01/09年删除现有贸易。

我在SQ中有一个解决办法,但希望在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.

给我的坏官办公室看病!

* A/63/150。

Answer 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:

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

我试图相应地修改你的榜样,但可以加以检验。这里是:

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

简言之,使用过滤器而不是例外。

Answer 2

我有办法解决我的问题。下面的表象是Dmitry Polyanitsa的表象。有一个ice日!

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

友情链接