none
About Where clause(MDX)

    Question

  • Here are two querys

     

    Query 1

     

    SELECT

    {   

        [Measures].[State Change Count]

    } ON COLUMNS,

    {

        NONEMPTY

        (  

            FILTER(

                        [Date].[Date].[Date],

                        [Date].[Date].CURRENTMEMBER.MEMBER_VALUE

                                                        >= CDate(@StartDateParam)

                        AND

                        [Date].[Date].CURRENTMEMBER.MEMBER_VALUE

                                                        <= CDate(@EndDateParam)

            ) *

            EXTRACT

            (

                NONEMPTY(

                    [Work Item].[System_Id].[System_Id] *

                    GENERATE(STRTOSET(@AreaParam) AS s, DESCENDANTS(s.CURRENTMEMBER))

                    ,

                    [Measures].[Current Work Item Count]

                )  

                ,

                [Work Item].[System_Id]

            ) *

           

            GENERATE(STRTOSET(@ChangedByParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Changed By].[Person].[Person])) *

            GENERATE(STRTOSET(@WorkItemTypeParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Work Item].[System_WorkItemType].[System_WorkItemType])) *

            GENERATE(STRTOSET(@ChangedToParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Work Item].[System_State].[System_State]))

            ,

            [Measures].[State Change Count]

        )

       

    }

    ON ROWS

    FROM [Team System]

     

    Query 2

     

    SELECT

    {   

        [Measures].[State Change Count]

    } ON COLUMNS,

    {

        NONEMPTY

        (  

            FILTER(

                        [Date].[Date].[Date],

                        [Date].[Date].CURRENTMEMBER.MEMBER_VALUE

                                                        >= CDate(@StartDateParam)

                        AND

                        [Date].[Date].CURRENTMEMBER.MEMBER_VALUE

                                                        <= CDate(@EndDateParam)

            ) *

            EXTRACT

            (

                NONEMPTY(

                    [Work Item].[System_Id].[System_Id] *

                    GENERATE(STRTOSET(@AreaParam) AS s, DESCENDANTS(s.CURRENTMEMBER))

                    ,

                    [Measures].[Current Work Item Count]

                )  

                ,

                [Work Item].[System_Id]

            ) *

           

            GENERATE(STRTOSET(@ChangedByParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Changed By].[Person].[Person])) *

            GENERATE(STRTOSET(@WorkItemTypeParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Work Item].[System_WorkItemType].[System_WorkItemType]))

            ,

            [Measures].[State Change Count]

        )

       

    }

    ON ROWS

    FROM [Team System]

    WHERE

    (

        GENERATE(STRTOSET(@ChangedToParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Work Item].[System_State].[System_State]))

    )

     

    I don`t know why results are different.

    Could anyone explain to me?

    Wednesday, September 22, 2010 2:50 PM

Answers

  • Hi,

     

    Did you got wrong value or different structure of results? If you meant the structure, since you move the set to the where Clause, then you will see the different results. Like below one?

    select {[Measures].[Internet Order Quantity]} on 0,

    nonempty(

        { [Date].[Calendar Year].[Calendar Year]} *

        GENERATE([Product].[Product Categories].[Category].&[1] as s,

        DESCENDANTS(s.CURRENTMEMBER,[Product].[Product Categories].[Subcategory]))

    ,[Measures].[Internet Order Quantity]) on 1

    from [Adventure Works];

    ---------------output

                  Internet Order Quantity

    CY 2001  Mountain Bikes       173

    CY 2001  Road Bikes     840

    CY 2002  Mountain Bikes       615

    CY 2002  Road Bikes     2,062

    CY 2003  Mountain Bikes       2,088

    CY 2003  Road Bikes     2,797

    CY 2003  Touring Bikes  825

    CY 2004  Mountain Bikes       2,094

    CY 2004  Road Bikes     2,369

    CY 2004  Touring Bikes  1,342

     

    select {[Measures].[Internet Order Quantity]} on 0,

    nonempty(

        { [Date].[Calendar Year].[Calendar Year]}    

    ,[Measures].[Internet Order Quantity]) on 1

    from [Adventure Works]

    where GENERATE([Product].[Product Categories].[Category].&[1] as s,

    DESCENDANTS(s.CURRENTMEMBER,[Product].[Product Categories].[Subcategory]))

    --------output

           Internet Order Quantity

    CY 2001  1,013

    CY 2002  2,677

    CY 2003  5,710

    CY 2004  5,805

     

    Thanks,

    Raymond
    Raymond Li - MSFT
    • Marked as answer by SZUU Friday, September 24, 2010 6:34 PM
    Friday, September 24, 2010 9:12 AM
    Moderator

All replies

  • Hi,

     

    Did you got wrong value or different structure of results? If you meant the structure, since you move the set to the where Clause, then you will see the different results. Like below one?

    select {[Measures].[Internet Order Quantity]} on 0,

    nonempty(

        { [Date].[Calendar Year].[Calendar Year]} *

        GENERATE([Product].[Product Categories].[Category].&[1] as s,

        DESCENDANTS(s.CURRENTMEMBER,[Product].[Product Categories].[Subcategory]))

    ,[Measures].[Internet Order Quantity]) on 1

    from [Adventure Works];

    ---------------output

                  Internet Order Quantity

    CY 2001  Mountain Bikes       173

    CY 2001  Road Bikes     840

    CY 2002  Mountain Bikes       615

    CY 2002  Road Bikes     2,062

    CY 2003  Mountain Bikes       2,088

    CY 2003  Road Bikes     2,797

    CY 2003  Touring Bikes  825

    CY 2004  Mountain Bikes       2,094

    CY 2004  Road Bikes     2,369

    CY 2004  Touring Bikes  1,342

     

    select {[Measures].[Internet Order Quantity]} on 0,

    nonempty(

        { [Date].[Calendar Year].[Calendar Year]}    

    ,[Measures].[Internet Order Quantity]) on 1

    from [Adventure Works]

    where GENERATE([Product].[Product Categories].[Category].&[1] as s,

    DESCENDANTS(s.CURRENTMEMBER,[Product].[Product Categories].[Subcategory]))

    --------output

           Internet Order Quantity

    CY 2001  1,013

    CY 2002  2,677

    CY 2003  5,710

    CY 2004  5,805

     

    Thanks,

    Raymond
    Raymond Li - MSFT
    • Marked as answer by SZUU Friday, September 24, 2010 6:34 PM
    Friday, September 24, 2010 9:12 AM
    Moderator
  • Thank you,

    I really appreciate your help

    Friday, September 24, 2010 6:34 PM