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

Filed under:

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”

image

3. Right click on the dsOverview Dataset and select Dataset Properties

image

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