CODEWITHSUNDEEP
tfschangesetworkitem
Andrew Xiang
Andrew Xiang

Reputation: 71

How to query work items with too many changesets

These work items (and the linked changesets) are often error prone. So, good candidates for team code review, and should pay more attention.

I have checked Work Item Query Language and Client Object Model and WIQL syntax for Link Query, but still cannot get these work items (with count of changesets) effectively.

Is there any way to query like this (should be WIQL, SQL syntax here only for demonstration)?

SELECT [Source].[System.Id], COUNT(1) AS Changesets FROM WorkItemLinks
WHERE [Source].[System.WorkItemType] = 'Bug'
    AND [System.Links.LinkType] = 'Changeset'
GROUP BY [Source].[System.Id]
HAVING Changesets > 5

Or is there an efficient way to use the APIs, that don't need to read each work item to get the count?

Or could only be achieved by execute SQL queries on database directly (like this)?

Or can we have something like CurrentRelatedLinkCount/ExternalLinkCount/HyperLinkCount/RelatedLinkCount, which are supported by default (from VS IDE or API), as the query field?

Upvotes: 6

Views: 3670

Answers (4)

cerezza
cerezza

Reputation: 251

You can use ExternalLinkCount for Changeset links. enter image description here

Upvotes: 3

paulyphonic
paulyphonic

Reputation: 874

If you have SQL Server Analysis Services installed you can query the relationships. Here is how to do it in Excel:

  1. Connect to your Tfs_Analysis cube in Excel (Data --> Other Sources --> Analysis Services)
  2. Select the Workitem Changeset fields,
  3. Look for the Version Control Changeset dimension, and select Changeset ID
  4. Look for the Work Item dimension and select ID

This will allow you to query the relationships between Work Items and Changesets. If you display one as a Row field and the other as a Column field, and then get the subtotals for each row, this will give you the changeset count per work item (as well as the work item count per changeset).

Note, however, that running this query on a very large project in TFS can be prohibitively expensive, so you should do this for a small range of changesets, and a small range of work items. To do this, note that to filter dates for changesets you need to use the Date dimension, whereas to filter dates for work items you need to use one of the work item fields (created date, changed date, etc.)

You could probably modify the OLAP cube to get this information put into fact tables for you by changing its ETL process from the warehouse, and in that case you would just be able to query changeset count.

Upvotes: 0

Andrew Xiang
Andrew Xiang

Reputation: 71

Report would do this - Report Builder supports SQL.

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

It is difficult without your table schema. But I guess you're trying to do this:

SELECT [Source].[System.Id], COUNT(1) AS Changesets FROM WorkItemLinks
WHERE [Source].[System.WorkItemType] = 'Bug'
    AND [System.Links.LinkType] = 'Changeset'
GROUP BY [Source].[System.Id]
HAVING COUNT(1) > 5

Am I right?

Upvotes: 0

Related Questions