How to request work items with too many change sets

Question

These work items (and related changes) are often error prone. So, there are good candidates for viewing the team code and should pay more attention.

I checked the Work Item Query Language and the client object model and the WIQL syntax for requesting links , but still cannot use these work items efficiently (with the number of changes).

Is there a way to request this (there should be WIQL syntax, SQL is here just 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 APIs that don't need to read every work item to get an account?

Or can it be achieved only by executing SQL queries directly in the database ( like this )?

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

Answer 1

You can use ExternalLinkCount for Changeset links.

Answer 2

This is difficult without your table schema. But I think you are 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 

I'm right?

Answer 3

The report will do this - the report builder supports SQL.

Answer 4

If you have SQL Server Analysis Services installed, you can request a relationship. Here's how to do it in Excel:

• Connect to the Tfs_Analysis cube in Excel (Data → Other Sources → Analysis Services)
• Select the fields "Changes in the working class",
• Take a look at measuring version control settings and select a parameter set identifier
• Find the size of the work item and select ID

This will allow you to query the relationship between work items and change sets. If you display it as a Row field and the other as a column field, and then get subtotals for each row, this will give you the number of change sets for each work item (as well as the number of work items for the change set).

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 change sets and few work items. To do this, note that you need to use a date dimension to filter dates for change sets, while you need to use one of the work item fields to create dates for work items (creation date, date modified, etc.).

Perhaps you may modify the OLAP cube to get this information for the actual tables for you by changing your ETL process from stock, in which case you can simply request a set of changes account.