TFS OData Services as data source for SSRS reports

In April Microsoft release a beta of TFS OData Services , enabling a simply way to query and update tfs using http Url’s and atom xml data f, even from non-Windows platforms. This is good news for everyone who wants to extend tfs to mobile devices, but I saw an alternative use for it.

Looking for a SSRS data source
If you want to create reports containing details of work item, a story card report for example, you don’t really have a good data source to base your report on. The TFS Warehouse doesn’t offer vital fields like description, history and many other fields of importance. So far the best option I’ve found is to use Ewald Hofman’s solution in this post http://www.ewaldhofman.nl/post/Work-item-query-as-input-for-a-RS-report.aspx. Although it’s a proven working solution it requires both customizations and deployment of a web services . If TFS OData services will be a part of TFS installation it could be the answer I’ve been looking for.

Installing TFS OData services
To test the new TFS OData services, I started installing it on one of our lab servers. Quite quickly it became obvious that TFS OData install is not install that simply installs the TFS OData Services on a Server. It installs a complete development environment for developers who wants to build mobile TFS applications. To get it installed I switch to a lab devbox and installed all the tools required by the install. Once installed I needed to point the TFS OData services to a tfs server, this was no problem. One thing that caused me some problems was to handle the certificates.

OData as datasource in SSRS
As Microsoft has been pushing OData quite a bit, I was expecting it to be fairly easy to consume OData services in SSRS. Sadly I was surprised to find out that SSRS can generate an OData feed, but have no guidance how to consume OData as a data sources. After quite some time searching and asking around my colleagues I found a solution. SSRS have an XML data source, and you can specify an url as connection string. You can also specify Query parameters and how to translate the returning xml using the Query in SSRS. The following XML translates the OData response to fields in SSRS:

<Query>
    <ElementPath IgnoreNamespaces="true">
        feed{}/entry{}/content{}/properties
    </ElementPath>
</Query>

OData & SSRS - Possible but not practical
So it is possible to consume OData services in SSRS, but it’s not really practical as you specify the URL in the data source. OData uses the URL as the query specification, forcing you to have one data source for each query you intend to use. It might be possible to fix this or write an custom data source adapter for OData, using SSRS extension model.

Beta 1 shortcomings
As I got a data source and a query in SSRS, I started to create a simple story card report. Quite soon I run into trouble. Some parts of the URI’s used for accessing queries is less natural, like /ColletionName/Queries('b3844370-e517-471d-a55c-d45f9bf7716d')/WorkItems This formats with guids as identifiers for queries, instead of query name, might work if coding against it, but for manually doing reporting it’s not a great experience.
The next problem is that the beta 1 of TFS OData returns a predefined subset of a work item, regardless of the work item definition, or the query specification. This is a show stopper, at least as a SSRS data source.

Conclusion
If you want to create reports containing details of work item the best option is still Ewald Hofman’s solution to create an web service to execute Work Item Queries using the TFS API. TFS OData has to develop and grow a bit before it can be used as a data source, on the other hand it’s only a beta 1 yet. The potential is there in the OData format and if its developed and delivered as a part of TFS it could be used as a standardized data source for TFS data.

How to change iteration for Burn Down reports in project portal

Last week I needed to change the default iteration for the burn down chart in the (WSS) team project portal . My first thought was that this should be a simple task done in minutes, as the burn down chart is a SSRS report and I know I can change the default parameters for SSRS reports on the properties tab of the SSRS report.

How to change default parameters for SSRS reports
The simplest way to change the default parameter for a SSRS report is to right click on the Reports folder in team explorer and select Show Report Site. Once the report site opens up in your browser, click on the report you want to change, and then the report shows, click on the Properties tab. In the left border, click on the Parameters tab. Now you can see all parameters in the report and change their default values. Some of them are easy to change, others like the iteration parameter is not expressed in a intuitive or user friendly format.

Finding the right iteration expression
To find the value for an iteration, you need read access to the TFS Cube, and a tool such as SQL Server Management Studio. To get the desired iteration value you have to browse the TFS Cube, find and browse the Iteration hierarchy, find the desired iteration and drag it over to the query area. This procedure is described in detail in a post by John Socha-Leialoha (http://blogs.socha.com/2009/10/customizing-report-parameters-for-team.html)

In my case, and I suspect in many other cases, I didn't have read access to the customers tfs cube, forcing me to find another way to get the iteration value. In other cases, access and knowledge to tools could be a show stopper.

An easier way
As I was forced to find another solution, I came up with the idea to create a custom SSRS report showing the iterations and their corresponding iteration values/expression. Creating the report was easy, and all I had to do once created was to upload it to the server and set the data source. Once done, I simply run the report, find the iteration I need and I can copy the iteration value at the same row. Next time I need to change the iteration, I simply run the report again.
I think it is a really simple and good way to solve the problem, so if you running iterations and want to change the current iteration every 2-4 weeks, grab the report from my skydrive http://cid-5d46cae8c0008cf0.office.live.com/self.aspx/.Public/AreasAndIterations.rdl

Last changed Team projects - Custom Report for tfs 2008

As a TFS admin I sometime wonder how many team projects is in use and maybe when it was last changed. Sometimes I've been forced to find out and I have created some SQL queries to run against the warehouse to figure it out.

Packaged into an custom SSRS Report
Now I have packaged those queries into a custom SSRS report for tfs2008. You download the report at my skydrive http://cid-5d46cae8c0008cf0.skydrive.live.com/self.aspx/.Public/TeamProjectLastChanged.rdl Once downloaded you simply needs to upload it to your own TFS Server and point the data source to the tfs standard TFSReportDs shared data source.

TFS2010 updated version http://cid-5d46cae8c0008cf0.office.live.com/self.aspx/.Public/TeamProjectLastChanged2010.rdl