# Thursday, November 6, 2014

Final post for tonight and then I might have a little nap on the train.

On another recently created project utilising the custom Team field to scope Work Items to TFS teams rather than the Area Path I encountered another minor issue.

I created a Test Plan using Microsoft Test Manager and then clicked the Open in Web Access button

image

I received the following error in Team Web Access:

The test plan with id XX does not exist or it’s area path is not owned by the default project team.  Include it in the default project team’s owned areas and try again.

image

The error is a little cryptic as I hadn’t even started adding area paths, much less locking them down.  It was in fact, another symptom of using the custom Team field,

I hadn’t implemented the template customisation and so the Work Item definition was missing the following on the Team field:

<DEFAULT from="value" value="Unassigned" />

TFS 2013 Update 3 requires that you add your Team field to the new Test Plan Work item.  You don’t have to expose the field on the UI but it must have a default value or you will hit issues in Team Web Access when accessing those Test Plans that do not have the field populated.

Easy to resolve –

  • Run a query into Excel that brings back all the Test Plan Work Items
  • Add the Team field to the list of columns
  • Set the Team value for the Test Plans to Unassigned (or whatever you use)
  • Use witadmin.exe or the Process Template Editor to update your Work Item definitions to have a default Team value

Time for a snooze.

Cheers,

Richard



.

Thursday, November 6, 2014 8:00:00 PM (GMT Standard Time, UTC+00:00)  #    Comments [0]


If you made it through my last post, you’ll know that I recently updated a TFS 2013 RTM install to Update 2 to resolve a bug in TFS. 

During the update we have to re-apply service accounts and confirm servers etc.  At one stage we were prompted for the SQL Reporting Services Server and the reporting service password.  The issue we needed to resolve was holding up work and unfortunately we couldn’t locate the SSRS password that we needed.  I asked which was more important – getting the team working again or running reports.  The answer was obviously the former and so we took the decision to skip Reporting Services safe in the knowledge that we could deal with it later as a non-critical issue.

The next day the TFS admin had all the details he needed to re-enable reporting and got stuck into the TFS Admin Console.

MSDN: TFS 2013 - Add a report server

Edit the information to configure reporting

However, he added the server details for the data warehouse, clicked Test Connection and received the following error:

The database you specified cannot be used. The database exists, but its schema is not valid for use with Team Foundation Server.

The obvious reason for the error was that the TFS update had changed the data warehouse schema and now our existing warehouse database was out of date.

I wondered if we could update the warehouse database individually but if you understand the reporting architecture in TFS (read my other blog post if you don’t) then you’ll know that we could just blow away the existing warehouse and re-create it.  So that’s what we did, we now have the imaginatively named Tfs_Warehouse2 and Tfs_Analysis2 databases.  We hooked up reporting again, rebuilt the warehouse using the Admin Console and Bob’s your uncle.

We hit a minor snag later that the analysis database didn’t seem to have reflected the changes so we called the ProcessWarehouse web service followed by the ProcessAnalysisDatabase web service, checked everything completed successfully with the GetProcessingStatus web service (details here) and everything was working again in time for tea and biscuits!

I later found an old TFS 2010 related blog post that confirmed what had happened and that our resolution was correct.  Hurrah!

60 minutes left, let’s go for the hat-trick.

Cheers,

Richard



.

Thursday, November 6, 2014 7:30:00 PM (GMT Standard Time, UTC+00:00)  #    Comments [0]


My blog posts are like really terrible buses, you wait 8 weeks for one and now I’ve finally got a little time (3 hours on a train home from the midlands) so 2 or 3 are going to arrive at once.

A process template customisation I do regularly for customers is adding a Team field to scope Work Items to teams in TFS 2012 and TFS 2013.  Microsoft have detailed the process here so it’s not a particularly controversial thing to do and I believe it provides a better experience when you have multiple teams working against a central backlog.

I blogged recently about how to use PowerShell to automate the process of adding a team field.

I performed the customisation for three companies in the last couple of months and bumped into a some issues with Team Web Access in particular.  Even though adding a Team field is very much a valid thing to do, there can be a couple of wrinkles to iron out.

The first issue came as a bit of a shock.  The background to the problem is this - a customer has been happily using TFS 2013 on a pathfinder project with a single team.  Things have gone well and so they wish to roll out the tool to about 5 other teams.

We worked together to create a slightly customised process template which added some further hierarchy to the standard Visual Studio Scrum 2013 process template.  Without thinking, I started work using a Visual Studio Scrum 2013.3 template and when I tried to upload it to the customer’s server we had some errors as Update 3 added all sorts of test related artefacts that weren’t available with the customer’s TFS 2013 RTM version. 

That wasn’t the issue, however and I quickly made the changes to an RTM version of the VS Scrum template and uploaded it successfully.  We then created a central project to house the company’s portfolio backlog (I must add that to my things to blog about!).

We were to resume working on the new project a few days later but before then I received a frantic email from the pathfinder team saying that whenever they tried to edit an existing Work Item using the Team Web Access client they received the following error:

Team Foundation Error

