I'm trying to do the above. I used the graphical interface to build the "Remaining Work" part which produces the result in hours. I have borrowed the out of the box report "Unplanned Work" which produces a count of work items. To get them on the same chart, I need the number of unplanned hours. I've tried changing [Measures].[Cumulative Count] to [Measures].[Cumulative Remaining Work] , but whereas the first produces an accurate count of added work items, the second just returns null values for that column. I should say that I've checked manually for values for 'remaining work' for at least 1 of the added items (the ones which are showing when I use 'cumulative count' instead of 'cumulative remaining work'). Any help gratefully received. Code below:
WITH
MEMBER [Measures].[Unplanned Remaining Work] AS
IIF
(
Datediff
(
'd'
,[Date].[Date].
CurrentMember.MemberValue
,
StrToMember(@FromDateDate).MemberValue
)
< 0
-- Iif Current Date is after PlanCompletionDate
,
SUM(
NONEMPTY(
CROSSJOIN(
[Work Item].[System_Id].[System_Id]
,
StrToMember(@FromDateDate).NextMember : [Date]. [Date].CurrentMember
,{
[Work Item].[Previous State].[All].
UnknownMember
,[Work Item].[Previous State].[Proposed]
}
,[Work Item].[System_State].[System_State].[Active]
,[Area].[Parent_ID].[All]
,[Iteration].[Parent_ID].[All]
)
,[Measures].[State Change Count]
)
,[Measures].[Cumulative Remaining Work]
)
,0
)
MEMBER
[Measures].[Date Key] AS
[Date].[Date].
CurrentMember.UniqueName
SELECT NON EMPTY { [Measures].[Unplanned Remaining Work],[Measures].[Microsoft_VSTS_Scheduling_RemainingWork], [Measures].[Microsoft_VSTS_Scheduling_CompletedWork] } ON COLUMNS, NON EMPTY { ([Date].[Date].[Date].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@WorkItemSystemWorkItemType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@IterationParentID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@AreaParentID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDateDate, CONSTRAINED) : STRTOMEMBER(@ToDateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Team Project].[Team Project].&[1] } ) ON COLUMNS FROM [Team System]))))) WHERE ( [Team Project].[Team Project].&[1], IIF( STRTOSET(@AreaParentID, CONSTRAINED).Count = 1, STRTOSET(@AreaParentID, CONSTRAINED), [Area].[Parent_ID].currentmember ), IIF( STRTOSET(@IterationParentID, CONSTRAINED).Count = 1, STRTOSET(@IterationParentID, CONSTRAINED), [Iteration].[Parent_ID].currentmember ), IIF( STRTOSET(@WorkItemSystemWorkItemType, CONSTRAINED).Count = 1, STRTOSET(@WorkItemSystemWorkItemType, CONSTRAINED), [Work Item].[System_WorkItemType].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS