TFS Reports using XML and XSL

Article
06/11/2008

There is a lot of ways to get data out of the TFS Reporting Warehouse and into a report. Things like Excel and Reporting Services RDL Reports work well for people. Typically people use these tools to pull data from the Cube and display it in a consumable format. Of course these aren’t the only way to get data and display. Here I will look at using the relational Warehouse as a source and use simple XSL transforms to pretty up the data. This method will not work for everyone – in my opinion it is really only suitable for fairly simple reports.

Here I will create a report that displays my current work items (task, bugs, etc…). This is something like a “what am I supposed to be doing” report.

First we need to understand how to get the data from the relational warehouse. I will not cover all the ins and outs of the warehouse schema here. If you are looking for further information on this front take a look at the MSDN documentation (https://msdn.microsoft.com/en-us/library/ms244691.aspx) describing the various tables (Facts, Dimensions, and Fact Links) and their columns. I will look at getting Work Item data so here are the relevant tables:

Person – People’s names, Email, etc…
Work Item – Work item dimension information. This includes things like the Work Item ID, Revision, Title, etc…
Current Work Item – Current Work Item Fact information. This includes fact information and links to the appropriate dimensions. We will join this to the Work Item and person table to get the data we need in our report.

To get our report data our query would look like (alright we don't need CreatedData, but I am going to leave it in there):

SELECT * FROM
(
SELECT wi.[System_ID]
,wi.[System_State]
,wi.[System_WorkItemType]
,wi.[System_Title]
,cwi.[System_CreatedDate]
,DATEDIFF(d, cwi.[System_CreatedDate], GETDATE()) AS [Age]
FROM [dbo].[Person] AS p
LEFT JOIN [dbo].[Current Work Item] AS cwi
ON cwi.[Assigned To] = p.[__ID]
LEFT JOIN [dbo].[Work Item] AS wi
ON cwi.[Work Item] = wi.[__ID]
WHERE p.[Person] = 'Nick Ericson' AND
wi.[System_State] <> 'Closed'
) AS WorkItem
ORDER BY WorkItem.[System_State], WorkItem.[System_CreatedDate]
FOR XML AUTO, ROOT('WorkItems')

Which results in XML that looks like:

So now we need to apply the XSLT to transform this to HTML. It is fairly simple to apply a transform via some managed code. One could use a web services to deliver the report to users with relatively little effort. Another method would be to return the XML to the browser along with the xml-stylesheet tag (i.e. the two lines below added to the head of the XML). This could also be done via a web service or ISAPI DLL. I will not go into the details of how this would work here - if there is interest let me know.

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="WorkItems.xsl"?>

where WorkItems.xsl is your XSL file.

Whichever method you choose, once the XSL (which is included below) is applied you will get the following report:

Bugs

ID	State	Title	Age (days)
14	Active	Some new work item rev again and again	89
15	Active	new bug a b c d e	85
16	Active	Test work item 0 created: 3/19/2008 9:39:05 AM	83
17	Active	Test work item 1 created: 3/19/2008 9:39:05 AM	83
18	Active	Test work item 2 created: 3/19/2008 9:39:05 AM	83
19	Active	Test work item 3 created: 3/19/2008 9:39:05 AM	83
20	Active	Test work item 4 created: 3/19/2008 9:39:05 AM	83

Tasks

ID	State	Title	Age (days)
1	Active	Set up: Set Permissions	110
2	Active	Set up: Migrate Source Code	110
3	Active	Set up: Migrate Work Items	110
4	Active	Set up: Set Check-in Policies	110
5	Active	Set up: Configure Build	110
6	Active	Set up: Send Mail to Team Members and Stakeholders about installation and getting started	110
7	Active	Create Vision Statement	110
8	Active	Set up: Create Project Description on Team Project Portal	110
9	Active	Create Personas	110
10	Active	Define Iteration Length	110
11	Active	Create Test Approach Worksheet including Test Thresholds	110
12	Active	Brainstorm and Prioritize Story List	110
13	Active	Set up: Create Project Structure	110

Now this is a fairly simple example but you can see that you can develop some reports fairly quickly using this method.

Below is the XSL. It isn’t the prettiest example out there but it is fairly straight forward to follow:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
<xsl:output method='html' version='1.0' encoding='utf-8' indent='yes'/>

<xsl:template match="/">
    <html>
      <body>
        <p>
          <h3 style="color: #3300CC">Bugs</h3>
        <table style="border-collapse:collapse;" width="100%">
          <tr style="background-color: #3300CC; color: #FFFFFF; font-weight: bold">
            <td colspan="2">ID</td>
            <td colspan="2">State</td>
            <td colspan="2">Title</td>
            <td>Age (days)</td>
          </tr>
          <xsl:for-each select="WorkItems/WorkItem[@System_WorkItemType='Bug']">
            <tr>
              <xsl:attribute name="style">
                <xsl:if test="position() mod 2 = 0">
                  background-color: #9999FF
                </xsl:if>
              </xsl:attribute>
              <td>
                <xsl:value-of select="@System_ID"/>
              </td>
              <td> </td>
              <td>
                <xsl:value-of select="@System_State"/>
              </td>
              <td> </td>
              <td>
                <xsl:value-of select="@System_Title"/>
              </td>
              <td> </td>
              <td>
                <xsl:value-of select="@Age"/>
              </td>
            </tr>
          </xsl:for-each>
        </table>
        </p>
        <p>
          <h3 style="color: #3300CC">Tasks</h3>
          <table style="border-collapse:collapse;" width="100%">
            <tr style="background-color: #3300CC; color: #FFFFFF; font-weight: bold">
              <td colspan="2">ID</td>
              <td colspan="2">State</td>
              <td colspan="2">Title</td>
              <td>Age (days)</td>
            </tr>
            <xsl:for-each select="WorkItems/WorkItem[@System_WorkItemType='Task']">
              <tr>
                <xsl:attribute name="style">
                  <xsl:if test="position() mod 2 = 0">
                    background-color: #9999FF
                  </xsl:if>
                </xsl:attribute>

Share via

TFS Reports using XML and XSL

Bugs

Tasks

Additional resources