Work item query as input for a RS report

When you want to create a report that contains detailed data, not all fields are in the TFS warehouse. Think of text fields like description, how to reproduce, and so on. To be able to add these fields to your report, you have to write a web service that reads from a Work Item Query. This web service can then be used as data source for your report.

To create this web service do the following:

1. Open Visual Studio
2. Create a new project of the type “ASP.NET web service application”, name the project ReportingService
3. Rename the Service1.asmx to WIQuery.asmx
4. Paste the following code to the WIQuery.asmx.cs file

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Web.Services;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.Text.RegularExpressions;
using System.Xml;
 
 
namespace ReportingService
{
    [WebService(Namespace = "http://www.avanade.com/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class WIQuery : System.Web.Services.WebService
    {
 
        /// <summary>
        /// Opens the stored query.
        /// </summary>
        /// <param name="tfsUri">The uri to the TFS Server (eg. http://MyTfsServer:8080)</param>
        /// <param name="teamProject">The team project where the query is located.</param>
        /// <param name="queryName">Name of the query that should be executed (case sensitive!).</param>
        /// <returns></returns>
        [WebMethod(Description = "Use the web service to execute a TFS Work Item Query. Use the following syntax as Query Text in the report (the query name is case sensitive!):<br>" +
            @"&ltQuery&gt" + "<br>" +
            @"&ltSoapAction&gthttp://www.avanade.com/OpenStoredQuery&lt/SoapAction&gt" + "<br>" +
            @"&ltMethod Namespace=""http://www.avanade.com/""  Name=""OpenStoredQuery""&gt" + "<br>" +
            @"&ltParameters&gt" + "<br>" +
            @"&ltParameter Name=""tfsUri""&gt" + "<br>" +
            @"&ltDefaultValue&gtMyTfsUri (eg. http://tfsserver:8080)&lt/DefaultValue&gt" + "<br>" +
            @"&lt/Parameter&gt" + "<br>" +
            @"&ltParameter Name=""teamProject""&gt" + "<br>" +
            @"&ltDefaultValue&gtMyTeamProject&lt/DefaultValue&gt" + "<br>" +
            @"&lt/Parameter&gt" + "<br>" +
            @"&ltParameter Name=""queryName""&gt" + "<br>" +
            @"&ltDefaultValue&gtMyQuery&lt/DefaultValue&gt" + "<br>" +
            @"&lt/Parameter&gt" + "<br>" +
            @"&lt/Parameters&gt" + "<br>" +
            @"&lt/Method&gt" + "<br>" +
            @"&lt/Query&gt")]
        public XmlDocument OpenStoredQuery(string tfsUri, string teamProject, string queryName)
        {
            // Execute the query
            WorkItemCollection workItems = ExecuteQuery(tfsUri, teamProject, queryName);
 
            // Create a new datatable based on the display fields stored in the query
            DataTable dt = CreateDatatable(workItems);
 
            // Populate the data table with the results of the query
            foreach (WorkItem wi in workItems)
                AddDataRow(dt, wi);
 
            // Convert the datatable to xml, because when returning the datatable, the 
            // report will show the schema
            return ConvertToXml(dt);
        }
 
        /// <summary>
        /// Converts a datatable to XML.
        /// </summary>
        /// <param name="dt">The datatable that will be converted.</param>
        private XmlDocument ConvertToXml(DataTable dt)
        {
            using (MemoryStream stream = new MemoryStream())
            {
                // Remove the schema from the datatable
                dt.WriteXml(stream, XmlWriteMode.IgnoreSchema);
                var xmlDoc = new XmlDocument();
                stream.Position = 0;
                xmlDoc.Load(stream);
 
                // Return the xml docuemnt that contains only the 
                // contents of the datatable
                return xmlDoc;
            }
        }
 
        /// <summary>
        /// Creates the datatable.
        /// </summary>
        /// <param name="coll">The workitem collection that contains the results of the query.</param>
        private DataTable CreateDatatable(WorkItemCollection coll)
        {
             // Create a new table
            DataTable dt = new DataTable();
            dt.TableName = "wi";
 
            // Add a new column for each visible field
            foreach (FieldDefinition field in coll.DisplayFields)
            {
                // All fields must have for its DefaultValue the value string.Empty to ensure
                // that with the conversion to XML by WriteXML the empty fields are written as
                // empty nodes . It is not possible to set String.Empty for numeric fields, so
                // that fields are typed as object.
                var column = new DataColumn
                                        {
                                            AllowDBNull = true,
                                            ColumnName = field.Name,
                                            DataType = typeof(String),
                                            DefaultValue = string.Empty
                                        };
 
                dt.Columns.Add(column);
            }
 
            // Return the table
            return dt;
        }
 
        /// <summary>
        /// Adds a data row to the datatable based on the contents of the work item. Only fields in the display fields of the work
        /// item query will be added.
        /// </summary>
        /// <param name="dt">The datatable to add the datarow to.</param>
        /// <param name="wi">The work item that will be added to the datarow.</param>
        private void AddDataRow(DataTable dt, WorkItem wi)
        {
            // Create a new array to store the values from the work item
            object[] values = new object[dt.Columns.Count];
 
            // Add for the value for each column to the array
            foreach (DataColumn column in dt.Columns)
            {
                switch (column.ColumnName)
                {
                    case "Task Type":
                        values[column.Ordinal] = wi.Type;
                        break;
                    default:
                        if (wi.Fields.Contains(column.ColumnName))
                        {
                            values[column.Ordinal] = FormatValue(wi.Fields[column.ColumnName]);
                        }
 
                        break;
                }
 
            }
 
            // Add values array to the datatable
            dt.LoadDataRow(values, true);
        }
 
        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="teamProject">The team project.</param>
        /// <param name="queryName">Name of the query.</param>
        /// <returns></returns>
        private WorkItemCollection ExecuteQuery(string tfsUri, string teamProject, string queryName)
        {
            // Open connection to TFS
            TeamFoundationServer tfsServer = new TeamFoundationServer(tfsUri);
 
            // Get the work item 'service'
            WorkItemStore workItemStore = (WorkItemStore)tfsServer.GetService(typeof(WorkItemStore));
 
            // Search the query
            StoredQuery query = FindQuery(workItemStore, teamProject, queryName);
 
            // Populate the list of variables that might be used in the query
            Hashtable context = new Hashtable();
            context.Add("project", teamProject);
 
            // Execute the query
            return workItemStore.Query(query.QueryText, context);
        }
 
        /// <summary>
        /// Finds the query.
        /// </summary>
        /// <param name="workItemStore">The work item store.</param>
        /// <param name="teamProject">The team project.</param>
        /// <param name="queryName">Name of the query.</param>
        /// <returns></returns>
        private StoredQuery FindQuery(WorkItemStore workItemStore, string teamProject, string queryName)
        {
            // For each query in the team project
            foreach (StoredQuery query in workItemStore.Projects[teamProject].StoredQueries)
            {
                // Query found: return it
                if (query.Name == queryName)
                    return query;
            }
 
            // Query not found: throw an exception
            throw new Exception(string.Format("Cannot find the query '{1}' in the team project '{0}'", teamProject, queryName));
        }
 
        /// <summary>
        /// Formats the value, so Reporting Services can understand the value.
        /// </summary>
        /// <param name="field">The work item Field</param>
        private object FormatValue(Field field)
        {
            if (field.FieldDefinition.FieldType == FieldType.Html)
            {
                // When HTML, then strip the HTML tags
                return StripHtml((string)field.Value);
            }
            else
            {
                // Else return the value
                return field.Value;
            }
        }
 
        /// <summary>
        /// Strip the HTML tags from the value
        /// </summary>
        /// <param name="value">The text to be stripped</param>
        private object StripHtml(string text)
        {
            // Replace the HTML entity &lt; with the '<' character
            text = text.Replace("&lt;", "<");
 
            // Replace the HTML entity &gt; with the '>' character
            text = text.Replace("&gt;", ">");
 
            // Replace the HTML entity &amp; with the '&' character
            text = text.Replace("&amp;", "&");
 
            // Replace the HTML entity &nbsp; with the ' ' character
            text = text.Replace("&nbsp;", " ");
 
            // Replace any <br> tags with a newline
            text = Regex.Replace(text, "<br.*>", Environment.NewLine);
 
            // Remove anything between <whatever> tags
            text = Regex.Replace(text, "<.+?>", "");
 
            return text.Trim();
 
        }
    }
}

5. Run (CTRL+F5) the solution. The webservice will now be hosted by ASP.NET Development Server
6. Open the Business Intelligence Development Studio to create a new report
7. Create a new datasource and call it WIQuery.
8. Set the type of the datasource to XML and change the ConnectionString to the url of the webservice
9. Create a new report and use the WIQuery datasource
10. Set the command text of your dataset to
    
    

    <Query>
        <SoapAction>http://www.avanade.com/OpenStoredQuery</SoapAction>
        <Method Namespace="http://www.avanade.com/" Name="OpenStoredQuery">
            <Parameters>
                <Parameter Name="tfsUri">
                    <DefaultValue>http://MyTfsServer:8080</DefaultValue>
                </Parameter>
                <Parameter Name="teamProject">
                    <DefaultValue>MyTeamProject</DefaultValue>
                </Parameter>
                <Parameter Name="queryName">
                    <DefaultValue>All Work Items</DefaultValue>
                </Parameter>
            </Parameters>
        </Method>
    </Query>

11. Change the DefaultValue of the parameters to the correct values:
    1. Uri of the TFS server
    2. Name of the Team project
    3. Name of the work item query (CASE SENSITIVE!)
12. You can now get refresh the datasource and start designing your report.

Happy reporting.