Export (0) Print
Expand All

Create Excel reports from a work item query

One of the quickest ways to generate a custom report in Excel is to start with a flat list query. You can generate both status and trend charts. Also, once the reports are built, you can manipulate the data further by adding or filtering fields using the PivotTable.

Here’s an example of a status report generated from a flat-list query.

Excel State pie chart report

Requirements

  • Your deployment needs to be integrated with reporting services. If your on-premises TFS deployment doesn’t have reporting services, you can add that functionality by following the steps provided here: Add reports to a team project.

  • You must be a member of the TfsWarehouseDataReader security roles. To get added, see Grant permissions to view or create reports in TFS.

  • A version of Excel that is compatible with Office 2007, Office 2010, or Office 2013. If you don’t have Excel, install it now.

  • Either Visual Studio or Team Explorer, which you can install from this download site. You can install Team Explorer for free.

    You need to install Team Explorer to get the Team Foundation add-in for Excel.

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

    Choose the fields you want to base reports on and include them in the filter criteria or as a column option. For non-reportable fields, see Q: Which fields are non-reportable?

  2. Create a report in Excel From the query results view.

    Create Report in Microsoft Excel
  3. Select the check boxes of the reports that you want to generate.

    Expanded nodes, New Work Item Report dialog box

    Wait until Excel finishes generating the reports. This step might take several minutes, depending on the number of reports and quantity of data.

    Each worksheet displays a report. The first worksheet provides hyperlinks to each report. Pie charts display status reports and area graphs display trend charts.

  4. To view a report, choose a tab, for example, choose the State tab to view the distribution of work items by State.

    You can change the chart type and filters. For more information, see Use PivotTables and other business intelligence tools to analyze your data.

  1. Open an Office Excel workbook and choose New Report.

    New Report (Team menu selection)
  2. Connect to the team project and choose the query.

    If the server you need isn’t listed, add it now.

  3. Choose the reports to generate (steps 3 and 4 from the previous procedure).

A: Even though you can include non-reportable fields in your query field criteria or as a column option, they won’t be used to generate a report.

  • Description, History, and other HTML data-type fields. These fields won’t be added to the PivotTable or used to generate a report. Excel does not support generating reports on these fields.

  • Fields with filter criteria 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.

A: Create the query in Team Web Access, and then follow the second procedure.

A: You can’t create Excel reports; however, you can create query-based charts.

A: At any time, you can choose Refresh on the Data tab to update the data for the PivotTables in your workbook. To learn more, see Update (refresh) data in a PivotTable.

Show:
© 2014 Microsoft