locked
Converting TFS query into .rdl report (linked work items) RRS feed

  • Question

  • Hi folks,

    We have a query in TFS that we run via excel to get current status on users stories and change requests (see the attached picture). I want to convert that into an .rdl file so that we can deploy it and make it easier to maintain. The query has two parts:

    1) For a specific project and iteration get the ID, title, state of User Stories and Change Request.

    2) Get any work items that do not have user story as their "Parent".

    I can easily create a datasource based on TFS2010ReportDS and get the part 1. I can't get the part 2 of the query. I have looked at various tables and views inside that datasource but can't figure out how to get the part 2.

    Please help..

    Thursday, June 21, 2012 7:37 PM

All replies

  • Basically the question I have how do I convert the following wiql into a sql statement that I use within Report Builder:

    <Wiql>SELECT [System.Id], [System.Links.LinkType], [System.Title], [Microsoft.VSTS.Scheduling.StoryPoints], [System.State] FROM WorkItemLinks WHERE ((Source.[System.WorkItemType] = 'User Story' or Source.[System.WorkItemType] = 'Change Request') and Source.[System.IterationPath] under 'ABC\Sprint 030' and Source.[System.Reason] &lt;&gt; 'Obsolete') and ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Reverse') and (Target.[System.WorkItemType] = 'User Story') ORDER BY [Microsoft.VSTS.Common.StackRank] mode(DoesNotContain)</Wiql>

    Thanks,

    Vik

    Friday, June 22, 2012 6:21 PM
  • It's surprising that no one had a similar issue...!!

    Thursday, June 28, 2012 7:52 PM
  • There is a solution, expose your TFS Query as an xml webservice and consume it in SSRS. 

    Ewald Hofman  did a post with code exampels back in 2010, you can find it at http://www.ewaldhofman.nl/post/2010/04/22/Disable-the-requirement-on-the-secure-connection-for-Reporting-Services.aspx
    Sunday, July 1, 2012 5:57 PM
  • Mattias, this link by Ewald is about disabling SSL for SSRS....I am not sure where it talks about exposing the query as a webservice. Am I missing something..?

    Vik

    Monday, July 2, 2012 3:21 PM
  • Oops,  my bad, must have had some keybord error while copy links:(
    This is the correct link, hope it makes more sense

    http://www.ewaldhofman.nl/post/2009/05/11/Work-item-query-as-input-for-a-RS-report.aspx

    Once again Im sorry for posting the wrong link.


    By the way, Ive done some work and extended Ewalds service with the ability to run WIQL and some other tweeks
    Monday, July 2, 2012 4:29 PM
  • This looks good...I was able to get to the webservice and get the results for a flat query. The query I have is a linked query and when I use that I get the following error:

    Microsoft.TeamFoundation.WorkItemTracking.Client.ValidationException: TF248021: You have specified a query string that is not valid when you use the query method for a flat list of work items. You cannot specify a parameterized query or a query string for linked work items with the query method you specified.
       at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.RunRegularQuery(String requestid)
       at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.RunQuery(String requestId)
       at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.RunQuery()
       at Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore.Query(String wiql, IDictionary context)
       at ReportingService.WIQuery.ExecuteQuery(String tfsUri, String teamProject, String queryName) in C:\TFS\Play\Sandbox_Collection\Query_WI\ReportingService\ReportingService\WIQuery.asmx.cs:line 172
       at ReportingService.WIQuery.OpenStoredQuery(String tfsUri, String teamProject, String queryName) in C:\TFS\Play\Sandbox_Collection\Query_WI\ReportingService\ReportingService\WIQuery.asmx.cs:line 50

    Monday, July 2, 2012 5:22 PM
  • If you have the main query already created in reporting services, do a left outer join to the vFactLinkedCurrentWorkItem view using workitemsk to sourceworkitemsk, then another left outer join from vFactLinkedCurrentWorkItem back to CurrentWorkItemView (or whatever view/table you are already using) joining on targetworkitemsk to workitemsk and [second work item table/view].workitemtype=User Story. Specify the appropriate linktype ("Parent") by joining the vFactLinkedCurrentWorkItem to DimWorkItemLinkType table on WorkItemLinkTypeSK where LinkName=Parent. Now in the where clause of the query set [second work item table/view].workitemsk = null.

    • Edited by Tim Pacl Friday, September 21, 2012 7:57 PM
    Friday, September 21, 2012 7:53 PM