Creating Agile Project Reports with TFS and Crystal Reports - Part 2
page 3 of 5
by Eric Landes
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21387/ 70

Scenario Stories report

Our first Report is used when you need to post your stories somewhere in your war room. For anyone running an agile project utilizing stories, you want to post those stories in a visible manner.  This communicates in an "in your face manner" the status of the project, as soon as a team member enters that room.  For that reason, being able to print stories out from your TFS database is important.

As mentioned in the first part of this series, it is assumed that you are using the standard TFS process template MFS Agile, to capture stories as scenarios.  If you customize your own template to follow your process more closely, the concepts discussed here should help in creating the report.  But the actual field names may be different.

Fields used for Scenario Stories report

To create your report, first open up Crystal XI.  Next, select "Standard Report Wizard" under the New Report section.  When asked for the connection information, select OLE DB and the "Microsoft OLE DB for SQL Server" connection driver.  Then put in your TFS SQL Server name and enter your login information.

For the database, select TfsWorkItemTracking.  Keep in mind that we are pulling data not from the warehouse, but from more of a transactional database.  Microsoft may change this in upgrades, so beware.  We are basing this off a view, so hopefully you are a little protected, just make sure to test this when upgrading in the future.

After you select the database, select the view "WorkItemsLatestUsed" to base the report on.  Use the following fields:

·         System.IterationPath (to get the project name)

·         System.ID (Unique number for the Story)

·         System.WorkItemType (to filter it by Scenario)

·         System.State (to show whether this is an active story or completed)  

·         System.Reason (if state is set to Resolved, Reason displays if it is completed or removed)

·         Microsoft.VSTS.Common.Rank (this is where the Iteration number is stored)

·         Microsoft.VSTS.Common.RoughOrderOfMagnitude (this contains the score for this particular story)

While stepping through the wizard, do not group anything, but on filter select System.WorkItemType.  Make this "Is equal to" and enter "Scenario" in the textbox for the filter expression.

After selecting those fields, then you need to add 3 formulas.  The first one is for the Score and is called ScoreText. 

Listing 1

"Score = " + ToText({WorkItemsLatestUsed.Microsoft.VSTS.Common.RoughOrderOfMagnitude})

Then we add the formula to show the iteration.  Below we have the formula used for displaying the iteration.

Listing 2

"Iteration " + {WorkItemsLatestUsed.Microsoft.VSTS.Common.Rank}

Finally, we create a formula Status.  This formula checks to see what the state is.  If the state is Resolved, then this formula will show the Reason field.  The reason field will have completed, removed or other options related to the resolved option.

Listing 3

IIF ({WorkItemsLatestUsed.System.State}= 
 "Resolved",{WorkItemsLatestUsed.System.Reason},{WorkItemsLatestUsed.System.State} )

The wizard leaves you in a layout mode where you need to move some fields around.  For this layout we keep everything in the detail area and put in the ID the Iteration formula, Status formula, and ScoreText formula in the detail.  The Title is put there as well.  You can add the assigned to here also.

See Figure 1 for the look of the report and download an example of this layout here.

Figure 1

View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2019  |  Page Processed at 2019-09-15 10:35:50 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search