The server operation failed, (Value cannot be null. Parameter name:key).

I quizzed the team on whether they had changed anything on the pathfinder project as templates are like cookie cutters, once you cut out the cookie, it doesn’t matter what you do to the cutter, it doesn’t effect the cookies you produced previously.

I went onsite and pulled down the processconfiguration.xml, categories.xml and the Work Item definitions for the pathfinder project.  Nothing had changed!

Some experimentation confirmed that uploading a new process template configured to use a custom team field had broken Team Web Access for an existing project.  Clearly that shouldn’t happen and although I we could have battled through and fixed it we took the decision to update the TFS 2013 RTM server (without knowing for sure that Update 2/3/4 had fixed the problem).  We took the decision to go with Update 2 as we weren’t quite ready for the new test artefacts and I’d recently been badly burned by an issue with Update 3 massively inflating the collection database (another thing to add to my list of blog posts to write).

The upgrade to Update 2 only caused us a short period of downtime and it was something we were planning on doing soon anyway.  The problem was resolved immediately so it was obviously a known bug which was addressed in the first update for TFS 2013 (there was no update 1 for TFS 2013).

A similar but not identical issue is discussed in the MSDN forum post here.

There are still 90 minutes left of my train journey so I may as well write up the next instalment.

Cheers,

Richard



.

Thursday, November 6, 2014 7:00:00 PM (GMT Standard Time, UTC+00:00)  #    Comments [0]


# Friday, September 12, 2014

One of the Team Foundation Server Process Template customisations I do frequently is changing a project to make use of a custom team field rather than the default Area Path to scope Work Items to teams.

If you haven’t made use of teams in TFS 2012 or TFS 2013 yet then check out these links

Multiple Teams with Microsoft Team Foundation Server 2012 & Visual Studio Scrum

TFS 2013: Collaborate using team resources

If you don’t know what is involved in customisation then you need to check out this page.

Customize a team project to support team fields

Now, you can, as with any process update, make changes offline and any subsequent projects created using that template will see the changes.  Or, you can update an existing in-flight project with template changes. 

It’s not particularly difficult to change your template to make use of a team field but it is time consuming so I wrote a PowerShell script to do it both to save me time and to play around with using PowerShell to do XML transformations.

My script downloads the required files from a Visual Studio Scrum 2013 project, backs them up, performs an XML Transform on them and uploads them to your project.  If you are using a different process template then you will need to edit the XML transforms and the selected Work Item definitions but the technique is the same.

You can call the script Add-Teams.ps1 from the command line and provide 3 parameters

Add-Teams.ps1 “http://TFS2013:8080/tfs/DefaultCollection” MyProject “C:\TemplateUpdates”

Defaults are specified at the beginning of the script so if you plan to run it using PowerShell ISE for example, then simply change the values.

        #TFS Collection Url
        [string]$CollectionUrl =          "http://TFS2013:8080/tfs/DefaultCollection",
        #Name of the TFS Team Project to transform
        [string]$TeamProjectName =        "MyProject",
        #Working folder to store the XML transforms, downloaded files and backup
        [string]$WorkingFolder =          "C:\TemplateUpdates" 

There is some (hopefully) useful logging throughout the script which uses write-verbose, so if you want to turn it on then  give the script a –verbose flag when you run it.

The script

First we create a function called Transform-XML that takes an XML file and a transform file to apply.  We’ll use it to alter the Work Item definitions, the Process Configuration and add a Global List.


