Create TFS Report Step by Step

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

    Credentials: Use Windows Authentication(intergraded security)

    TfsReportDS.rds

    Type: Microsoft SQL Server

    Connection String: Data source=<server name>; 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 then -1
else null
end
 SELECT 
 non empty  {[Measures].[Current Work Item Count],[Measures].[Age] ,[Measures].[AgeRange]}   ON COLUMNS,
 [System_CreatedDate].[Date].[Date].AllMembers on Rows

 FROM [Current Work Item]

  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 <projectname> TargetServerURL to http://<Servername>/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://<server>/Reports/Pages/Report.aspx?ItemPath=%2f<project>%2f<Report1Name>, select properties->DataSource, select correct source “/TfsOlapReportDS”

At last, the report is like