Exercise 4: Creating a Complex Query for Advanced Reporting

In this exercise, you will learn how to create a complex query in Microsoft Visual Studio 2010 to provide more specific data than the default queries can provide.

1. Open Microsoft Visual Studio 2010 by going to Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010 if it is not already open.

2. In Team Explorer, expand Tailspin Toys to the Queries node (Tailspin Toys | Work Items | My Queries).

   Figure 1

   Location of My Queries node

3. Right-click on My Queries and select New Query from the context menu.

   Figure 2

   Creating a new query

4. By default, there are already three query clauses (rows) provided when creating a new query. Change the query clauses so that the query looks for all test cases that are not closed as follows:
   • First clause: Team Project = @Project
   • Second clause: And Work Item Type = Test Case

   • Third clause: And State <> Closed

     Figure 3

     New query checks for test cases that are not closed

5. Select the dropdown box next to Type of Query and select Work Items and Direct Links. This will add an additional sub-query and show a secondary grid below the first.

   Figure 4

   Changing the Query Type in the New Query Editor

6. Try not to confuse the secondary query grid with the previous query. The secondary query grid is used to find linked work items from results returned from the first query.

   Figure 5

   Secondary query was added to be used on work items with links

7. Let’s modify the secondary query to find all closed bugs that are linked to the test cases returned from the primary query.
8. Change the first query clause to be Work Item Type = Bug.

9. Add a second query clause: And State = Resolved.

   Figure 6

   Modifying secondary query to find linked bugs that are resolved

10. Now that the query has been defined, select the Run button to preview the data set that is returned.

    Figure 7

    Location of Run query button

    Figure 8

    Example results set from new query

11. Note that the query returns four test cases, only one of which has a linked bug. Since the Top level work items setting is set to Return all top level work items, all of the test cases are returned – even those that do not have linked bugs.

12. Select the Save Query button.

    Figure 9

    Location of Save Query button

13. In the Save Query As dialog box, type Test Cases With Resolved Bugs for the Name field. Finally select the Save button to save the query and return to the Query Editor window.

    Figure 10

    Naming and saving new work item query

14. Change the Top level work items setting to the Only return items that have the specified links option as shown in the figure below and then select the Run button to preview the results once again.

    Figure 11

    Modifying query to only show work items with specified links

15. The result, which you can see in the figure below, is that only one item is returned since it is the only test case with a linked bug.

    Figure 12

    Result set from modified query

Next Step

Exercise 5: Creating a Report in Microsoft Excel