function Transform-XML 
{
     Param (
            [Parameter(Mandatory=$True)][ValidateNotNull()]$Wit,
            [Parameter(Mandatory=$True)][ValidateNotNull()]$Tfm
        )


Next, the necessary Work Item definitions (Product Backlog Item, Task, Bug, Feature & Test Plan) are downloaded from your project using WitAdmin.exe (usually found in C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Witadmin.exe)


# Export Work Item definitions 
write-Verbose "Exporting PBI definition"
& $WitAdmin exportwitd /collection:$CollectionUrl /p:$TeamProjectName /f:$PBIDefinition /n:"Product Backlog Item"
write-Verbose "Exporting Task definition"
& $WitAdmin exportwitd /collection:$CollectionUrl /p:$TeamProjectName /f:$TaskDefinition /n:"Task"
write-Verbose "Exporting Bug definition"
& $WitAdmin exportwitd /collection:$CollectionUrl /p:$TeamProjectName /f:$BugDefinition /n:"Bug"
write-Verbose "Exporting Feature definition"
& $WitAdmin exportwitd /collection:$CollectionUrl /p:$TeamProjectName /f:$FeatureDefinition /n:"Feature"
# Required if using TFS 2013 Update 3 or later 
write-Verbose "Exporting Test Plan definition"
& $WitAdmin exportwitd /collection:$CollectionUrl /p:$TeamProjectName /f:$TestPlanDefinition /n:"Test Plan"

# Export Process Configuration
write-Verbose "Exporting Process Configuration"
& $WitAdmin exportprocessconfig /collection:$CollectionUrl /p:$TeamProjectName /f:$PCDefinition 

# Export Global List 
write-Verbose "Exporting Global List"
& $witadmin exportgloballist /collection:$CollectionUrl /f:$GLDefinition 

The we copy all the downloaded files into a backup directory.  If your Team project was called MyProject then all the files would be downloaded to C:\TemplateUpdates\MyProject and a backup would be copied to C:\TemplateUpdates\MyProjectBackup\110914.161115 where the date is 11\09\2014 and the time is 16:11 and 15 seconds.  This way, if anything goes wrong, you have your original files to upload.


# Create backup of process files 
write-Verbose "Backing up process files"
$timestamp = Get-Date -UFormat "%d%m%y.%T" | foreach {$_ -replace ":", ""}

Copy-Item -Path $ProjectFolder -Container -Destination "$($ProjectFolder)Backup\$timestamp" -Recurse -force

After that we call the Transform-XML function against the required files

try
{
    #Try transforming the required files and exit the script if there are any problems
    
    # Add Global List 
    write-Verbose "Adding Team Global List"
    Transform-XML $GLDefinition $GLTransform

    # Update Work Item Definitions to add new Team field and include on form 
    write-Verbose "Updating PBI Definition"
    Transform-XML $PBIDefinition $WITTransform
    write-Verbose "Updating Task Definition"
    Transform-XML $TaskDefinition $WITTransform
    write-Verbose "Updating Bug Definition"
    Transform-XML $BugDefinition $WITTransform
    write-Verbose "Updating Feature Definition"
    Transform-XML $FeatureDefinition $WITTransform
    # Required if using TFS 2013 Update 3 or later
    write-Verbose "Updating Test Plan Definition"
    Transform-XML $TestPlanDefinition $WITTransform

    # Update Process Template to make use of new Team field instead of AreaPath 
    write-Verbose "Updating Process Configuration"
    Transform-XML $PCDefinition $PCTransform
}

The XML transform for a Work Item looks like this for example


<?xml version="1.0"?>
<witd:WITD xmlns:witd="http://schemas.microsoft.com/VisualStudio/2008/workitemtracking/typedef" xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <WORKITEMTYPE name="Bug">
    <FIELDS>
    <!-- If the team field has already been added then remove it first-->
    <FIELD name="Team" xdt:Transform="Remove"  xdt:Locator="Match(name)" />
      <FIELD name="Team" refname="RippleRock.Scrum.Team" type="String" reportable="dimension" xdt:Transform="Insert">
        <HELPTEXT>Name of the team that will do the work.</HELPTEXT>
        <ALLOWEXISTINGVALUE />
        <ALLOWEDVALUES >
          <GLOBALLIST name="Teams" />
        </ALLOWEDVALUES >
        <DEFAULT from="value" value="Unassigned" />
      </FIELD>
    </FIELDS>
    <FORM>
      <Layout>
      <Group>
      <Column>
        <Group Label="Status">
          <Column PercentWidth="100">
          <!-- If the team field has already been added then remove it first-->
            <Control FieldName="RippleRock.Scrum.Team" xdt:Transform="Remove"  xdt:Locator="Match(FieldName)" />
            <Control FieldName="RippleRock.Scrum.Team" Type="FieldControl" Label="Team" LabelPosition="Left" EmptyText="&lt;None&gt;" xdt:Transform="Insert" />
          </Column>
        </Group>
        </Column>
        </Group>
      </Layout>
    </FORM>
  </WORKITEMTYPE>
</witd:WITD>


This adds a field called RippleRock.Scrum.Team to the Work Item and then adds a Field Control at the end of the Status group on the Work Item form.  I added an additional Remove transform for both elements so that if you run the script against an item that already has a Team field then it will be replaced.

Finally, we import all our definitions and we should be good to go.


# Import Global List 
write-Verbose "Importing Global List"
& $witadmin importgloballist /collection:$CollectionUrl /f:"$ProjectFolder\GlobalList.xml"

# Import Work Item definitions 
write-Verbose "Importing PBI definition"
& $WitAdmin importwitd /collection:$CollectionUrl /p:$TeamProjectName /f:"$ProjectFolder\WorkItem Tracking\TypeDefinitions\ProductBacklogItem.xml" 
write-Verbose "Importing Task definition"
& $WitAdmin importwitd /collection:$CollectionUrl /p:$TeamProjectName /f:"$ProjectFolder\WorkItem Tracking\TypeDefinitions\Task.xml" 
write-Verbose "Importing Bug definition"
& $WitAdmin importwitd /collection:$CollectionUrl /p:$TeamProjectName /f:"$ProjectFolder\WorkItem Tracking\TypeDefinitions\Bug.xml" 
write-Verbose "Importing Feature definition"
& $WitAdmin importwitd /collection:$CollectionUrl /p:$TeamProjectName /f:"$ProjectFolder\WorkItem Tracking\TypeDefinitions\Feature.xml" 
# Required if using TFS 2013 Update 3 or later
write-Verbose "Importing Test Plan definition"
& $WitAdmin importwitd /collection:$CollectionUrl /p:$TeamProjectName /f:"$ProjectFolder\WorkItem Tracking\TypeDefinitions\TestPlan.xml"

# Import Process Configuration 
write-Verbose "Importing Process Configuration"
& $WitAdmin importprocessconfig /collection:$CollectionUrl /p:$TeamProjectName /f:"$ProjectFolder\WorkItem Tracking\Process\ProcessConfiguration.xml"


Remember to follow the steps in the “Configure Team Settings” section of the Customize a team project to support team fields to alter how Work Items show up on the team backlogs.

image

Download the script and the transforms here.

I hope it saves you some time.

Cheers,

Richard



.

Friday, September 12, 2014 7:00:00 AM (GMT Daylight Time, UTC+01:00)  #    Comments [0]


# Thursday, September 11, 2014

I was working with some custom build definitions on a training course last week and one of the delegates had a failing build.  After a quick visual inspection I couldn’t spot what was wrong and so I wanted a way of easily comparing the build with my working build definition.

I knew we could pull the information using the TFS API and the IBuildDefinition Interface but I came across a flag on the TFS Power Tools (tfpt.exe) builddefinition option that I hadn’t realised was there.

tfpt.exe builddefinition /diff “<Project>\<Build Definition 1>” “<Project>\<Build definition 2>” /collection:<collection url>

This gives us a really quick and simple way of comparing the build definitions and it uses the Visual Studio file comparison to show the differences.

For more information on the command, launch a Developer Command prompt and type:

tfpt builddefinition /diff /collection:<collection url> /?

image

So, for example on my demo setup

  • Server = TFS2013
  • Collection = RippleRockCollection
  • Team Project = BuildDefinitionTest
  • Baseline Build = BuildDefinition1
  • Build for Comparison = BuildDefinition2

image

I used:

tfpt builddefinition /diff “BuildDefinitionTest\BuildDefinition1” “BuildDefinitionTest\BuildDefinition2” /collection:http://TFS2013:8080/tfs/RippleRockCollection

You’ll probably find an error saying:

Could not load file or assembly 'Microsoft.TeamFoundation.Build.Workflow, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

image

You can find Microsoft.TeamFoundation.Build.Workflow.dll in:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies

and simply copy the file to:

C:\Program Files (x86)\Microsoft Team Foundation Server 2013 Power Tools

Tfpt.exe should now launch Visual Studio and make the definitions very easy to visually compare side-by-side.

image

Cheers,

Richard



.

Thursday, September 11, 2014 9:57:47 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0]


