Creating a TFS Work Item Aging Report
Great post by Ruiz...
http://www.cnblogs.com/Ruiz/archive/2009/11/19/1606255.html
Looking for a way to report on the age of Work Items? For example, maybe you're looking for something like a bar graph that has several buckets.
a. Over 364
b. 180 to 364
c. 90 to 179
d. 1 to 89
And then a COUNT in each bucket.
For example, if a Work Item is open longer than 364 day, it would appear in bucket "a" as a count of 1. Say there are a total of 45 Work Items that were opened older than 364 days, then "45" would appear in column "a" now say that there are 100 work items open that are 1 to 89 days old, then 100 would appear in the "d" bucket.
----------------
After installation of SQL Server Business Intelligence Development Studio(2008), you can create TFS Reports yourself.
1) Using VS to create a Report Server Project “My Reports”
2) Right click Shared Data Sources and add following data sources
TfsOlapReportDS.rds:
Type:Microsoft SQL Server Analysis Services
Connection String: Data source=
Credentials: Use Windows Authentication(intergraded security)
TfsReportDS.rds
Type: Microsoft SQL Server
Connection String: Data source=
Credentials: Use Windows Authentication(intergraded security)
3
3.1) Select Shared data source TfsOlapReportDS as Data Source
3.2) Using following MDX script as Query
with
member [Measures].[Age]
As
IIF([Measures].[Current Work Item Count]>0,DateDiff("d",cdate([System_CreatedDate].[Date].CurrentMember .name),Now()),null)
member [Measures].[AgeRange]
as
case
when [Measures].[Age] >70 then 70
when [Measures].[Age] >60 then 60
when [Measures].[Age] >50 then 50
when [Measures].[Age] >40 then 40
when [Measures].[Age] >30 then 30
when [Measures].[Age] >20 then 20
when [Measures].[Age] >10 then 10
when [Measures].[Age] >0 then 0
when [Measures].[Age] <0>
3.3) Select Tabular as Report Type (We will delete it because we want to use chart)
3.4) Add all field to detail and then next,next…
4) Delete the table in Design mode, drop a Chart from Tool Box, set the chart type to column
5) Drop Current_work_Item_count from Report Data Window to Data Fields of the chart, and AgeRange to catagory fields.
6) Set the deploy property of the project
Right click project and then select property, in the Property dialog, set OverwriteDataSources to False, TargetDataSourceFolder to “” TargetReportFolder to
7
8) Set the data source of the report in report server.
Go to http://
