Creating Reports in Microsoft Excel by Using Work Item Queries

You can generate several reports in Microsoft Excel that show current status and historical data based on the filter criteria that you specify in a flat-list work item query. This is useful to show the distribution of work items according to selected criteria or to view trends for the past several weeks. In addition, it is an effective way for you to quickly generate PivotTable and PivotChart reports that you can customize to support other report views.  

Note

Filters that specify the Contains, Contains Words, Does Not Contain, or Does Not Contain Words operators will not be added to the PivotTable. Excel does not support these operators.

When you create an Excel report from a query, you can choose which reports to generate based on the variables that are used to filter the query and the criteria that you select. By using these methods, you can generate the following types of reports:

• Current reports: Pie charts that show the count of work items according to the filter criteria that are specified in the work item query.

• Trend reports: Line charts that show the distribution of work items over the past six weeks according to the filter criteria that are specified in the work item query. After the reports are generated, you can easily change the date range.

Each report includes several worksheets, and each worksheet shows a PivotTable report and a PivotChart report that derives data from the SQL Server Analysis Services cube.

In this topic

• Generating an Excel Report From a Work Item Query Using Team Explorer

• Generating Query-based Reports Using Excel

Required Permissions

To create or modify an Excel report that connects to the cube, you must be a member of the TfsWarehouseDataReader security role in Analysis Services. For more information, see Grant Access to the Databases of the Data Warehouse for Visual Studio ALM.

To generate a blank PivotTable report, you must also belong to a group that has been assigned Contributor permissions in SharePoint Products for the team project. For more information, see Managing Permissions.

Generating an Excel Report from a Work Item Query by Using Team Explorer

To generate an Excel report from a work item query by using Team Explorer

1. In Team Explorer, create or open a flat-list work item query that contains the work items that you want to include in the report.

2. Specify filter criteria whose data that you want to include in the report.

   For more information, see Working with a Flat List (Default) Query.

   Note

   You cannot create a report from a direct links or tree view query.

3. In the query results list, click Open in Microsoft Office, and then click Create Report in Microsoft Excel.

   Office Excel opens and displays the New Work Item Report dialog box.

   

4. Expand Current reports and Trend reports and any nodes that you find under each of these nodes, and then click the check boxes of the reports that you want to generate.

   

5. Click Finish.

   The set of reports are generated. Depending on the number of reports that you select to create and the amount of data that is contained in the reports, this step may take several minutes to be completed.

   The first worksheet provides an overview of the reports that were generated together with hyperlinks to each report.

6. Click each worksheet tab to review each report.

   For information about how to customize the report, see How to: Edit Reports in Microsoft Excel.

7. (Optional) Save the Excel workbook.

8. (Optional) Upload the Excel workbook to the team project portal.

   For more information, see Upload and Refresh Excel Reports in the Team Project Portal for Visual Studio ALM.

Generating Query-based Reports by Using Excel

To generate query-based reports by using Excel

1. In Office Excel, create a workbook, and then click the Team tab.

2. In the Reports group, click New Report.

   The Connect to Team Project dialog box appears.

3. In the Select a Team Foundation Server list, click the name of the server that contains the team project from which you want to retrieve work items.

   If that server does not appear in the list, follow these steps:

   1. Click Servers, and then click Add.

   2. In the Team Foundation Server name box, type the name of the server to which you want to connect.

   3. If Team Foundation Server uses a port number other than the default (8080) to communicate with client programs, type the appropriate port number in the Port number box, and then click OK.

   4. Click Close.

4. Under Team Project Collections, click the name of the collection that hosts the team project.

5. In the Team Projects list, click the name of the team project that contains the work items that you want, and then click OK.

   The worksheet or project plan is now connected to a team project on the server that you specified.

6. In the New Work Item Report dialog box, select a flat-list work item query to use to generate the reports.

   Note

   You cannot create a report from a direct links or tree view query.

7. Expand Current reports and Trend reports and any nodes that you find under each of these nodes, and then select the check boxes of the reports that you want to generate.

8. Click Finish.

   The set of reports are generated. Depending on the data and number of reports that you select to generate, this step may take several minutes to be completed.

   The first worksheet provides an overview of the reports that were generated together with hyperlinks to each report.

9. Click each worksheet tab to review each report.

   For information about how to customize the report, see How to: Edit Reports in Microsoft Excel.

10. (Optional) Save the Excel file.

11. (Optional) Upload the Excel file to the team project portal.

    For more information, see Upload and Refresh Excel Reports in the Team Project Portal for Visual Studio ALM.

See Also

Tasks

Working with a Flat List (Default) Query

Concepts

Create and Manage Excel Reports for Visual Studio ALM