# Monday, June 23, 2014

Most Team Foundation Server (TFS) administrators know how to license developers.  MSDN licenses are the most common, meaning that all users already have a Client Access License for Team Foundation Server, are also licensed to install a server and have access to the latest product updates and versions. 

Occasionally users misunderstand that MSDN licenses are “per developer” meaning that only the named user can access software downloads.  On the flip side, that user is then entitled to install Visual Studio on whatever machines they choose (including their home PC) but just remember that most software available for download through MSDN is for development, test and evaluation purposes - not for production.

The most common mistake users make when it comes to developer licensing is when using Team Foundation Server through Team Web Access (TWA).  There are different levels available for TWA which I will discuss later in this post.

However, when it comes to licensing non-development users such as project managers, ScrumMasters, Product Owners and project stakeholders, there is a lot of confusion.

No license (Web Access: Limited)

Having no TFS license still allows you to access some information in TFS and also to contribute to a project.  There are two options:

1.) You can access Team Foundation Server Team Web Access with “Limited” permissions (you may also hear this referred to as Work Item Web Access (WIWA) or Work Item Only View (WIOV)).  If the TFS administrator grants you access then you will be able to submit Bug Work Items or Feature Requests for example, through a web browser.   You can only view Work Items that you have created.

View work items that you have created

2.) You are entitled to view standard reports on TFS if your TFS administrator has granted you permission.  This was a licensing change back in 2012 and applies to TFS 2010 and later.

We have removed the TFS CAL requirement (you still need whatever Sharepoint/Office licensing is appropriate) for viewing reports in TFS.  This addresses a long standing concern that it was not reasonable to require a CAL for the occasional stakeholder who wanted to check a report to see progress or issues.  Add this to the Work Item Only View CAL exemption that we added a couple of years ago and you get a pretty comprehensive solution for the occasional, loosely connected stakeholder

Your team can create custom reports and make them available to you but you will not be able to create your own custom reports.

See TFS Reports for details on the standard reports available with your chosen process template.

Example of Remaining Work report

Also, have a look at the “When a Client Access in Not Required” section of the Visual Studio and MSDN Licensing White Paper for further information.

Any read-only data that comes from the Team Foundation Server SQL data warehouse or is surfaced through SQL Server Analysis Services would be a report, but custom reports could also be written to call into Team Foundation Server APIs and could also join that data with other data sources

Client Access License (Web Access: Standard)

Inexpensive Team Foundation Server Client Access Licenses can be purchased with or without Software Assurance.  If you choose to buy “License Only” (without Software Assurance) just remember that your CAL will be tied to a specific version of Team Foundation Server.  So, when your developers decide to upgrade the server to TFS 201x, technically your CAL for TFS 201x-1 is no longer valid.

