Bug Rates Report Query More "Active" States

Question

So I have custom work items with the following states:

Proposed - (Considered "Active")
Active - (Considered "Active")
Development - (Considered "Active")
Ready For Testing - (Considered "Resolved")
Ready For Production - (Considered "Resolved")
Closed

The question is, with the Bug Rates report, by default it has a parameter of "Active" which exists in our work items (as you can see above), however, I also need additional parameters to be included with the "Active" parameter in that report.  Is it possible?  How?  Because Active also means "Proposed" and "Development" in addition to Active.

Maybe this will make it easier to understand:

Active = Propose, Active, Development

I need the Bug Rates report to reflect those states as they're also active...

Make sense?

Answer 1

I think what needs to happen is this...

Original snippet:

MEMBER [Measures].[Active Cumulative] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Active + "]"),
         [Work Item].[System_Reason].[System_Reason]         
    ),
    [Measures].[Cumulative Count]

    )


Needs to be modified to do something like this (but i don't know how to get that cumulative sum):

MEMBER [Measures].[Active Cumulative] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Active + "]"),
        StrToMember("[Work Item].[System_State].[System_State].[" + @Development + "]"),
        StrToMember("[Work Item].[System_State].[System_State].[" + @Proposed+ "]"),
         [Work Item].[System_Reason].[System_Reason]         
    ),
    [Measures].[Cumulative Count]
    )

But I don't know how to accurately format this....

WITH
MEMBER [Measures].[Date Value] AS
    [Date].[Date].CurrentMember.Member_Value

MEMBER [Measures].[Active Cumulative] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Active + "]"),
         [Work Item].[System_Reason].[System_Reason]         
    ),
    [Measures].[Cumulative Count]
    )

MEMBER [Measures].[Active StateChange] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Active + "]"),
         [Work Item].[System_Reason].[System_Reason]         
    ),
    [Measures].[State Change Count]
    )

MEMBER [Measures].[Resolved StateChange] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Resolved + "]"),
        StrToMember("[Work Item].[System_Reason].[System_Reason].[" + @Fixed + "]")      ),
    [Measures].[State Change Count]
    )

SELECT
{
    [Measures].[Date Value],
    [Measures].[Active Cumulative],
    [Measures].[Active StateChange],
    [Measures].[Resolved StateChange]
} ON COLUMNS,
(
    Filter(
        [Date].[Date].[Date],
        [Date].[Date].CurrentMember.Member_Value >= CDate(@StartDateParam) AND
        [Date].[Date].CurrentMember.Member_Value <= CDate(@EndDateParam)
    )
) ON ROWS
FROM
(
    SELECT
    (
        STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),
        STRTOSET(@IterationParam),
        STRTOSET(@AreaParam),
        STRTOSET(@PriorityParam),
        StrToMember("[Work Item].[System_WorkItemType].[System_WorkItemType].[" + @Bug + "]")
    ) ON 0
    FROM [Team System]
)

Answer 2

anyone???

Answer 3

Or perhaps something like this where I can SUM the values in the Select???

    [Measures].[Active Cumulative],
    [Measures].[Development Cumulative],

WITH
MEMBER [Measures].[Date Value] AS
    [Date].[Date].CurrentMember.Member_Value

MEMBER [Measures].[Active Cumulative] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Active + "]"),
         [Work Item].[System_Reason].[System_Reason]          
    ),
    [Measures].[Cumulative Count]
    )

MEMBER [Measures].[Development Cumulative] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Development + "]"),
         [Work Item].[System_Reason].[System_Reason]          
    ),
    [Measures].[Cumulative Count]
    )

MEMBER [Measures].[Active StateChange] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Active + "]"),
         [Work Item].[System_Reason].[System_Reason]          
    ),
    [Measures].[State Change Count]
    )

MEMBER [Measures].[Resolved StateChange] AS
Sum(
   (
        StrToMember("[Work Item].[System_State].[System_State].[" + @Resolved + "]"),
        StrToMember("[Work Item].[System_Reason].[System_Reason].[" + @Fixed + "]")      ),
    [Measures].[State Change Count]
    )

SELECT
{
    [Measures].[Date Value],
    [Measures].[Active Cumulative],
    [Measures].[Development Cumulative],
    [Measures].[Active StateChange],
    [Measures].[Resolved StateChange]
} ON COLUMNS,
(
    Filter(
        [Date].[Date].[Date],
        [Date].[Date].CurrentMember.Member_Value >= CDate(@StartDateParam) AND
        [Date].[Date].CurrentMember.Member_Value <= CDate(@EndDateParam)
    )
) ON ROWS
FROM
(
    SELECT
    (
        STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),
	    STRTOSET(@IterationParam),
	    STRTOSET(@AreaParam),
	    STRTOSET(@PriorityParam),
	    StrToMember("[Work Item].[System_WorkItemType].[System_WorkItemType].[" + @Bug + "]")
    ) ON 0
    FROM [Team System]
)

Answer 4

anyone?????????

Answer 5

Hi mrpeepers,

Sorry for delayed response. 

Please check if the following works:

MEMBER [Measures].[Active Cumulative] AS
Sum(
   {
        {StrToMember("[Work Item].[System_State].[System_State].[" + @Active + "]"),
        StrToMember("[Work Item].[System_State].[System_State].[" + @Development + "]"),
        StrToMember("[Work Item].[System_State].[System_State].[" + @Proposed+ "]")} *
         [Work Item].[System_Reason].[System_Reason]         
    },
    [Measures].[Cumulative Count]
    )

 

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

yes!  i think that does it!  thank you!