Maybe my next post will just be about why I am so excited and impressed with the out of the box templates.  But, for this first blog with my new focus, I thought I would just walk through the process I went through to create a task progress report (to enhance the out of the box Agile template).

So, I started with the MSF for Agile Development 5.0 RC template.  After reviewing the template, I came away pretty excited about many of the new reports.  I am especially excited about the reporting services reports.  The big advantage I see here is that these are querying the Warehouse directly instead of the Analysis Services Cube which means that they are much closer to real-time which I find very important for reports like Burndown and task status.  One report that I focused on right away was the User Story Progress Report.  An overview is shown below:

This report is very useful, but a lot of our internal managers really prefer to manage at the task level and either dont have stories in TFS or would like to view this type of report for tasks in addition to the User Stories.  So, what did I do?

Step 1: Download the Agile Template

In VS 2010 RC, open Process Template Manager from Team->Team Project Collection Settings.  Download the MSF for Agile Development template to your local file system.  A project template is a folder of xml files.  There is a ProcessTemplate.xml in the root and then a bunch of directories for things like Work Item Definitions and Queries, Reports, Shared Documents and Source Control Settings. 

Step 2: Copy the folder

My plan here is to make a new template with all of my modifications.  You can also just enhance update the MSF template.  However, I think it is cleaner when you start making modifications to make your own template.  So, copy the folder and name it with your new template name.

Step 3: Change Template Name

Open ProcessTemplate.xml and change the <name> of the template.

Step 4: Copy the rdl of the Report you want to use a starting point

In my case, I copied Stories Progress.rdl and named the file Task Progress Breakdown.rdl.  I reviewed the requirements for the new report with some of the users here and came up with this plan.  Should show tasks and be expandable to show subtasks.  Should add Assigned To and Estimated Finish Date as 2 extra columns.

Step 5: Walkthrough the existing report to understand how it works

The main thing that I do here is try to get the sql to run in SQL Management Studio.  So, I can walkthrough the process of building up the data for the report.

After analyzing this particular report I found a couple of very useful things.  One, this report is already built to display subtasks if I just flip the IncludeTasks flag to 1.  So, if you are using Stories and have tasks assigned to each story.  This might give you everything you want. 

For my purposes, I did make that change to the Stories Progress report as I find it to be a more useful report to be able to see the tasks that comprise each story.  But, I still wanted a task only version with the additional fields.

Step 6: Update the report definition

I tend to work on rdl in visual studio directly as xml.  Especially when I am just altering an existing report, I find it easier than trying to deal with the BI Studio designer.  For my report I made the following changes.

• Updated Fields
  • Removed Stack Rank and Replaced with Priority since we dont use Stack Rank
  • Added FinishDate and AssignedTo
• Changed the root deliverable SQL to pull @tasks instead of @deliverablecategory and added a join CurrentWorkItemView for FinishDate and Assigned to

SELECT cwi.[System_Id] AS ID FROM [CurrentWorkItemView] cwi
            WHERE cwi.[System_WorkItemType] IN (@Task)
            AND cwi.[ProjectNodeGUID] = @ProjectGuid SELECT lh.SourceWorkItemID AS ID FROM FactWorkItemLinkHistory lh
            INNER JOIN [CurrentWorkItemView] cwi ON lh.TargetWorkItemID = cwi.[System_Id]
            WHERE lh.WorkItemLinkTypeSK = @ParentWorkItemLinkTypeSK
                AND lh.RemovedDate = CONVERT(DATETIME, '9999', 126)
                AND lh.TeamProjectCollectionSK = @TeamProjectCollectionSK
                AND cwi.[System_WorkItemType] NOT IN (@DeliverableCategory)

• Added AssignedTo and FinishDate columns to the @Rollups table
• Added two columns to the table used for column headers

<Tablix Name="ProgressTable">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>2.7625in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>0.5125in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>3.4625in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>0.7625in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1.25in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1.25in</Width>
            </TablixColumn>
          </TablixColumns>

• Added Cells for the two new headers
• Added Cells to the data table to include the two new values (Assigned to & Finish Date)
• Changed a bunch of widths that would change the format of the report to display landscape and have room for the two additional columns
• Set the Value of the IncludeTasks Parameter to 1 <ReportParameter Name="IncludeTasks">
        <DataType>Integer</DataType>
        <DefaultValue>
          <Values>
            <Value>=1</Value>
          </Values>
        </DefaultValue>
        <Prompt>IncludeTasks</Prompt>
        <Hidden>true</Hidden>
      </ReportParameter>
• Change a few descriptions on how the report should be used

This is the resulting report

I have attached the final rdl.

Step 7: Update ReportTasks.xml

Last step before the template is ready for use is to update the reportTasks.xml file in the reports folder.  This file defines the reports that are available in the template.

          <report name="Task Progress Breakdown" filename="Reports\Task Progress Breakdown.rdl" folder="Project Management" cacheExpiration="30">
            <parameters>
              <parameter name="ExplicitProject" value="" />
            </parameters>
            <datasources>
              <reference name="/Tfs2010ReportDS" dsname="TfsReportDS" />
            </datasources>
          </report>

Step 8: Upload the template

Open the process Template Manager just like Step 1.  And upload the new template.

Thats it.  One other note, if you want to add this report to existing team project you will have to go into reportmanager (the reporting services portal) and upload the rdl to that projects directory.

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.