In this blog post I’ll be walking you through the process of customizing the TFS 2011 Scrum 2.0 Process Template to add a new work item type, introducing new work item queries, modifying existing queries, adding new reports, etc. To give you a bit of context, at the 2011 ALM Summit Anu Bharadwaj presented a talk on Exploratory Testing highlighting how the Team @ Microsoft took advantage of MTM 2011 to test various VS ALM products in the “fun” way by organizing “Testing Tours.” To dive deep in to the subject I decided to modify the scrum 2.0 process template by introducing a ‘Testing Tour’ type work item and setting up some reporting around it to monitor, track and report on the testing inroads made through Testing tours.
1. What is a testing Tour?
This question has greatly been answered already. An excerpt from the post here…
“Tours are essentially used to define a theme for exploration. If you were a tourist in a new city, how would you explore the city? Perhaps you would visit the famous landmarks in a quick tour, perhaps you would do a detailed walking tour of just one of your favourite sites, perhaps you would visit the lesser known attractions and avoid the touristy places. Likewise, if you were a tester testing a new app, how would you test it? Perhaps you would do a quick sales demo walking through the primary scenarios, perhaps you would test one scenario in great detail, perhaps you would test some of the lesser advertised features. Any theme that you choose to apply to your exploration is informed by the learning you have had from your previous explorations. You know your developer is lousy at exception handling - a tour like "Intelligent tourist tour" (where the tourist asks hard questions to the guide :D) would help - you could try all the hard boundary value inputs or rarer scenarios bound to produce a boundary condition. You know the app has performance issues when more than a few users hit the server - you could try the "Carnival tour" where your entire test team logs on to the client altogether and parties on it! :)
My favourite tour is the Back Alley tour where you exercise some of the lesser known paths in the application, the Fedex tour where you can track a data structure from point to point to see how it gets transmuted, the All Nighter tour can be helpful in catching some nasty memory leaks”…
2. Making sure we’re on the right track…
The Goal is to customise the scrum 2.0 process template such that we have new work item type to track all ‘Testing Tours’ that are performed in a sprint. The Testing tour work item will be child to the Product Back log Item. It should be possible to identify when what testing tour was conducted, whether the tour is impeded, pending, in progress or complete. The tester responsible for the tour should be able to see the testing tour work item in his/her to-do view in visual studio team explorer. As part of the bigger picture the testing team should be able to run daily stand ups using the testing tours reports which give a representation of testing tour run frequency, bug count by testing tours & testing tour coverage by stories.
Order of changes required to reach the finish line,
I. Download existing Scrum 2.0 process Template
II. Adding New Work Item Type Definition
III. Adding/Editing Work Item Queries
IV. Uploading the new Process Template and Creating a new Team Project
V. Testing the to-do view, work item links & queries in Visual Studio
VI. Verifying Changes to the TFS data warehouse
VII. Writing new SSRS Reports
VIII. Writing new Reports using TFS API (covered in Part II)
IX. Wearing the tester hat to start touring! (covered in Part II)
In case you are looking for a short cut, you can download the modified process template from here.
3. Customising Microsoft Scrum 2.0 Process Template
To try this out you will need TFS 2011 preview, Visual Studio 2011 preview & Microsoft Test Manager 11 preview. If you don’t already have a play pit with all this set up, consider downloading a ready to use TFS 2011 ALM preview VM from Brian Keller’s Blog. Let’s get started…
3.1 – Download Scrum 2.0 process Template
You can download the Microsoft Scrum 2.0 Preview Process Template from the Process Template Manager from Visual Studio 2011 Preview Team Explorer.
Open Visual Studio | Go to Team Explorer Home | Click Settings | Click Process Template Manager | From the Process Template Manager modal window click Download
3.2 - Adding New Work Item Type Definition
Navigate to the path you downloaded the process template. You will see a lot of subfolders, basically a process template is a collection of files that together define various process elements of a team project in Team Foundation Server. The key folders for us will be WorkItem Tracking and Reports,
Navigate to the ..\WorkItem Tracking\TypeDefinitions folder, the xml files this folder contains are the definitions of the work item types. Create a copy of ‘Task.xml’ and rename the copy to ‘TestingTour.xml’.
Note – If you have Visual Studio 2010 available on a machine, you can cheat by opening ‘TestingTour.xml’ in the Process Template Editor. This will not only make editing the TestingTour.xml easy but also allow you to preview the changes.
1. Specify the work item name and description, make sure the name here matches the name of the file.
2. Add a new custom field ‘Testing Tour’, make it reportable and build a list of all the Testing tours you would like to show up in the testing tour drop down list.
3. Modify the Activity Field and set ‘Testing’ as the default value, so that when the Testing Tour work item is created the Activity Field list which contains ‘Development’, ‘Design’, ‘Documentation’, etc is set to ‘Testing’.
4. Now from the Layout tab in the ‘Details’ Group add a new control and change the Label to ‘Testing Tour:’ and set the Field Name to ‘Custom.TestingTour’. After making the changes you can click on the Preview Form button to see a preview of what the work item ‘TestingTour’ will look like.
Note – If you are having trouble or do not have Visual Studio 2010 then consider downloading the final ‘TestingTour.xml’ from here.
5. Navigate to the ..\WorkItem Tracking folder and open WorkItems.xml, the newly created ‘TestingTour.xml’ will needed to be added to the WorkItems.xml in order for the new work item type to be created during the team project creation. In the <WORKITEMTYPES> tag add the path to the TestingTour.xml as shown in the screen shot below,
With what we have done so far, if you were to create a new team project from this process template you will be able to see the new ‘Testing Tour’ work item type. Screen shot below…
3.3 - Adding/Editing Work Item Queries
Navigate to the ..\WorkItem Tracking\Queries folder, the wiq files contain the work item queries that are created for you when the team project is created.
We will be adding two new work item queries,
- ‘MyTestingTours.wiq’ – To see all the testing tours assigned to me. The below query will also show the Testing Tour the work item will be used to cover.
<span style="color: blue;"><?</span><span style="color: rgb(163, 21, 21);">xml </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">1.0</span>" <span style="color: red;">encoding</span><span style="color: blue;">=</span>"<span style="color: blue;">utf-8</span>"<span style="color: blue;">?>
<</span><span style="color: rgb(163, 21, 21);">WorkItemQuery </span><span style="color: red;">Version</span><span style="color: blue;">=</span>"<span style="color: blue;">1</span>"<span style="color: blue;">>
<</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</span>SELECT [System.Id],
[System.Title],
[System.State],
[System.AssignedTo],
[Custom.TestingTour]
FROM WorkItems
WHERE [System.TeamProject] = @project
AND [System.WorkItemType] = 'TestingTour'
AND [System.AssignedTo] = @me
ORDER BY [System.Id]
<span style="color: blue;"></</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</</span><span style="color: rgb(163, 21, 21);">WorkItemQuery</span><span style="color: blue;">>
</span>
- ‘TestingTours.wiq’ – To all testing tours by PBI in the current sprint. This will be a linked query and will show the parent work item i.e. the requirement work item linked to the Testing Tour work item. This will help us report on the product backlog coverage by Testing tour.
<span style="color: blue;"><?</span><span style="color: rgb(163, 21, 21);">xml </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">1.0</span>" <span style="color: red;">encoding</span><span style="color: blue;">=</span>"<span style="color: blue;">utf-8</span>"<span style="color: blue;">?>
<</span><span style="color: rgb(163, 21, 21);">WorkItemQuery </span><span style="color: red;">Version</span><span style="color: blue;">=</span>"<span style="color: blue;">1</span>"<span style="color: blue;">>
<</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</span>SELECT
[System.Id],
[System.Links.LinkType],
[System.WorkItemType],
[System.Title],
[System.State],
[Custom.TestingTour],
[Microsoft.VSTS.Common.Priority]
FROM WorkItemLinks
WHERE ([Source].[System.TeamProject] = @project
AND [Source].[System.IterationPath] UNDER '$$PROJECTNAME$$\Release 1\Sprint 1'
AND [Source].[System.WorkItemType] = 'TestingTour')
And ([System.Links.LinkType] <span style="color: red;"><> </span>'')
And ([Target].[System.WorkItemType] <span style="color: red;"><> </span>'')
ORDER BY [Microsoft.VSTS.Common.Priority] mode(MayContain)
<span style="color: blue;"></</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</</span><span style="color: rgb(163, 21, 21);">WorkItemQuery</span><span style="color: blue;">>
</span>
Along with this we’ll be editing,
- ‘BlockedTasks.wiq’ – To account for any impeded testing tours in a sprint that have purposely not been removed.
<span style="color: blue;"><?</span><span style="color: rgb(163, 21, 21);">xml </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">1.0</span>" <span style="color: red;">encoding</span><span style="color: blue;">=</span>"<span style="color: blue;">utf-8</span>"<span style="color: blue;">?>
<</span><span style="color: rgb(163, 21, 21);">WorkItemQuery </span><span style="color: red;">Version</span><span style="color: blue;">=</span>"<span style="color: blue;">1</span>"<span style="color: blue;">>
<</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</span>SELECT [System.Id],
[System.WorkItemType],
[System.Title],
[Microsoft.VSTS.Common.BacklogPriority],
[System.AssignedTo],
[System.State],
[Microsoft.VSTS.CMMI.Blocked]
FROM WorkItems
WHERE [System.TeamProject] = @project
AND [System.IterationPath] UNDER '$$PROJECTNAME$$\Release 1\Sprint 1'
AND ( [System.WorkItemType] = 'Task' OR [System.WorkItemType] = 'TestingTour' )
AND [Microsoft.VSTS.CMMI.Blocked] = 'Yes'
AND [System.State] <span style="color: red;"><> </span>'Removed'
ORDER BY [Microsoft.VSTS.Common.BacklogPriority]
<span style="color: blue;"></</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</</span><span style="color: rgb(163, 21, 21);">WorkItemQuery</span><span style="color: blue;">>
</span>
- ‘MyWork-ToDo.wiq’ – To add testing tours to the work item hub view in Visual Studio Team Explorer. Now the interesting thing is that this query picks up the Work Items that are assigned to me not from the work item type directly but from the ‘My Work Category’ group that is defined in the ..\WorkItem Tracking\Categories.xml which I’ll show you how to modify in the next step.
<span style="color: blue;"><?</span><span style="color: rgb(163, 21, 21);">xml </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">1.0</span>" <span style="color: red;">encoding</span><span style="color: blue;">=</span>"<span style="color: blue;">utf-8</span>"<span style="color: blue;">?>
<</span><span style="color: rgb(163, 21, 21);">WorkItemQuery </span><span style="color: red;">Version</span><span style="color: blue;">=</span>"<span style="color: blue;">1</span>"<span style="color: blue;">>
<</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</span>SELECT
[System.Id],
[System.WorkItemType],
[System.Title],
[System.AssignedTo],
[System.State],
[Microsoft.VSTS.Common.BacklogPriority]
FROM WorkItems
WHERE [System.TeamProject] = @project
AND [System.AssignedTo] = @me
AND ([System.State] <span style="color: red;"><> </span>'Done')
AND ([System.State] <span style="color: red;"><> </span>'Removed')
AND [System.WorkItemType] in group 'My Work Category'
ORDER BY [Microsoft.VSTS.Common.BacklogPriority], [System.Id]
<span style="color: blue;"></</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</</span><span style="color: rgb(163, 21, 21);">WorkItemQuery</span><span style="color: blue;">>
</span>
- ‘SprintBacklog.wiq’ – To account for testing tours along with tasks, bugs & PBI’s in a sprint. As you can see below, if the Testing Tour work item is linked to PBI then it will only be picked up if it has not been removed from the sprint.
<span style="color: blue;"><?</span><span style="color: rgb(163, 21, 21);">xml </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">1.0</span>" <span style="color: red;">encoding</span><span style="color: blue;">=</span>"<span style="color: blue;">utf-8</span>"<span style="color: blue;">?>
<</span><span style="color: rgb(163, 21, 21);">WorkItemQuery </span><span style="color: red;">Version</span><span style="color: blue;">=</span>"<span style="color: blue;">1</span>"<span style="color: blue;">>
<</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</span>SELECT [System.Id],
[System.Title],
[Microsoft.VSTS.Common.BacklogPriority],
[System.AssignedTo],
[System.State],
[Microsoft.VSTS.Scheduling.RemainingWork],
[Microsoft.VSTS.CMMI.Blocked],
[System.WorkItemType]
FROM WorkItemLinks
WHERE ([Source].[System.TeamProject] = @project
AND [Source].[System.IterationPath] UNDER '$$PROJECTNAME$$\Release 1\Sprint 1'
AND ( [Source].[System.WorkItemType] = 'Product Backlog Item' OR [Source].[System.WorkItemType] = 'Bug'
OR [Source].[System.WorkItemType] = 'TestingTour' OR [Source].[System.WorkItemType] = 'Task' )
AND [Source].[System.State] <span style="color: red;"><> </span>'Removed')
And ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward')
And (( [Target].[System.WorkItemType] = 'Task' OR [Target].[System.WorkItemType] = 'TestingTour' )
AND [Target].[System.State] <span style="color: red;"><> </span>'Removed')
ORDER BY [Microsoft.VSTS.Common.BacklogPriority],
[Microsoft.VSTS.Scheduling.Effort],
[Microsoft.VSTS.Scheduling.RemainingWork]
mode(Recursive)
<span style="color: blue;"></</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</</span><span style="color: rgb(163, 21, 21);">WorkItemQuery</span><span style="color: blue;">>
</span>
- ‘UnfinishedWork.wiq’ – To track unfinished work on the testing tours in a sprint, this query differs from the above only in the work item state comparison.
<span style="color: blue;"><?</span><span style="color: rgb(163, 21, 21);">xml </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">1.0</span>" <span style="color: red;">encoding</span><span style="color: blue;">=</span>"<span style="color: blue;">utf-8</span>"<span style="color: blue;">?>
<</span><span style="color: rgb(163, 21, 21);">WorkItemQuery </span><span style="color: red;">Version</span><span style="color: blue;">=</span>"<span style="color: blue;">1</span>"<span style="color: blue;">>
<</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</span>SELECT [System.Id],
[System.Title],
[Microsoft.VSTS.Common.BacklogPriority],
[System.AssignedTo],
[System.State],
[Microsoft.VSTS.Scheduling.RemainingWork],
[Microsoft.VSTS.CMMI.Blocked],
[System.WorkItemType]
FROM WorkItemLinks
WHERE ([Source].[System.TeamProject] = @project
AND [Source].[System.IterationPath] UNDER '$$PROJECTNAME$$\Release 1\Sprint 1'
AND ( [Source].[System.WorkItemType] = 'Product Backlog Item' OR [Source].[System.WorkItemType] = 'Bug'
OR [Source].[System.WorkItemType] = 'TestingTour' OR [Source].[System.WorkItemType] = 'Task' )
AND [Source].[System.State] <span style="color: red;"><> </span>'Removed'
AND [Source].[System.State] <span style="color: red;"><> </span>'Done') And ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward')
And (( [Target].[System.WorkItemType] = 'Task' OR [Target].[System.WorkItemType] = 'TestingTour' )
AND [Target].[System.State] <span style="color: red;"><> </span>'Done'
AND [Target].[System.State] <span style="color: red;"><> </span>'Removed')
ORDER BY [Microsoft.VSTS.Common.BacklogPriority],
[Microsoft.VSTS.Scheduling.Effort],
[Microsoft.VSTS.Scheduling.RemainingWork]
mode(Recursive)
<span style="color: blue;"></</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</</span><span style="color: rgb(163, 21, 21);">WorkItemQuery</span><span style="color: blue;">>
</span>
- ‘WorkInProgress.wiq’ – To track the in progress tours in a sprint along this also brings back the remaining work for the completion of the tour as part of the query result.
<span style="color: blue;"><?</span><span style="color: rgb(163, 21, 21);">xml </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">1.0</span>" <span style="color: red;">encoding</span><span style="color: blue;">=</span>"<span style="color: blue;">utf-8</span>"<span style="color: blue;">?>
<</span><span style="color: rgb(163, 21, 21);">WorkItemQuery </span><span style="color: red;">Version</span><span style="color: blue;">=</span>"<span style="color: blue;">1</span>"<span style="color: blue;">>
<</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</span>SELECT [System.Id],
[System.WorkItemType],
[System.Title],
[System.AssignedTo],
[System.State],
[Microsoft.VSTS.Scheduling.RemainingWork]
FROM WorkItems
WHERE [System.TeamProject] = @project
AND [System.IterationPath] UNDER '$$PROJECTNAME$$\Release 1\Sprint 1'
AND ( [System.WorkItemType] = 'Task' OR [System.WorkItemType] = 'TestingTour' )
AND [System.State] = 'In Progress'
ORDER BY [System.AssignedTo],
[Microsoft.VSTS.Common.BacklogPriority]
<span style="color: blue;"></</span><span style="color: rgb(163, 21, 21);">Wiql</span><span style="color: blue;">>
</</span><span style="color: rgb(163, 21, 21);">WorkItemQuery</span><span style="color: blue;">>
</span>
As I mentioned earlier, the ‘My Work Category’ group that is defined in the ..\WorkItem Tracking\Categories.xml will need to be modified to include the ‘TestingTour’ type work item.
Also, the new queries that we have defined will not be picked up until we explicitly set this up in the ..\WorkItem Tracking\WorkItems.xml. In the Current Sprint QueryFolder add the ‘TestingTour.wiq’ and in the My Work QueryFolder add the MyTestingTours.wiq.
3.4 - Uploading the new Process Template and Creating a new Team Project
Finishing touches, before you upload the process template, remember to rename the process template by modifying ..\Microsoft Visual Studio Scrum 2.0 – Preview 1\ProcessTemplate.xml failing to do this may result in you overwriting the existing process template during upload.
As a best practice, I would highly recommend modifying the ..\Microsoft Visual Studio Scrum 2.0 – Preview 1\Classification\Classification.xml to include a property for process template Name, you will understand the joy this brings when you try and modify the process template programmatically using the TFS API, more on this here.
If you are still reading, congratulations, you have done all the hard work, it’s time to create a new Team Project using the process template you have successfully customized for ‘Testing Tours’.
1. Open Process Template Manager (Refer to 3.1) and click on Upload and navigate to the customized process template and click ok.
2. From the Team Project Selector Control choose ‘New Team Project’ or even better, type ‘New Team Project’ in the Quick Launch control
3. Let the wizard lead you, make sure you select the customized process template you uploaded in the previous steps.
Great! So the project ‘Testing Tour – Demo’ has been created using the customized Scrum Process Template. To try out the queries it is important that you create at least 1 Product Back Log Item, 1 Task associated to that Product Back Log Item and 2 Testing Tours (1 associated to the Product Backlog Item and 1 orphaned). Make sure you assign all these work items to the Iteration ‘Testing Tour – Demo\Release1\Sprint1’.
To add the Testing Tour work item to the Product Backlog Item, you can go to Links, click 
to add a new TestingTour type work item.
Create the other Testing Tour work Item from the Team Explorer Work Item Page and do not associate it to the Product Backlog Item, assign it to Sprint 1 and set the field Blocked to ‘Yes’.
Now that we have some requirements, tasks and testing tours set up, let’s move to the next section to test the customizations done to the process template to track, monitor and manage testing tours.
3.5 - Testing the to-do view, work item links & queries in Visual Studio
Open Visual Studio | Team Explorer | Work Items | Shared Queries
- From under the
folder run the 
query. We can see all work items, including testing tours, both forward associated to the PBI and the orphaned testing tour work item.
- From under the
folder run the 
query. We can see that the blocked testing tour work item shows up in the query result. This makes tracking any impeded tours very easily to track and manage.
- From under the
folder run the 
query. You can see the parent work item i.e. PBI associated to the testing tour. Makes tracking tour by requirement so easy to track. You can also see the Testing tour type as part of the query results.
- From under the
folder run the 
or the 
query. You should be able to track all testing tours a long with the other work item types that have unfinished work or are in progress.
- From under the
folder run the 
query. You will be able to see all testing tours assigned to you.
- From under the
folder run the 
query. You will be able to see all tasks, bugs, pbi’s and testing tours (‘My Work Category’ group) that are currently assigned to you.
As a tester if you were to use Visual Studio for developing let’s say coded UI or performance tests you could take benefit of ‘My Work’, ‘Suspend’ & ‘Code Review’ feature as well using the Testing Tour work item.
All in all a custom work item for Testing Tours can simplify monitoring & tracking of tours! Let’s move on to the next section to see how changes made in the process template impact the TFS 2011 data warehouse.
3.6 - Verifying Changes to the TFS data warehouse
When the ‘Testing Tour’ field was created in section 3.2 we set Reporting to ‘Dimension’. This means Custom.TestingTour should have been added to the TFS Data warehouse.
Open SSMS or SQL Express and connect to the TFS_Warehouse, if you are using the Preview VM then use ‘VSALM’ as the server name, if you are not sure, open the Team Foundation Server Administration console and check the connection string to identify the db server name.
- Select from [Tfs_Warehouse].[dbo].[_WitFields] and verify you can see the Testing Tour field added to this table.
- Now that the Field has been added to the work item you can take advantage of reporting using tfs data warehouse, you can see the field Testing Tour has also been added to all the Work Item Views. For Example, Select from the [Tfs_Warehouse].[dbo].[CurrentWorkItemView] and you will see the column Custom_TestingTour
Note – In case you can’t see any values populated for the Custom_TestingTour validate that the TFS Data warehouse has processed at least once after you created the new work items in section 3.4. If you are having trouble with TFS Data warehouse consider going through the helpful guidance here.
3.7 - Writing new SSRS Reports
I will be covering reporting on Testing Tours in detail in the next part, but to get you started, I’ll show you a sample – Get All Testing Tours by Iteration for a specific Team Project. Get All details such as the current state of the test tour, effort remaining, last state, user, iteration start date, end date, etc.
<span style="color: blue;">SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
</span><span style="color: green;">-- =============================================
-- Author: Tarun Arora
-- Create date: 10 December 2011
-- Description: Get All Test Tours
-- By Iteration & Team Project
-- =============================================
</span><span style="color: blue;">CREATE PROCEDURE </span><span style="color: teal;">usp</span><span style="color: gray;">.</span><span style="color: teal;">TestTours</span><span style="color: gray;">.</span><span style="color: teal;">TestToursByProjectAndIteration
@project </span><span style="color: blue;">nvarchar</span><span style="color: gray;">(</span>50<span style="color: gray;">), </span><span style="color: teal;">@Iteration </span><span style="color: blue;">nvarchar</span><span style="color: gray;">(</span><span style="color: magenta;">max</span><span style="color: gray;">)
</span><span style="color: blue;">AS
BEGIN
SET NOCOUNT ON</span><span style="color: gray;">;
</span><span style="color: green;">-- Uncomment for Testing
-- Declare @Iteration nvarchar(max),
-- @Project nvarchar(50)
-- Set @Project = (Select ProjectNodeGUID from dbo.DimTeamProject </span>
<span style="color: green;"> -- where ProjectNodeName = 'Testing Tour - Demo' and IsDeleted = 0)
-- Set @Iteration = (select IterationGUID from dbo.DimIteration </span>
<span style="color: green;"> -- where ProjectGUID = @Project and IterationPath = '\Testing Tour - Demo\Release 1\Sprint 1')
</span><span style="color: blue;">Select
</span><span style="color: teal;">wi</span><span style="color: gray;">.</span><span style="color: teal;">[LastUpdatedDateTime]
</span><span style="color: gray;">,</span><span style="color: teal;">[Microsoft_VSTS_Scheduling_RemainingWork]
</span><span style="color: gray;">,</span><span style="color: teal;">[PreviousState]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_State]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_ChangedDate]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_Id]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_Title]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_ChangedBy]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_Reason]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_AssignedTo]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_WorkItemType]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_CreatedDate]
</span><span style="color: gray;">,</span><span style="color: teal;">[System_CreatedBy]
</span><span style="color: gray;">,</span><span style="color: teal;">[Microsoft_VSTS_Common_Activity]
</span><span style="color: gray;">,</span><span style="color: teal;">[Microsoft_VSTS_CMMI_Blocked]
</span><span style="color: gray;">,</span><span style="color: teal;">[Custom_TestingTour]
</span><span style="color: gray;">,</span><span style="color: teal;">[ProjectNodeName]
</span><span style="color: gray;">,</span><span style="color: teal;">[AreaName]
</span><span style="color: gray;">,</span><span style="color: teal;">wi</span><span style="color: gray;">.</span><span style="color: teal;">[IterationPath]
</span><span style="color: gray;">,</span><span style="color: teal;">wi</span><span style="color: gray;">.</span><span style="color: teal;">[IterationName]
</span><span style="color: gray;">,</span><span style="color: teal;">wi</span><span style="color: gray;">.</span><span style="color: teal;">[IterationGUID]
</span><span style="color: gray;">,</span><span style="color: teal;">i</span><span style="color: gray;">.</span><span style="color: teal;">[StartDate]
</span><span style="color: gray;">,</span><span style="color: teal;">i</span><span style="color: gray;">.</span><span style="color: teal;">[FinishDate]
</span><span style="color: blue;">FROM </span><span style="color: teal;">[Tfs_Warehouse]</span><span style="color: gray;">.</span><span style="color: teal;">[dbo]</span><span style="color: gray;">.</span><span style="color: teal;">[CurrentWorkItemView] wi
</span><span style="color: gray;">INNER JOIN </span><span style="color: teal;">[Tfs_Warehouse]</span><span style="color: gray;">.</span><span style="color: teal;">[dbo]</span><span style="color: gray;">.</span><span style="color: teal;">[DimIteration] i
</span><span style="color: blue;">ON </span><span style="color: teal;">i</span><span style="color: gray;">.</span><span style="color: teal;">IterationGUID </span><span style="color: gray;">= </span><span style="color: teal;">wi</span><span style="color: gray;">.</span><span style="color: teal;">IterationGUID
</span><span style="color: blue;">WHERE </span><span style="color: teal;">[System_WorkItemType] </span><span style="color: gray;">= </span><span style="color: red;">'TestingTour'
</span><span style="color: gray;">AND </span><span style="color: teal;">[ProjectNodeGUID] </span><span style="color: gray;">= </span><span style="color: teal;">@Project
</span><span style="color: gray;">And </span><span style="color: teal;">wi</span><span style="color: gray;">.</span><span style="color: teal;">[IterationGUID] </span><span style="color: gray;">= </span><span style="color: teal;">@Iteration
</span><span style="color: blue;">END
GO
</span>
By passing the Iteration path, query the DimIteration to get the IterationGuid, Iteration Start and Finish Dates. Join with the CurrentWorkItemView and get all test tour details. The execution of the above query gets the results below,
Simple & Sweet! Since the data is in the warehouse, you can take full advantage of the extensive reporting options that come with TFS. It is also possible to connect the warehouse using an excel and put together quick pivots to measure the impact test tours are having on your overall ALM process.
4. Review and What’s next?
In Part-1 of ‘Customizing TFS 2011 Scrum process template to introduce Test Tours to start exploratory testing using MTM11’ overall we explored,
- How to set up a new work item type
- Make the new work item type reportable
- Add/Update Work Item Queries
- Query the TFS Data warehouse for the new work item type.
- Download a copy of the customized process template.
This brings up close to the finish line… But not quite there yet! 
In Part 2, I’ll be diving deep to build some firm reports using both TFS Data warehouse and TFS API to effectively represent the testing tour run frequency, bug count by testing tours & testing tour coverage by stories. As part of the bigger picture the testing team should be able to run daily stand ups using the testing tour reports. If you are looking for some inspiration around using TFS API – check out this post on MTM Testing Scorecard using TFS API.
Hope you enjoyed this post! Remember to subscribe to http://feeds.feedburner.com/TarunArora. I would love to hear your feedback, if you have any recommendations on things that I should consider or any questions or feedback, feel free to leave a comment.
See you in Part II.
