PI Status Report

Select report parameters
Sample report

Internal Link

http://chd1tfs01/Reports/browse/TfsReports/DefaultCollection/EPS

Inspiration

Jane Johnson, a SCRUM master in Chandler, wants to be able to easily see how much progress a team is making toward completing each PI Objective throughout a PI. To view the progress in TFS, Jane needs to see the PI Objectives, their related Features, and the child Product Backlog Items (PBIs) in one view. The TFS Query Builder's tree view only allows for Parent/Child or Duplicate Of/Duplicate tree views, so she wasn't able to easily query for the work items she needed.

What it does

The PI Status Report uses SQL Server Reporting Services (SSRS) to query the TFS_Warehouse database for a team's PI Objectives, related Features, and child PBIs for a given quarter. It calculates the team's progress for each PI Objective and each Feature using the completed story points for the related PBIs.

How we built it

We used SQL Server Management Studio to build and test our query. Next, we created a SSRS project in Visual Studio to build the report. Lastly, we deployed the SSRS report to the TFS Reporting page.

Challenges we ran into

The first challenge we ran into was around grouping and sorting the TFS work items so that it showed the cascading Features and PBIs for each PI Objective. We ended up sorting by PI Objective ID, then Feature ID, then sorted by an assigned "depth" level for each type of work item.

The second challenge we ran into was getting the right permissions to the TFS Reporting page. Since no one has really used the TFS Reports, we didn't have access to view the page or to publish a report to it.

The last challenge we ran into was changing the percentage calculations so that Stretch Objectives wouldn't negatively impact the overall PI Completion percentage. After talking to Rapana, we learned that the overall PI Completion percentage is based on business value. So, to avoid confusion and stick to the original goal of displaying progress for each PI Objective, we removed the overall PI Completion percentage from the report.

Accomplishments that we're proud of

Defining and completing all Minimum Viable Product (MVP) and Nice-to-Have requirements for our Hackathon project. We also are proud to know that we built a tool for the SCRUM masters that will actually be used to help the teams.

What we learned

We gained exposure to new tools and technologies that we don't usually work with (Katy gained experience with SQL, Shawn gained experience with GitHub).

What's next for PI Status Report

Color code rows, possibly matching TFS color scheme Drilldown groups for report UI Email subscriptions User guide/notes about the report Add report link to Express dashboard Percent complete by actual business value Convert query to stored procedure