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.
