Work item query as input for a RS report

by Ewald Hofman 11. May 2009 15:17

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
  5. 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();
     
            }
        }
    }
  6. Run (CTRL+F5) the solution. The webservice will now be hosted by ASP.NET Development Server
  7. Open the Business Intelligence Development Studio to create a new report
  8. Create a new datasource and call it WIQuery.
  9. Set the type of the datasource to XML and change the ConnectionString to the url of the webservice
  10. Create a new report and use the WIQuery datasource
  11. 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>
  12. 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!)
  13. You can now get refresh the datasource and start designing your report.

 

Happy reporting.

Tags:

Work items

Comments

5/14/2009 12:03:46 PM #

Prabal

I couldn't get it to work. Below is the XML on the webpage.

<?xml version="1.0" encoding="utf-8" ?>
- <DocumentElement>
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  <wi />
  </DocumentElement>

I created the datasource, created new reported and pointed it to the new ds but in query builder / designer I don't see any columns.

Prabal United States |

5/14/2009 1:53:15 PM #

Prabal

Found the problem... If the value of the any of the items in the first row is string.empty it writes it as e.g: <Comments /> and that translates into missing field in the dataset. I replace the empty string with - to make it work.

Prabal United States |

5/14/2009 1:54:51 PM #

Prabal

!!!??? Problem ???!!!
Created the report, but after uploading it to TFS Report Manager I can't get it to work. I uploaded the datasource created as mentioned above but the url in the ds is pointing to my local webservice link. I thought I had asked if I need the webservice once I have the xml and you said I don't.
What gives?

Prabal United States |

9/17/2009 1:19:42 AM #

Peter

Worked great. You dont mention the need to add references to the project.

I would like to modify the WS so that it gets the WorkItems from the TFS DB directly, and not via a Query. Is that a good idea? any tips as to how to go about this? I am new to the Microsoft world.

Peter

Peter Sweden |

9/17/2009 10:03:57 PM #

Ewald Hofman

You could do that if that is what you want. You will have to rewrite the web service to pass your own arguments. There is a lot of good documentation about how to programmatically work with the TFS SDK, such as www.slideshare.net/.../getting-started-with-the-tfs-api and blogs.microsoft.co.il/.../default.aspx

Ewald Hofman Netherlands |

5/26/2010 8:11:26 AM #

Anthony

I've created this using VS2010, and I can't get it to work. When I hit Ctrl-F5 I get the following error in IE.

Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.
Parser Error Message: Could not create type 'ReportingService.Service1'.

Any ideas?


Anthony

Anthony Canada |

5/26/2010 10:57:16 PM #

Ewald Hofman

Right click on the WIQuery.asmx file and choose the "view markup" from the context menu.

Then change the Service1 to WIQuery

Ewald Hofman Netherlands |

10/14/2010 1:43:12 PM #

kamagra

I've created this using VS2010, and I can't get it to work. When I hit Ctrl-F5 I get the following error in IE.

Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.
Parser Error Message: Could not create type 'ReportingService.Service1'

kamagra United States |

Comments are closed

Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen


ClusterMap

Widget Statistics not found.

There is an error in XML document (0, 0).X

Recent comments

Comment RSS