A TFS CAL allows you to access all information in TFS.  You can use the client that makes sense for you including the Team Explorer for Microsoft Visual Studio.

Visual Studio Professional includes a CAL for TFS.

Visual Studio Professional or a TFS CAL gives you a Standard license for Team Web Access.  This means that you are not licensed for some features.

Review the article Team Web Access: Change access levels for a full list of features by TWA access level.

Note that the feature list changed between TFS 2012 and TFS 2013.  If you are using TFS 2012 then with a standard CAL, you are not licensed for backlog management, sprint planning and Kanban board functionality.

Visual Studio Premium/Ultimate/Test Professional License (Web Access: Full)

You can view a breakdown of features available in the various versions of Visual Studio here.  Additional features are available through Team Web Access if you are licensed for one of the following as they give you Full access:

  • Visual Studio Test Professional
  • Visual Studio Premium
  • Visual Studio Ultimate

I always think there should be a name for this like SuperCAL or CAL++ because while you cannot buy it separately, it is the thing that trips most people up. 

Most notable for non-development users are the things listed on the right hand side of the following table:

Features available with Team Foundation Server 2013 2013 CAL

Features requiring Test Pro, MSDN Platforms, Premium or Ultimate

Task Boards and Kanban Boards

Backlog Management and Sprint Planning Tools

Request and Manage feedback

Test Case Management

Agile Portfolio Management

Team Rooms

Work Item Chart Authoring

If you have Product Owners or Stakeholders for example, who are licensed with a TFS CAL but want to use Team Web Access to update portfolio information or contribute to Team Rooms, they will need to upgrade to Visual Studio Test Professional as the most inexpensive way of obtaining a Full license for TWA.

Another example is testers who want to use the web-based Test Case Management functionality of TFS Team Web Access.  Even if they do not intend to install Microsoft Test Manager, they will still need to upgrade to Visual Studio Test Professional

Conclusion

Licensing Visual Studio/TFS/MSDN is probably not as complicated as you think but there are many permutations of the way people use the software and the licensing reflects that. 

If you are in any doubt then refer to the Visual Studio and MSDN Licensing White Paper (mmmmmmm, 34 pages of licensing information), contact Microsoft or your Reseller.

Cheers,

Richard



.
Tags:

Monday, June 23, 2014 3:57:58 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0]


# Thursday, May 29, 2014

*Check out the 2015 update to this post

I’ve been answering a lot of questions on Team Foundation Server reporting over the last couple of weeks so I thought I’d put some of that information in a blog post.

Team Foundation Server is an amazing tool for storing information about your development projects and all that information can be retrieved if you know what the options are.

This post is targeted at TFS 2013 but most of it is relevant for TFS 2010 & TFS 2012

How does data flow in TFS?

This is probably the most concise reference of TFS reporting architecture and the various options that I can find: Create, customize, and manage reports for Visual Studio ALM

 

image

In a nutshell, when you work as normal in TFS - checking in source code, executing builds, running tests and completing work - all that information is being stored in transactional, operational stores within the Team Project Collection database (there is a separate collection database for each Team Project Collection that you create). 

These stores are designed to provide high transaction speed and data integrity in normalised tables meaning data isn’t duplicated.  As a result, it’s not very easy to work with these tables, it’s difficult to know which tables to use to get the data you want and queries can be slow because of all the joins required. 

A number of data adapters then populate a single, relational, data warehouse with the latest data from the stores.  Note that reportable data from all team project collections is published to that warehouse (Tfs_Warehouse) meaning that you can report across multiple collections (obviously, you should be careful who you give permissions to!)

Tfs_Warehouse has a schema that is much easier to understand because it is optimised for queries and reporting instead of transactions.  Additionally, this database can be on a completely different server so queries won’t slow down TFS.  The data warehouse is organised in a star-like schema into fact tables and dimension tables.

Finally, and every 2 hours by default, an Online Analytical Processing (OLAP) database (Tfs_Analysis) is populated to allow easy reporting on that information and this is where most of the standard reports will pull their data from.

Team Foundation Data Stores and Reports Overview

You can read more about the architecture used for TFS reporting in Components of the TFS data warehouse

Types of Report

What types of reporting are available in TFS?

  • Work Item Queries
  • Work Item Reporting
  • Out-of-the-box Excel Reports
  • Out-of-the-box SSRS Reports
  • Team Web Access Charting
  • Team Web Access standard reports
  • Custom Excel Reports
  • Custom SSRS Reports
  • SQL Queries
  • TFS API
  • REST API

Let’s look at them in a little more detail

Work Item Queries

Work Item queries are probably the simplest way of pulling information from TFS.  The data retrieved is always up to date as queries do not rely on the data warehouse. 

There are 3 types of query

For more information on Work Item Queries see Query for work items

TFS 2013 Update 2 adds the option to query on Work Item Tags.  If you have not installed TFS 2013 Update 2 or if you have TFS 2012 then  you cannot include tags in your Work Item Queries

