TFS Data Warehouse Reporting Repository

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=; Initial Catalog=TfsWarehouse

Credentials: Use Windows Authentication(intergraded security)

TfsReportDS.rds

Type: Microsoft SQL Server

Connection String: Data source=; Initial Catalog=TfsWarehouse

Credentials: Use Windows Authentication(intergraded security)

3) Right Click Reports and then add a report

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 TargetServerURL to http:///Reportserver

7) Right click the report name and them click deploy

8) Set the data source of the report in report server.

Go to http:///Reports/Pages/Report.aspx?ItemPath=%2f%2f, select properties->DataSource, select correct source “/TfsOlapReportDS”

Read Users' Comments (1)comments