Customizing the Test Status on the TFS 2010 SSRS Stories Overview Report
Posted
by Bob Hardister
on Geeks with Blogs
See other posts from Geeks with Blogs
or by Bob Hardister
Published on Wed, 04 Apr 2012 06:40:36 GMT
Indexed on
2012/04/04
17:30 UTC
Read the original article
Hit count: 246
This post shows how to customize the SQL query used by the Team Foundation Server 2010 SQL Server Reporting Services (SSRS) Stories Overview Report.
The objective is to show test status for the current version while including user story status of the current and prior versions. Why? Because we don’t copy completed user stories into the next release. We only want one instance of a user story for the product because we believe copies can get out of sync when they are supposed to be the same.
In the example below, work items for the current version are on the area path root and prior versions are not on the area path root. However, you can use area path or iteration path criteria in the query as suits your needs.
In any case, here’s how you do it:
1. Download a copy of the report RDL file as a backup
2. Open the report by clicking the edit down arrow and selecting “Edit in Report Builder”
3. Right click on the dsOverview Dataset and select Dataset Properties
4. Update the following SQL per the comments in the code:
Customization 1 of 3
…
-- Get the list deliverable workitems that have Test Cases linked
DECLARE @TestCases Table (DeliverableID int, TestCaseID int);
INSERT @TestCases
SELECT h.ID, flh.TargetWorkItemID
FROM @Hierarchy h
JOIN FactWorkItemLinkHistory flh
ON flh.SourceWorkItemID = h.ID
AND flh.WorkItemLinkTypeSK = @TestedByLinkTypeSK
AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126)
AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
JOIN [CurrentWorkItemView] wi ON flh.TargetWorkItemID = wi.[System_ID]
AND wi.[System_WorkItemType] = @TestCase
AND wi.ProjectNodeGUID = @ProjectGuid
-- Customization 1 of 3: only include test status information when test case area path = root. Added the following 2 statements
AND wi.AreaPath = '{the root area path of the team project}'
…Customization 2 of 3
…
-- Get the Bugs linked to the deliverable workitems directly
DECLARE @Bugs Table (ID int, ActiveBugs int, ResolvedBugs int, ClosedBugs int, ProposedBugs int)
INSERT @Bugs
SELECT h.ID,
SUM (CASE WHEN wi.[System_State] = @Active THEN 1 ELSE 0 END) Active,
SUM (CASE WHEN wi.[System_State] = @Resolved THEN 1 ELSE 0 END) Resolved,
SUM (CASE WHEN wi.[System_State] = @Closed THEN 1 ELSE 0 END) Closed,
SUM (CASE WHEN wi.[System_State] = @Proposed THEN 1 ELSE 0 END) Proposed
FROM @Hierarchy h
JOIN FactWorkItemLinkHistory flh
ON flh.SourceWorkItemID = h.ID
AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
JOIN [CurrentWorkItemView] wi
ON wi.[System_WorkItemType] = @Bug
AND wi.[System_Id] = flh.TargetWorkItemID
AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126)
AND wi.[ProjectNodeGUID] = @ProjectGuid
-- Customization 2 of 3: only include test status information when test case area path = root. Added the following statement
AND wi.AreaPath = '{the root area path of the team project}'
GROUP BY h.ID
…
Customization 2 of 3
…
-- Add the Bugs linked to the Test Cases which are linked to the deliverable workitems
-- Walks the links from the user stories to test cases (via the tested by link), and then to
-- bugs that are linked to the test case. We don't need to join to the test case in the work
-- item history view.
--
-- [WIT:User Story/Requirement] --> [Link:Tested By]--> [Link:any type] --> [WIT:Bug]
INSERT @Bugs
SELECT tc.DeliverableID,
SUM (CASE WHEN wi.[System_State] = @Active THEN 1 ELSE 0 END) Active,
SUM (CASE WHEN wi.[System_State] = @Resolved THEN 1 ELSE 0 END) Resolved,
SUM (CASE WHEN wi.[System_State] = @Closed THEN 1 ELSE 0 END) Closed,
SUM (CASE WHEN wi.[System_State] = @Proposed THEN 1 ELSE 0 END) Proposed
FROM @TestCases tc
JOIN FactWorkItemLinkHistory flh
ON flh.SourceWorkItemID = tc.TestCaseID
AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126)
AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
JOIN [CurrentWorkItemView] wi
ON wi.[System_Id] = flh.TargetWorkItemID
AND wi.[System_WorkItemType] = @Bug
AND wi.[ProjectNodeGUID] = @ProjectGuid
-- Customization 3 of 3: only include test status information when test case area path = root. Added the following statement
AND wi.AreaPath = '{the root area path of the team project}'
GROUP BY tc.DeliverableID…
5. Save the report and you’re all set. Note: you may need to re-apply custom parameter changes like pre-selected sprints.
© Geeks with Blogs or respective owner