Work Item Reporting

Work Item Reporting is the easiest route into custom reporting with TFS.  You can take a Flat List Query and within Excel click the New Report button on the Team menu

image

or in Visual Studio, click the Create Report in Microsoft Excel

image

From there you can choose to generate a report for the current state and/or the trend over time

Expanded New Work Item Report dialog box

TFS will generate a workbook with a number of reports on separate worksheets and a table of contents.  You are free to customise any of these reports and save the result for later use.

Here is an example of editing an Excel report Custom Burnup chart with TFS and Excel

For more detail, see Creating Reports in Microsoft Excel by Using Work Item Queries

Out-of-the-box Excel Reports

Depending on your chosen process template, and if you have installed SharePoint as part of your TFS deployment then you may have access to a number of standard Excel reports. 

These are included with both MSF templates (Agile & CMMI) but not with the Visual Studio Scrum template.  That said, with a little tweaking they could work with the Scrum template (eg, swap references to Story Points field used by MSF Agile to the Effort field used by Scrum)

They can be accessed through the SharePoint portal or from the Documents tab in Team Explorer.

Here is the Task Progress report as an example.

Task Progress Excel Report

See Excel Reports for more detail

Out-of-the-box SSRS Reports

Again, depending on your chosen process template (and assuming you have installed Reporting Services) then you will have access to a number of reports automatically when you create your Team Project.  They can be accessed via Web Access, SharePoint and Team Explorer.

View, upload, and organize reports (Reporting Services Reports)

Open the team project report site

For example, this is the standard Backlog Overview report available with the Scrum process template

Backlog Overview report

For a full list of available reports see Reports (SQL Server Reporting Services)

To view the reports, you must be assigned or belong to a group that has been assigned the Browser role in SQL Server Reporting Services. For more information, see Grant permissions to view or create reports in TFS.

Team Web Access Charting

Charts are new in TFS 2013 and are a bit like Work Item Reports but a lot simpler to create and not as customisable or detailed.  The big benefit is that you can pin these charts to your Home page in Team Web Access (you’ll need TFS 2013 update 2 for that) so they become very visible to your team

My good buddy, Colin, has an excellent write up on reports here although it pre-dates Update 2 so doesn’t mention pinning to the home page or customising colour on the reports (read about that here)

image

You can view charts with a Standard TFS Client Access License but to create them you must have a Full license which means Visual Studio Premium, Ultimate or Test Professional.  See more about Access Levels for Team Web Access here.

Team Web Access Charting

Team Web Access standard reports

Team Web Access has some useful reports which can be accessed very easily.  The reports are non-customisable but the good thing about them is that they do not rely on the data warehouse cube so they are never out of date.

Velocity

The Velocity diagram shows the number of Story Points (or whatever unit you are using to estimate User Stories/Backlog Items) scheduled and completed across iterations.  This will help with forecasting and release planning for your project.

Cumulative Flow Diagram

The CFD Diagram shows up to 30 weeks of data displaying the number of Work Items and their state over a date range.  If bulges are showing up on your CFD it could be an indicator of a bottleneck in your development process

Example CFD chart after a week

TFS 2013 Update 2 adds the ability to specify a start date for your CFD which can be very useful if you have a long running project.

image

Sprint Burndown

Finally, your Sprint Burndown shows the number of hours (assigned to the Tasks) your team has committed to in the sprint.  As work is completed (or added) then the value for Remaining Work on the Y-axis will change and an ideal trend line will show you when you are likely to complete the work if the current work rate continues

Sprint progress chart showing ideal trendline and actual work completed

TFS 2013 Update 2 finally added the much requested feature to be able to remove weekends from the TWA Burndown chart.  This means the burndown does not show the “steps” of a flat line when no work is completed at the weekend which could be a little demoralising.  Giles covers it in more detail here

image

Custom Excel Reports

Now you get into some proper custom reporting.  I still feel that the Work Item Query Reports covered earlier in the article are the easiest way to get started with Excel but you can connect to the Analysis Database manually if you like.

Take a look at Create a Report in Microsoft Excel for Visual Studio ALM which describes how to connect Excel to the TFS Analysis database.  Doing it this way means you are creating a report from scratch which is great if you know what you want but there is definitely a steeper learning curve.  Start with reading Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

Custom SSRS Reports

If you have a SQL Server Reporting Services expert on your team then they may prefer to generate a report that way.  The benefit of using SSRS is that the report can be published to Team Foundation Server and it will be available alongside the OOB Reports in the Reports tab of Team Explorer or the Reports website.  It also allows for much more detailed reports than excel and allows you to plot multiple data points on the same report.

You can use:

Both report authoring tools can target Tfs_Analysis and Tfs_Warehouse although you may find Report Designer more complex to begin with.  You can view a comparison of the two tools here.

It may be easier to start with an existing report and customise it.  As well as the OOB reports, there are a number of sample reports available such as the Community TFS Report Extensions

Create a Detailed Report using Report Designer

Table reference for the relational warehouse database for Visual Studio ALM

Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

