TFS Reporting Recipes #2: Get Daily Values for a Date Range

Monday, October 22, 2012

Purpose One of the most commonly required features in a report is a list of the daily values of some metric over a range of dates (for example, how many open bugs were in the system at the end of the day for each day in the last month).  This query will retrieve these metric values for each day in the date range. Prerequisites TFS 2010 or higher @StartDate parameter – The beginning of the required date range @EndDate parameter – The end of the...
no comments

TFS Reporting Recipes #1: Get the Iteration Path/GUID for the Current Sprint

Wednesday, October 17, 2012

Purpose Many times you want to filter the contents of reports based on sprints or iterations (for example, the number of re-opened bugs in each sprint in the project).  In this case, you often want the default of the report filter to show the iteration path of the current sprint.  This query will retrieve the current sprint’s iteration path. Prerequisites TFS 2010 or higher The Scrum process template A @ProjectGUID  parameter – The GUID of the Team Project in which you’re interested ...
no comments

TFS Reporting Recipes #0: Introduction

Well, it’s been a while since I’ve last blogged and it’s time to get back into the rhythm!  In this series, which I’m calling TFS Reporting Recipes, I’d like to show some tricks, tips and other patterns that help me along when I’m writing reports for customers.  I almost always use the relational warehouse since I feel more comfortable in SQL than in MDX, so for the recipes I assume you’re in the Tfs_Warehouse database. Feel free to sound off in the comments if you have questions/issues that you’d like to see me address. Happy Reporting! ...
no comments

Writing a Simple TFS Report – Part 4: Publishing the Report

Friday, January 6, 2012

In the last post of this series we completed our report so that it contains all the graphical annotations we wanted.  It is now time to publish it to the server and make it available to our users.  Publishing the Report to the Server Once we have a report running on the local machine, publishing it is the easy part!  1.  Open the report that we worked on throughout the previous parts of the series 2.  From the main ribbon button, choose the Save As menu option.  The Save As Report file dialog...

Writing a Simple TFS Report–Part 3: Adding the Final Touches

Friday, December 30, 2011

In the last post of this series, we created a report that visually displayed the data we extracted from the TFS 2010 warehouse.  In this post, we’ll add some additional enhancements to the report in order to increase its effectiveness.  As you recall, we want our report to look like this: While right now our report looks like this: This means we need to add the following enhancements: 1.  Change the report’s title and fields 2.  Make sure that cells which have a reason of Fixed have a green...

Writing a Simple TFS Report – Part 2: Creating the Report

Friday, December 23, 2011

Welcome back!  In the last part of this series we discussed the actual query needed to extract the data from the TFS relational data warehouse.  In this post, we’ll see how to use this query in order to create an actual report.    Choosing a Report Authoring Tool In order to actually produce our report we need a report authoring tool.  This tool should allow us to design our report in a WYSIWYG manner and specify the query for getting the data.  We would normally choose from the following options: Business...

Writing A Simple TFS Report – Part 1: Getting the Data

Friday, December 16, 2011

In the last post we saw the final result of what we’re aiming for – the Bug’s Life report.  In this post, we’ll start working on the report by actually extracting the data. The TFS Reporting Data Sources TFS supplies us with two different data sources for reporting needs: The data warehouse – A relational data store that is designed using a snowflake schema and is named Tfs_Warehouse.  By default, this database is updated within 30 minutes of an anything ‘interesting’ happening in TFS.  We query this database using the SQL language. ...
no comments

Writing A Simple TFS Report– Part 0: Introduction

Friday, December 9, 2011

In this series of blog posts, I will show how to write a simple report that makes use of the integrated business intelligence (BI) capabilities that are available in TFS 2010.  If you’re unfamiliar with how to use this great feature, this series is for you!  This first post will describe the final “product'” we’d like to construct. What We’re Aiming For Our end result for this series is the aptly-named Bug’s Life report: For each specific bug ID, the report shows us the various states that the bug went through, along with the...
no comments