Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Create Excel reports from a work item query

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 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.

Requirements

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 permissions to view or create reports in TFS.

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.

  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 for a flat-list query whose data that you want to include in the report.

    Tip Tip

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

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

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

    Initial 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 choose the check boxes of the reports that you want to generate.

    Expanded New Work Item Report dialog box
  5. Choose 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. Choose 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.

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

  2. In the Reports group, choose New Report.

    The Connect to Team Project dialog box appears.

  3. In the Select a Team Foundation Server list, choose 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. Choose Servers, and then choose 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 choose OK.

    4. Choose Close.

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

  5. In the Team Projects list, choose the name of the team project that contains the work items that you want, and then choose 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.

    Tip Tip

    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. Choose 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. Choose 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.

Show:
© 2014 Microsoft. All rights reserved.