If you create a custom report then you may want to add it to your Process Template so it is automatically available for any new projects created.

Add reports to the process template

SQL Queries

The majority of the reports we’ve discussed so far pull their data from the Analysis Services OLAP cube but although it’s generally more difficult, there is no reason why you can’t grab data directly from the relational data warehouse

The TFS Reporting Guide is generally a good resource for TFS reporting but it has some good examples of running T-SQL queries directly against the TFS Data Warehouse relational database.  For example, check out the Mean Time Between Failure (MTBF) Report which uses the CurrentWorkItemView to retrieve the current state of each work item from the dimension table.  The sample query fetches all Work Items of type Bug that are in a Closed state in the chosen Team Project and calculates the average time difference in hours between them.

image

You can also query the TFS operational stores but it’s not advised and you should really use the TFS API instead.  For example How to generate a report of active users who log onto the TFS server

TFS API

One of the great things about TFS has always been it’s customisability and there has always been a reasonably well documented API for pushing and pulling information to/from TFS.  For example you can create a TFS Work Item with about 3 lines of C# code.  You can also use the TFS API to create reports in a supported way.  If you have a look at the WCF Service sample for a Build Duration Report in the TFS Reporting Guide Data Warehouse section.

You’ll need to become familiar with Extending Team Foundation too.

REST API

A little while ago an OData Service was released for TFS to allow clients to be created for smartphones, tablets and non-Windows operating systems as long as they supported http requests. 

Team Foundation Server OData API

OData Service for Team Foundation Server v2

TFS Reporting Guide also has a whole section on this

This has now been enhanced as described in A new API for Visual Studio Online.  However, at this stage this API is only available for Visual Studio Online but it will likely be available for on-premise installations in the next version of TFS.

Some Questions

What about Visual Studio Online?

Visual Studio Online (formerly Team Foundation Service Preview) does not use SQL Reporting Services and so there is no data warehouse to connect the standard reports.  You still have the Team Web Access reports and the option to use the TFS API but in addition it is worth checking out the very cool REST API for Visual Studio Online which should make it easier to pull information from TFS Online

What permissions do I need to view and create reports?

Permissions for Excel Reports That Connect To the Analysis Services Cube

What is the permission needed to view and create a Excel report for TFS Analysis Services cube?

If you prefer to use Report Builder to edit or add Reporting Services Reports then it is slightly different, you’d need to have your account added to the Content Manager role for Reporting Services.

Grant permissions to view or create SSRS reports in TFS

I’m using Microsoft Test Manager with the Visual Studio Scrum template and the OOB reports are lacking.  What can I do?

There are some standard reports for testing such as the Test Plan Progress Report and the Test Case Readiness Report.  If that is not enough then it is worth checking out the (now slightly old but still relevant) blog posts here:

Test Case Management (TCM) Reporting - Frequently Asked Questions - Part 1

Test Case Management (TCM) Reporting - Frequently Asked Questions - Part 2

which gives a lot more information on custom excel reporting.  

Are there metrics associated with Builds and Continuous Integration and can we access historic build success ratios and failures etc.?

There is a full build log with diagnostics available for all builds (you can set a retention policy on them) and also the information is published to the data warehouse.  There are OOB reports (Build Summary, Build Success over Time) and options for custom reporting as discussed above.  See Analyze and report on build details and build coverage using the Build perspective for more detail.

I want to my reports to update more frequently

You can change the process control setting

Managing the data warehouse and analysis services cube

Change a Process Control Setting for the Data Warehouse or Analysis Services Cube

My TFS data warehouse is broken

Check your account permissions, review log files and rebuild the warehouse.

Manually process the data warehouse and analysis services cube for Team Foundation Server

TFS Admin Console - Rebuild the Data Warehouse and Analysis Services Cube

TFSConfig Comman Line - RebuildWarehouse Command

I don’t have Reporting Services or SharePoint installed

No problem, get them installed, start generating your reports or sharing your Excel workbooks with your colleagues.

Install Reporting services

Install SharePoint

Upload reports to a team project

Summary

Hopefully someone will find this post useful and I know I am likely to redistribute a lot of the information within it in the future.  You can see that there are many options for pulling data and generating reports from Team Foundation Server and you just need to figure out which best suits your needs.

Thanks,

Richard



.

Thursday, May 29, 2014 12:41:49 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0]


# Friday, May 16, 2014

I was doing a little custom reporting using Report Builder today and when I came to preview my report I received an error message.

image

The permissions granted to user 'TFS2013\user' are insufficient for performing this operation.

image

System.Web.Services.Protocols.SoapException: The permissions granted to user 'TFS2013\user' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'TFS2013\user' are insufficient for performing this operation.
   at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateReportEditSession(String Report, String Parent, Byte[] Definition, String& EditSessionID, Warning[]& Warnings)
   at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateReportEditSession(String Report, String Parent, Byte[] Definition, String& EditSessionID, Warning[]& Warnings)
----------------------------
The permissions granted to user 'TFS2013\user' are insufficient for performing this operation.

