none
Count the number of rows from the Query Result in TFS

    Question

  • Hi,

    TFS queries can be exported to an excel sheet. I need to make use of this feature to return a metrics of the total tasks completed in a week. For that I need to be able to return the count of the rows returned by my query. This query returns the complete task for the week. I am not being able to figure out a way to do that. Is there a way to count the number of rows from the query result.

    Friday, February 18, 2011 3:47 PM

Answers

  • Hi Prachi,

    It sounds like you want to automate a way to get the total number of results in a query.  You can do this in two ways:

    1. In the Excel integration to TFS, you could create a macro that gets the number of rows in your ListObject.  Below is an example if the TFS list object is the only list object in your sheet

    Sub GetRows()
        Dim ws As Worksheet
        Set ws = Application.Worksheets.Item(1)
        Dim lo As ListObject
        Set lo = ws.ListObjects.Item(1)
        MsgBox lo.ListRows.Count
    End Sub

        

    2. You can use the TFS object model to run a query.  This query will return a WorkItemCollection and you can get a count on that.

     

     

    string collectionUrl = "your TPC URL";
    string queryString = "your WIQL query";
    WorkItemStore store = new WorkItemStore(collectionUrl);
    WorkItemCollection wic = store.Query(queryString);
    return wic.Count;

     

    Thanks,

    Erin

    Friday, February 18, 2011 11:01 PM
    Owner
  • Hi Prachi,

     

    Thanks for your post.

     

    Do you mean that you want to know how many items be found after perform a specific Query? If so, please refer to the following screenshot:


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 21, 2011 6:08 AM
    Moderator

All replies

  • Hi Prachi,

    It sounds like you want to automate a way to get the total number of results in a query.  You can do this in two ways:

    1. In the Excel integration to TFS, you could create a macro that gets the number of rows in your ListObject.  Below is an example if the TFS list object is the only list object in your sheet

    Sub GetRows()
        Dim ws As Worksheet
        Set ws = Application.Worksheets.Item(1)
        Dim lo As ListObject
        Set lo = ws.ListObjects.Item(1)
        MsgBox lo.ListRows.Count
    End Sub

        

    2. You can use the TFS object model to run a query.  This query will return a WorkItemCollection and you can get a count on that.

     

     

    string collectionUrl = "your TPC URL";
    string queryString = "your WIQL query";
    WorkItemStore store = new WorkItemStore(collectionUrl);
    WorkItemCollection wic = store.Query(queryString);
    return wic.Count;

     

    Thanks,

    Erin

    Friday, February 18, 2011 11:01 PM
    Owner
  • Hi Prachi,

     

    Thanks for your post.

     

    Do you mean that you want to know how many items be found after perform a specific Query? If so, please refer to the following screenshot:


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 21, 2011 6:08 AM
    Moderator
  • I didnt see the query reult count..but in any case if i may wish to print the actual count of the query result i can use the macro suggested by Erin. Thank you guys..
    Tuesday, February 22, 2011 1:30 PM