The series I just recently completed showed how to create some reports for your agile projects using TFS and Crystal Reports.  This article continues that tradition of using TFS and Crystal Reports this time to create story cards from the TFS datastore.

In part 1 of our series we went over the premise of using Team Foundation Server (TFS) and Crystal Reports to create reports for agile projects.  In the 2nd part of the series, we covered 2 reports:  A Story Details Backlog report to allow you to distribute your story backlog to remote users.  The second story covered was a Current Iteration report to report the status of your current iteration, and a breakdown report. 

In part 3, we explained how to create a burndown chart utilizing TFS and Crystal Reports. 

For this article, we will create a report that allows the project manager to post Story cards from data in TFS on their war room boards.  This report will also address issues related to differences in the appearance between standard story cards, and these printed out story cards.

·         Team Foundation Server (TFS)

·         SQL Server 2005

·         Visual Studio 2005

·         Crystal Reports XI

As detailed in Part 1, we are using TFS to capture the stories.  I capture stories in TFS for many reasons.  One of the reasons includes the ability to have these stories in a data store that gets backed up etc.  Another reason is to allow reporting in different ways using SQL.

For off-site customers there are reports that these customers can see.  The bottom line for me is that I am more comfortable storing my stories into a database rather just keeping them in index cards that I can lose. 

We'll go through the steps of creating a report that will make stories for you. First off the Story report is designed to print out story cards that can be pinned on your story boards.  If you are familiar with agile projects using XP or similar methodologies, you probably are familiar with how story cards are normally created.  Most teams that use stories use note card and write the story on that card.  Then they place the story card on a board that contains your backlog. 

In our scenario we enter the story information into a scenario work item in Team Foundation Server.  And then we use this report to print out the stories.  One of the original story reports I created, did not have any markings that show it as unique.  After printing that out and putting these stories on the board for the project, Some of the feedback received from customers indicated that it was hard to differentiate between these printed story cards. 

Normally story cards that are written on tend to take on a look of their own, with different markings on them.  I thought it might be good to give some uniqueness to laser printed story cards.  When we get to creating the report, you'll see the route I took to get around this issue.

Let's look at the SQL statement used to run this report.

Listing 1

select      [System.Id]
            , [System.Title]
            , [System.CreatedDate]
            , [System.TeamProject]
                                , [Microsoft.VSTS.Common.RoughOrderOfMagnitude]
                                , [Microsoft.VSTS.Common.Rank] as Iteration 
            , [System.State]
                                , [System.Reason]
            , [Microsoft.VSTS.Common.ClosedDate]
from dbo.WorkItemsLatestUsed
where [System.WorkItemType] = 'Scenario'

In our SQL Statement, we are pulling the ID, Title, Microsoft.VSTS.Common.Rank for the iteration, and .VSTS.Common.RoughOrderOfMagnitude as the Score.  This SQL statement gives us the basis for the story Report.

With that SQL statement, we can create a report that contains stories the size of 4X6 index cards.  Let's set this up using a normal 8.5" X 11" page, I do that then cut out the story cards when I need them. 

First in the report, we need to add the following fields.  The System.ID, System.Title, Microsoft.Common.VSTS.RoughOrderOfMagnitude, and Iteration from the command.  The ID field is helpful to find the story in TFS later during your planning meetings with customers.  Since you may keep details in the description field.  It's easier to find those stories, especially as you collect many of these stories.  You may have as many as 200 stories in an average project, and this is a good place to do this.

Illustration 1 shows where to place the fields.  @IdNum (a formula that adds a # sign in front of the number), should be in the center at the top.  System.Title goes in the middle, The Score and Iteration number are placed at the bottom of the card.  All fonts are Arial with a 36 point unless otherwise noted.  Let's add some more formatting.

Figure 1

First we need to add a distinctive look so that each story will stand out in a planning meeting.  To do this let's use the Wingdings fonts that come with many Windows fonts.  If you don't have the Wingdings font, look for a symbol type font that can be unique.

The other thing we want to do to make the look of each card distinctive, is to give a different color to each card.  To simulate a random color, we use a formula on the font color to make it different for every 10th card.

To do this, we'll use the System.ID field, which will always be different.  This field is different then the first System.ID field we set.  We put this in the top left hand part of the card.  Let's set that System.ID font to Wingdings in the properties section.  Then in the Font Color, let's click the Formula button, and place the formula in Listing 2. 

Listing 2

iif(mid (cstr( {command.System.Id} ),3)="1", crBlack,
 iif( mid( cstr( {command.System.Id} ),3) ="2", crRed, 
iif( mid( cstr( {command.System.Id} ),3) ="3" , crBlue , 
iif( mid( cstr( {command.System.Id} ),3)="4", crGreen, 
iif( mid( cstr( {command.System.Id} ),3)="5", crPurple, 
iif( mid( cstr( {command.System.Id} ),3)="6", crYellow, 
iif( mid( cstr( {command.System.Id}),3) ="7", crGray, 
iif( mid( cstr( {command.System.Id} ),3)="8", crMaroon, crTeal))))))))

We also want to put this same formula in the color formula for System.Title.  This keeps the title the same color as the unique marking.  Now let's copy that System.ID with the wingding font to the right top corner, then the bottom 2 corners of each side.  Now your card is ready to print!

[Download Report]

In this article, we showed how to use Team Foundation Server, and Crystal Reports to create a Story Card.  This is useful for anyone using TFS as the repository for their project information for their agile project.  This card can be used for anyone who sees benefit of using a printed card over written index cards for your project.  I hope this is helpful for anyone doing XP style agile development, and happy coding.