Sum and Group Results - Date Comparison

  • I am working on a MDX Dataset from a TFS2010 Cube (I Have also posted on the Team Foundation Server - Reporting & Warehouse forum).

    I am looking to produce the trend of aged defects over the last several months. I have code producing the data that I need, for I am unable to get it to count in the way I need for the SSRS line graph. Here is the code:

    WITH MEMBER [Measures].[AgedGroupTotal] AS ([Measures].[Work Item Count])

    MEMBER [Measures].[AgedGroup30] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) <= 30, [Measures].[Work Item Count], null)

    MEMBER [Measures].[AgedGroup3060] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) > 30 AND DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) <= 60, [Measures].[Work Item Count], null)

    MEMBER [Measures].[AgedGroup6090] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) > 60 AND DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) <= 90, [Measures].[Work Item Count], null)

    MEMBER [Measures].[AgedGroup90] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) > 90, [Measures].[Work Item Count], null)

    SELECT NON EMPTY { [Measures].[AgedGroup30], [Measures].[AgedGroup90], [Measures].[AgedGroup6090], [Measures].[AgedGroup3060], [Measures].[AgedGroupTotal] }

    ON COLUMNS, NON EMPTY { ([Date].[Month].[Month].ALLMEMBERS *

    [Date].[Week].[Week].ALLMEMBERS *

    [Date].[Date].[Date].ALLMEMBERS *

    [Work Item].[System_CreatedDate].[System_CreatedDate].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME

    ON ROWS FROM ( SELECT ( [Date].[Date].&[2013-01-01T00:00:00] : [Date].[Date].&[2013-03-31T00:00:00] )

    ON COLUMNS FROM ( SELECT ( { [Work Item].[System_State].&[Active] } )

    ON COLUMNS FROM ( SELECT ( { [Work Item].[System_WorkItemType].&[Bug] } )

    ON COLUMNS FROM [Work Item])))

    WHERE ([Work Item].[System_WorkItemType].&[Bug], [Work Item].[System_State].&[Active])

    Here is the result:

    The problem is the [Work Item].[System_CreatedDate].[System_CreatedDate].ALLMEMBERS needing to be included in the result. Without this member being included, the DATEDIFF formula's for the age groups do not calculate and give an #Error. If I output this data into an SSRS table with a SUM on the AgedGroups, and exclude the created date, I get the data set I need:

    The issue is that I need this summed result in my MDX dataset. However, I can not exclude the Created date, which creates the issue. I am new to MDX, but in SQL this operation would be simple with using a TEMP table and sum/group by. However, I know MDX does not work this way. Can anyone help me figure out how to get this dataset in MDX?

    Thank you for any help,

    Evgapro

  • If I have understood this correctly then its quite a tricky one but should be possible using the generate statement

    I cannot test the below MDX but here is a stab at what you probably want

    WITH

    MEMBER [Measures].[AgedGroupTotal] AS ([Measures].[Work Item Count])

    MEMBER [Measures].[AgedGroup30] AS

    Sum(

    Generate( [Work Item].[System_CreatedDate].[All].Children

    ,Filter(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name

    , [Date].[Date].CurrentMember.Name) <= 30))

    [Measures].[Work Item Count])

    SELECT NON EMPTY

    { [Measures].[AgedGroup30]

    , [Measures].[AgedGroupTotal] }

    ON COLUMNS

    , NON EMPTY {

    [Date].[Month].[Month].ALLMEMBERS *

    [Date].[Week].[Week].ALLMEMBERS *

    [Date].[Date].[Date].ALLMEMBERS }

    ON ROWS

    FROM ( SELECT ( [Date].[Date].&[2013-01-01T00:00:00] : [Date].[Date].&[2013-03-31T00:00:00] )

    ON COLUMNS

    FROM ( SELECT ( { [Work Item].[System_State].&[Active] } )

    ON COLUMNS FROM (

    SELECT ( { [Work Item].[System_WorkItemType].&[Bug] } )

    ON COLUMNS FROM [Work Item])))

    Mack

  • Mackers,

    Thanks for the response. I am getting the following error (try to debug it, but not having luck):

    Executing the query ...

    Query (7, 52) Parser: The syntax for ')' is incorrect.

    Execution complete

    WITH

    MEMBER [Measures].[AgedGroupTotal] AS ([Measures].[Work Item Count])

    MEMBER [Measures].[AgedGroup30] AS

    Sum(

    Generate( [Work Item].[System_CreatedDate].[All].Children

    ,Filter(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name

    , [Date].[Date].CurrentMember.Name) <= 30))

    [Measures].[Work Item Count])

    SELECT NON EMPTY

    { [Measures].[AgedGroup30]

    , [Measures].[AgedGroupTotal] }

    ON COLUMNS

    , NON EMPTY {

    [Date].[Month].[Month].ALLMEMBERS *

    [Date].[Week].[Week].ALLMEMBERS *

    [Date].[Date].[Date].ALLMEMBERS }

    ON ROWS

    FROM ( SELECT ( [Date].[Date].&[2013-01-01T00:00:00] : [Date].[Date].&[2013-03-31T00:00:00] )

    ON COLUMNS

    FROM ( SELECT ( { [Work Item].[System_State].&[Active] } )

    ON COLUMNS FROM (

    SELECT ( { [Work Item].[System_WorkItemType].&[Bug] } )

    ON COLUMNS FROM [Work Item])))

    It seems to be complaining here:

    , [Date].[Date].CurrentMember.Name) <= 30))

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply