Create Excel reports from a work item query

  • Artikel

Go here to open the Visual Studio 2015 version of this topic.

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.

You can generate these reports only when you work with an on-premises TFS that has been configured with reporting services.

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 application-tier server hasn’t been configured to support 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 the Team Explorer plug-in for Visual Studio, which you can install from this download site. Team Explorer is free and requires a Windows OS.

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

Create an Excel report from a flat-list query

Use this procedure when you work from the Team Explorer plug-in for Visual Studio.

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

Create a query-based report by using Excel

Use this procedure when you work from Team Web Access or the Team Explorer plug-in for Visual Studio.

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

    New Report (Team menu selection)

    If you don’t see the Team menu, you'll need to install Team Explorer to get the Team Foundation add-in to Excel. See Requirements listed earlier in this topic.

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

Q & A

Q: Which fields are non-reportable?

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.

Q: Can I create reports if I’m working in Visual Studio Online?

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

Q: How do I refresh the report to show the most recent data?

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.

Q: Can I upload these reports to a project portal?

A: Yes. See Manage documents and document libraries.

See Also

Concepts

Choose the Team Foundation client to support your tasks