Showing posts with label creating custom reporting. Show all posts
Showing posts with label creating custom reporting. Show all posts

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=; 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

TFS Report Developer Resources

A really informational post by, Mauli Shah...

Videos/Samples:
Video for creating custom reports:
http://teamsystemrocks.com/files/10/team_foundation_server/entry164.aspx

MSDN article on creating SSRS reports:
http://msdn.microsoft.com/msdnmag/issues/06/06/DataPoints/default.aspx


Webcasts about SSRS:
http://www.microsoft.com/events/series/sqlserverbi.mspx#SQLServerReportingServices


SSRS tutorials:
http://msdn2.microsoft.com/en-us/library/ms170246.aspx


SSRS report samples for TFS:
http://blogs.msdn.com/tompatton/archive/2006/05/07/591713.aspx


Documentation:
TFS Warehouse documentation:
http://msdn2.microsoft.com/en-us/library/ms244696(VS.80).aspx


TFS General documentation:
http://msdn2.microsoft.com/en-us/library/ms181232(vs.80).aspx


How to write a warehouse adapter:
http://msdn2.microsoft.com/en-us/library/bb130342(VS.80).aspx


How to make a work item field reportable:
http://msdn2.microsoft.com/en-us/library/ms194942(VS.80).aspx


SSRS Report Design: Best Practices and Guidelines:
http://www.microsoft.com/technet/prodtechnol/sql/2005/rsdesign.mspx


Article for MDX newbies:
http://www.mosha.com/msolap/articles/MDXForEveryone.htm


Tutorials for AS, RS, MDX:
http://www.databasejournal.com/features/article.php/3593466


Blogs:
Mosha’s blog is great for OLAP/MDX:
http://www.mosha.com/msolap


Brian Welcker has a lot of stuff on SSRS:
http://blogs.msdn.com/bwelcker/default.aspx


Chris Hays has a bunch of “hacks” for SSRS:
http://blogs.msdn.com/ChrisHays/


Ameya’s blog has some good resources and some videos on different report authoring tools (you don’t have to just use Report Designer!): http://blogs.msdn.com/ameyab/

Read Users' Comments (1)comments

Creating and Customizing TFS Reports

This article provides an introduction to the important concepts and step by step instructions to Create and Customize Reports for Microsoft® Visual Studio® Team Foundation Server (TFS).

Click here.

Reports available:

TFS Reports

1. Bug Trends.rdl

2. Remaining Work by Count.rdl

3. Remaining Work by Size.rdl

4. Status by Area.rdl

Read Users' Comments (0)