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.