现有相当于HAVING

debugcn 发表于 Dev

为什么

这有效：

WITH 
    MEMBER [Measures].[CurrentDay] AS
        AGGREGATE( 
            [Date].[Calendar].Currentmember,
            [Measures].[Reseller Sales Amount]
        )
    MEMBER [Measures].[CurrentMonth] AS
        AGGREGATE( 
            [Date].[Calendar].Currentmember.parent,
            [Measures].[Reseller Sales Amount]
        )
SELECT
    NON EMPTY 
    { [Measures].[CurrentDay],
        [Measures].[CurrentMonth] } 
    ON COLUMNS,
    NON EMPTY 
    { [Date].[Calendar].[Date] }
    HAVING [Measures].[CurrentDay]<>null //<<<<<<<<<<<<<<having line
    ON ROWS
From [Adventure Works]

从我拥有的Adventure Works版本返回以下内容：

在此处输入图片说明

如果我HAVING [Measures].[CurrentDay]<>null将这一行注释掉，则会发生这种情况：

在此处输入图片说明

是否有消除是行的另一种方式null为CurrentDay不使用HAVING？

我尝试使用EXISTING没有任何成功：

WITH 
    MEMBER [Measures].[CurrentDay] AS
        AGGREGATE( 
            [Date].[Calendar].Currentmember,
            [Measures].[Reseller Sales Amount]
        )
    MEMBER [Measures].[CurrentMonth] AS
        AGGREGATE( 
            [Date].[Calendar].Currentmember.parent,
            [Measures].[Reseller Sales Amount]
        )
SELECT
    NON EMPTY 
    { [Measures].[CurrentDay],
        [Measures].[CurrentMonth] } 
    ON COLUMNS,
    NON EMPTY 
    { EXISTING [Date].[Calendar].[Date] }
    ON ROWS
From [Adventure Works]

编辑

要在SSMS中运行nsousa的解决方案，我需要IIF像这样嵌套：

WITH 
    MEMBER [Measures].[CurrentDay] AS
        AGGREGATE( 
            [Date].[Calendar].Currentmember,
            [Measures].[Reseller Sales Amount]
        )
    MEMBER [Measures].[CurrentMonth] AS
        IIF(
            ISEMPTY([Measures].[CurrentDay]),
            NULL,
            AGGREGATE( 
                [Date].[Calendar].Currentmember.parent,
                [Measures].[Reseller Sales Amount]
                )
        )
SELECT
    NON EMPTY 
    { [Measures].[CurrentDay],
        [Measures].[CurrentMonth] } 
    ON COLUMNS,
    NON EMPTY 
    { [Date].[Calendar].[Date] }
    ON ROWS
From [Adventure Works]

恩苏萨

您可以重新定义度量：

WITH
    MEMBER [Measure].[Not Null Reseller Sales Amount] AS
        IIF( IsEmpty( [Measures].[Reseller Sales Amount] ), 0, [Measures].[Reseller Sales Amount] )
    MEMBER [Measures].[CurrentDay] AS
        AGGREGATE( 
            [Date].[Calendar].Currentmember,
            [Measures].[Not Null Reseller Sales Amount]
        )
    MEMBER [Measures].[CurrentMonth] AS
        AGGREGATE( 
            [Date].[Calendar].Currentmember.parent,
            [Measures].[Not Null Reseller Sales Amount]
        )
SELECT
    NON EMPTY 
    { [Measures].[CurrentDay],
        [Measures].[CurrentMonth] } 
    ON COLUMNS,
    NON EMPTY 
    { [Date].[Calendar].[Date] }
    ON ROWS
From [Adventure Works]

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。