-1

(Disclaimer: T-SQL and SSRS expert, about two hours exposure to WIQL)

I have some users that created a Team Foundation Services (TFS) Work Items query using the UI designer that they're in love with, with hard-coded monthly parameters, and have asked me to explore converting this into an SSRS report where they can pass all the parameters they want.

To do that I'm guessing that I'd need to convert the query into T-SQL.

Exporting the report into a .wiq file, then reading the query in the xml within the .wiq file I was able to extract the below WIQL (slightly edited for confidentiality). Problem is, looking at my TFS server, which contains databases TFS_Coniguration, Tfs_CX, Tfs_DefaultCollection, TFS_IT, and TFS_Warehouse, I don't see a table or view named WorkItems anywhere, so I'm at a loss to translate the below WIQL into T-SQL.

If there's some kind of table alias stored in TFS that would give me clues, I'm not seeing it.

So, question: How can I convert the below WIQL into T-SQL? 

SELECT 
    [System.Id], [System.AreaPath], [System.Title], [System.Tags], [System.AssignedTo], 
    [System.State], [foo.VSTS.Agile.Release], [System.CreatedDate], [Microsoft.VSTS.Scheduling.StoryPoints] 
FROM 
    WorkItems 
WHERE 
    [System.TeamProject] = @project AND
    [System.WorkItemType] IN ('User Story') AND
    [System.State] = 'Active' AND
    [foo.VSTS.Agile.Release] <= '2018-06-10T00:00:00.0000000' AND
    [foo.VSTS.Agile.Release] >= '2018-06-30T00:00:00.0000000' AND
    [System.AreaPath] UNDER 'AppDev' 
ORDER BY
    [foo.VSTS.Agile.Release]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jim Horn
  • 879
  • 6
  • 14
  • The only database that's designed for user querying is the warehouse database. The others are explicitly not supported and should not be directly queried. The warehouse schema is thoroughly documented online. – Daniel Mann Jun 21 '18 at 20:56
  • Thanks Daniel. I see the below link for the TFS_Warehouse schema, do you know of any that work better? https://learn.microsoft.com/en-us/vsts/report/sql-reports/table-reference-relational-warehouse-database?view=tfs-2018 – Jim Horn Jun 22 '18 at 16:49
  • 1
    As an aside, gotta love it when you do your best to frame up a question, making sure it's easily understood and the code is readable, and within two minutes some knucklehead -1's it without an explanation. – Jim Horn Jun 22 '18 at 16:49

2 Answers2

1

Daniel is right, it's not suggest to use SQL to query TFS operational store directly(Tfs_DefaultCollection), which may lose support from MS TFS support team.

One way to do this is using the database view vw_denorm_WorkItemCoreLatest. In this case "Latest" means, you only get the latest revision of the workitem without any previous versions of it. If you need all versions of a workitem, use the view vw_WorkItemCoreAll.

The example from the WIQL looks like:

SELECT [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State]
FROM WorkItems 
WHERE [System.TeamProject] = @project
  and [System.WorkItemType] = 'Ticket'
  and [System.State] <> 'Closed'
  and [System.State] <> 'Removed'

Except the selected columns, a sample of SQL for your reference:

SELECT *
FROM [dbo].[vw_denorm_WorkItemCoreLatest]
WHERE [System.TeamProject] = 'MyTeamProject'
  and [System.WorkItemType] = 'Ticket'
  and [System.State] <> 'Closed'
  and [System.State] <> 'Removed'

For warehouse, please take a look at How to query Work Items using SQL on the Relational Warehouse

PatrickLu-MSFT
  • 49,478
  • 5
  • 35
  • 62
0

I took a different approach. I have used a SSIS package to call the data to an Excel file and build a Data Mart out of it which supports Incremental Load. Then I am using an SSRS to easily use the data within my SQl table to build my report

image

Ramil Aliyev 007
  • 4,437
  • 2
  • 31
  • 47
Muhammad Ali Shan
  • 1