That seemed odd as I hadn’t messed around with security settings and it was working recently.  Still, just to be on the safe side, I reviewed the article:

Grant permissions to view or create reports in TFS

Everything looked fine so I spent some time playing around with security settings and doing a little web searching.  I found an old stackoverflow post which talked about deleting the encryption key and lots of other things that seemed unlikely.

So I grabbed a cup of coffee and went outside for some fresh air and the answer hit me!

image

Simple as that, and kind of obvious - Run Report Builder as administrator.

Doh!  Hopefully this blog post will save somebody a little time and at least it has broken my blogging drought. 

Cheers,

Richard



.

Friday, May 16, 2014 7:26:55 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0]


# Friday, October 18, 2013

I was delivering a training course on TFS 2012 this week and one of the delegates came across some unexpected behaviour that I hadn’t noticed before.

We had added the Work Items Check-in Policy to version control earlier in the day and were looking at automated builds.

image

With TFS you can perform a private build (or buddy build) to ensure that your proposed changes integrate successfully before you check them in.  You can also select the option to Check in changes after successful build which turns it into a kind of voluntary Gated Check-in.

image

This is what my delegate did and as his build completed successfully, his shelveset was committed to version control.  Somebody then pointed out that as the changes were not associated with a Work Item there should have been a policy warning like the one below.

image

I can’t say I have ever noticed this behaviour before and it took me a minute to realise the reason behind it.  Check-in policies are evaluated on the client side so for example if you use the Code Analysis Policy then it is your responsibility to run the code analysis rules against your code before check-in.  The server will not evaluate the policy for you, it will simply check to see if you have passed the rules locally.  The check in that happens as part of the private build happens server side so it did not have to pass the client side check-in policies.

You can of course control permissions on builds or ensure that the build also runs a suite of unit tests but it’s one to be aware of.

Richard



.

Friday, October 18, 2013 4:44:26 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0]


# Tuesday, July 2, 2013

Recently I’ve been working with a customer on migrating their existing Team Project to a new Process Template on Team Foundation Server 2012. 

We’ve decided to go with the TFS Integration Tools to move selected Work Items and code across to a new project in a new Team Project Collection.  There were a number of alternative options open to us but this is a large project that has been migrated through the versions of TFS and we’re going to take the opportunity to clean it up and consolidate.

I spent some time mapping their existing Microsoft Solutions Framework for Agile Software Development 6.1 project to Microsoft Visual Studio Scrum 2.2.  I had to take into consideration some custom fields that had been added to the template and also some legacy fields that had been left hanging around after previous migrations.

The customer is not a heavy Visual Studio user, doing most of the development through Eclipse and as a result they are licensed for Visual Studio Professional 2012.  In turn this means that their developers have “Standard” licenses for Team Web Access

As part of the work we have been doing to transform their development process we have a need to use both the Product Backlog view and the Kanban board, both of which are only available to users who are “Full” licensed users of TWA. 

The cost of upgrading the developers to Visual Studio Premium was prohibitive as they will not make use of the additional Visual Studio features and the current Microsoft licensing did not give us any other option.

I evaluated both Urban Turtle and the Eylean products and although they both have their plus points and are very economically priced, neither gave us exactly what we were looking for.

Last week at the Build Windows Conference, Microsoft released a Preview of Team Foundation Server 2013 which also has a Go-Live license

Ordinarily it would be a pretty gung-ho decision to move your business onto pre-release software but in this case it is worth considering.  The reason is that Microsoft have listened to the feedback and re-jigged the licensing for TWA. 

TFS2013 now provides both the Kanban Board and the Backlog & Sprint Planning Tools to “Standard” users (VS Pro) and rightfully provides some very cool additional features (Portfolio Management, Team Rooms, Test Case Management, Feedback Management) to “Full” users (VS Premium, Ultimate & Test Pro

 clip_image002  

TFS 2012 Team Web Access Levels

TFS 2013 Preview Team Web Access Levels

As a result, there is a strong case to move the project to TFS2013 and although the decision on which way to go has not been made yet, I was worried that the TFS Integration Tools mapping that I had written would need a lot of work to update it for the Microsoft Visual Studio Scrum 3.0 Template.

I downloaded the Scrum 2.2 and Scrum 3.0 Templates and performed a Diff on the folders.

image

There were some minor build changes and some changes to allow for the new Git support but I was particularly interested in the Work Item and Reporting changes. 

Release Burndown report – Order by Iteration Path

image

Minor change but hopefully a helpful one as I have run into this problem before and presumably this small change to the rdl file will resolve it.

New Category – Feature Category

image

New Work Item Type - Feature

image

Both of these have been added to support the new portfolio management functionality in TWA.

And that’s it, so there are no field changes to the core Work Items.  Obviously it is still a Preview version of the template but it shouldn’t change significantly and I can make any tweaks if it does.

If you’re in a similar position in that you would like some of the “Full” features from TWA 2012 but the Premium upgrade does not make financial sense then TFS 2013 could give you what you need.

Cheers,

Richard



.

Tuesday, July 2, 2013 2:56:08 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0]