I'm using the following code to get the number "developer hours by week" (slightly modified version of http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=326021&SiteID=1) .
WITH
SET [ValidRows] AS
' { FILTER
(
NONEMPTY
(
( [Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_WorkItemType].[System_WorkItemType],
[Work Item].[System_Title].[System_Title] ),
{[Measures].[Current Work Item Count]}
),
[Measures].[Completed_Work] <> 0
)
}'
MEMBER [Measures].[Completed Work On Period Start] AS
(
(STRTOMember(@prmStartDate)),
[Measures].[Company_Common_ActualTime]
)
MEMBER [Measures].[Completed Work On Period End] AS
(
(STRTOMember(@prmEndDate)),
[Measures].[Company_Common_ActualTime]
)
MEMBER [Measures].[Completed_Work] AS
(
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT
{ [ValidRows] } ON ROWS,
NON EMPTY
{
[Measures].[Completed Work On Period Start],
[Measures].[Completed Work On Period End],
[Measures].[Completed_Work]
} ON COLUMNS
FROM [Team System]
WHERE ([Company_Common_AssignedGroup].&[Electronics])
However sometimes the title of the work item has been changed after work has completed. Then this work item gets listed, even if no time was spent on the workitem during this period.
When I change the MDX to
WITH
SET [ValidRows] AS
' { FILTER
(
NONEMPTY
(
( [Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_WorkItemType].[System_WorkItemType] ),
{[Measures].[Current Work Item Count]}
),
[Measures].[Completed_Work] <> 0
)
}'
MEMBER [Measures].[Completed Work On Period Start] AS
(
(STRTOMember(@prmStartDate)),
[Measures].[Company_Common_ActualTime]
)
MEMBER [Measures].[Completed Work On Period End] AS
(
(STRTOMember(@prmEndDate)),
[Measures].[Company_Common_ActualTime]
)
MEMBER [Measures].[Completed_Work] AS
(
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT
( [ValidRows] , [Work Item].[System_Title].[System_Title] ) ON ROWS,
NON EMPTY
{
[Measures].[Completed Work On Period Start],
[Measures].[Completed Work On Period End],
[Measures].[Completed_Work]
} ON COLUMNS
FROM [Team System]
WHERE ([Company_Common_AssignedGroup].&[Electronics])
then some work items are listed twice: once with their old title and once with their new title.
Only if I leave out the title (or any other field that may have changed during the selected period), I get the exact number of rows.
However, in my report I want to show the title of the work item.
I have tried all sort of stuff but because I'm new to MDX, I just can't seem to find the answer.
Any help is appreciated.
Thanks.
Removing 'invalid rows' from query-result list
Yorik
Hi Deepak,
Thanks for you support.
Unfortunetely, our IT-department installed "SQL Server Standard Edition" so I don't have perspective views. Nevertheless the measure I'm using is in fact in the History perspective. The measure used is custom defined and has its reportable attribute set to "measure". When I open the Team System cube, it is listed in the "Current Work Item" measure dimension as "Current Work Item Company_Common_ActualTime" and are also in the "Work Item History" measure dimension as "Company_Common_ActualTime". I'm using the latter in my MDX query, so I think this one refers to the cumulative (history) measure.
I think the problem is a more basic MDX problem (or my limited understanding of it). The problem is that all my query variations return the product of all rows in all dimensions at start and end date of the date range. What I want is a query that returns me only work items for which the selected measure (ActualTime) has changed and its corresponding title at one point in time (e.g: range end date). I can get the wanted result by only selecting the measure (SELECT ( [ValidRows] ) ON ROWS in stead of SELECT ( [ValidRows] , [Work Item].[System_Title].[System_Title] ) ON ROWS) in my last query-variation, but then the title is not available for display in a report.
I hope this makes my problem more understandable.
Thanks.
Jay K
Hi paso,
Have you been able to resolve this issue
-Matt
Pooja Katiyar
Hi Paso,
Since I don't have your cube to play with, I'm not certain if the code below would work. I've put my thoughts into the comments and I hope they will help you!
-J
WITH
SET [ValidRows] AS
{ FILTER
(
NONEMPTY
(
(
// Having [Person] here is tricky since if the [Assigned To] during the period of your interest is different from
// the latest revision, you would not be able to get the [Company_Common_ActualTime].
// [Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_WorkItemType].[System_WorkItemType],
[Work Item].[System_Title].[System_Title] ),
// since [Company_Common_ActualTime] from [Work Item History] is a running sum calculation and is very expensive,
// we want to just perform the computation on work items who's latest revision has a non-null Company_Common_ActualTime.
// I am assuming that in your company's process, once the Company_Common_ActualTime is set, you don't try set to
// it back to null unless it was set by mistake.
{[Measures].[Current Work Item Company_Common_ActualTime]}
),
[Measures].[Completed_Work] <> 0
)
}
MEMBER [Measures].[Completed Work On Period Start] AS
(
(STRTOMember(@prmStartDate)),
// Having [System_Title].[All] is important here since the [Title] at @prmStartDate could be different from the latest
// [Title] from [ValidRows], you would not be able to retrieve [Company_Common_ActualTime] if they are different.
[Work Item].[System_Title].[All],
[Measures].[Company_Common_ActualTime]
)
MEMBER [Measures].[Completed Work On Period End] AS
(
(STRTOMember(@prmEndDate)),
[Work Item].[System_Title].[All],
[Measures].[Company_Common_ActualTime]
)
// I am guessing that you might want the [Assigned To] person at @prmStartDate and @prmEndDate.
// Note that I didn't do the same for [Title] since most people care about the latest title only...
MEMBER [Measures].[Assigned To On Period Start] AS
EXTRACT (
NONEMPTY (
[Assigned To].[Person].[Person],
STRTOMember(@prmStartDate),
[Work Item].[System_Title].[All],
[Measures].[Cumulative Count]
),
[Assigned To].[Person].[Person]
).Item(0).Member_Caption
MEMBER [Measures].[Assigned To On Period End] AS
EXTRACT (
NONEMPTY (
[Assigned To].[Person].[Person],
STRTOMember(@prmEndDate),
[Work Item].[System_Title].[All],
[Measures].[Cumulative Count]
),
[Assigned To].[Person].[Person]
).Item(0).Member_Caption
MEMBER [Measures].[Completed_Work] AS
(
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT
{ [ValidRows] } ON ROWS,
NON EMPTY
{
[Measures].[Completed Work On Period Start],
[Measures].[Completed Work On Period End],
[Measures].[Assigned To On Period Start],
[Measures].[Assigned To On Period End],
[Measures].[Completed_Work]
} ON COLUMNS
FROM [Team System]
WHERE ([Company_Common_AssignedGroup].&[Electronics])
ducmis
Since your report seems to require historical, rather than current work item data, maybe using measures from the Work Item History Perspective instead will work:
http://msdn2.microsoft.com/en-us/library/ms244678(VS.80).aspx
>>
The Current Work Item Perspective enables queries and reports based on the current state of work items on the server. In contrast, the Work Item History perspective provides views on the historical information about work items over time. This perspective is based on the Work Item History relational table.
...
Measures
The following table describes the measures included in the Work Item History perspective. The scheduling measures listed here are included with the default process templates. When measures in the cube are based on fields in a process template, they use the reference name of the originating field, but have a localized translation for the measure name seen when you browse the cube with Microsoft Excel or other reporting tools.
Cumulative Baseline Work
The number of hours of work from the baseline plan for the selected dimensions. The reference name of this measure is Microsoft_VSTS_Scheduling_BaselineWork.
Cumulative Completed Work
The number of hours of work completed for the selected dimensions. The reference name of this measure is Microsoft_VSTS_Scheduling_CompletedWork.
Cumulative Count
Cumulative count records the number of work item revisions that have occurred for the selected dimensions.
>>
The query could be something like:
>>
WITH
SET [ValidRows] AS
FILTER
(
( [Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_WorkItemType].[System_WorkItemType],
[Work Item].[System_Title].[System_Title]),
[Measures].[Completed_Work] > 0
)
MEMBER [Measures].[Completed Work On Period Start] AS
(
(STRTOMember(@prmStartDate)),
[Measures].[Cumulative Completed Work]
)
MEMBER [Measures].[Completed Work On Period End] AS
(
(STRTOMember(@prmEndDate)),
[Measures].[Cumulative Completed Work]
)
MEMBER [Measures].[Completed_Work] AS
(
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT
{ [ValidRows] } ON ROWS,
NON EMPTY
{
[Measures].[Completed Work On Period Start],
[Measures].[Completed Work On Period End],
[Measures].[Completed_Work]
} ON COLUMNS
FROM [Team System]
WHERE ([Company_Common_AssignedGroup].&[Electronics])
>>
vidya_084
Hello Matt,
I have not yet been able to solve this issue. I took a look at Deepak's proposal but did not completely understand it and I think it still refers to removing duplicates while this is not realy the issue.
As an example I added some output from my last query variation (column titles do NOT correspond exactly to MEMBER names)
Person Id Type System_Title Start End Done
Alen 13428 Task design descussion (null) (null) (null)
Alen 13428 Task design discussion (null) 4 4
Pete 12252 Task testsystem 29 (null) -29
Pete 12252 Task CLOSED: testsystem (null) 31 31
As you can see both ID's are listed twice because their title has changed during the start-end period. In the last case (12252) work done equals the sum of both rows. I could create a subtotal in my report for each System_Id, but offcourse I also want the subtotal for each Person (for all work items) and this seems more difficult because only the ID-total rows should be taken into account. And anyway I would rather solve this in my MDX query in stead of my Report.
Thanks.
KatyG
After many futile attempts at posting a reply here (most of the text disappears, but re-appears in edit mode, ie. WYSI-Not-WYG), here is a link to a plain text version:
microsoft.public.vsnet.enterprise.tools > Removing 'invalid rows' from query-result list (TFS)
http://groups.google.com/group/microsoft.public.vsnet.enterprise.tools/msg/811ec9806a1b90ae