TFS Reports using Excel
| This article is in a draft stage.|
This article is designed to do a walk-through of creating a TFS report in Excel. For a video explanation and demo of this same material and other related TFS Reporting information, see the article on TFS Test Tools.
There are two ways to get started using Excel for getting TFS Reports. One is in Visual Studio 2010 and the other is actually starting in Excel.
Creating Excel Reports using Visual Studio 2010
The first way, starting in Visual Studio 2010, uses Team Explorer. To open the Team Explorer window, click on the View tab, and then select Team Explorer. You can also open the Team Explorer window by pressing the keys Ctrl + W and then pressing M. When Team Explorer opens it will have a pane on the right-hand side of the display (see Figure 1).
|Open Team Explorer in Visual Studio 2010 (Figure 1).|
In the example above, the project that is displayed is the MSStack project. If your project is not listed when you open Team Explorer, you will need to click on the Connect to Team Project icon in the upper left-hand corner of the Team Explorer pane (see Figure 2).
|Click on the "Connect to Team Project" icon (Figure 2).|
When the Connect to Team Project icon is clicked, it will display a dialog similar to the one displayed in Figure 3. Only projects that you have rights to will be displayed.
|Connect to a Team Project (Figure 3).|
If no server is selected, then no projects will be displayed. To select a server, click on the Servers... button in the upper right-hand corner. In the Add/Remove Team Foundation Server dialog, click on the Add... button if no servers are displayed in the list (see Figure 4).
|Add a TFS Server (Figure 4).|
In the Add Team Foundation Server dialog that shows up, enter the ICS TFS Server that contains all Church projects in the Name or URL of Team Foundation Server: field, which is
icstfs.ldschurch.org (see Figure 5). Then click on the OK button.
|Type in the ICS TFS Server name (Figure 5).|
That will display the Add/Remove Team Foundation Server dialog again and the ICS TFS Server should be highlighted. Click the Close button to set this as the default Team Foundation Server that will be used from now on. This will return focus to the Connect to Team Project dialog. The default Project Collection in the left pane will be
upgradeFromICSTFS2008 and all projects in this collection that you have rights to will be displayed in the right pane. In the list of projects, select the check boxes of the projects to which you would like to connect and be displayed in Team Explorer. To close the dialog and connect to the selected projects, click on the Connect button.
In Team Explorer, expand the project for which you want to generate Excel reports. This will display several containers below it including a Work Items area. Expand the Work Items area to see two categories under which queries exist (see Figure 6).
|Show the Query Categories (Figure 6).|
Expand the area that contains the query you wish to make into a report. If you do not see the exact query that you want, you can right click on either category and select New Query. For more information, see the blog about creating and saving queries.
To generate reports in Excel at this point is as simple as right clicking on any query and selecting the option to Create Report in Microsoft Excel. Excel will open and prompt for the queries you want to turn into charts. After clicking OK, several sheets will be created with the initial sheet being a Table of Contents (see Figure 7). Any of the charts can be customized.
|Excel Work Item Chart (Figure 7).|
If the Pivot Table Field List is not visible on the right-hand side of the screen, click in the table or on the chart in the left-hand side of the view.
You may have noticed in Team Explorer that under the project there is a category called Reports. This is for generating Reports in Visual Studio 2010. All of these reports can be added to your Dashboard or re-created in Excel. If there are no reports under this category, send an e-mail to TFS@ldschurch.org to make a request for these reports to be generated for your project.
Create TFS Reports starting in Excel
The second way to generate Excel Reports is to start in Excel and connect to the TFS Analysis Server. This is done by clicking on the Data tab in Excel and then clicking on the From Other Sources icon (see Figure 8).
|Other Data Sources in Excel (Figure 8).|
In the From Other Sources drop down menu, select the From Analysis Services option (see Figure 9).
|Choose "From Analysis Services" (Figure 9).|
The Data Connection Wizard will start to walk you through the process to establish a connection to the TFS Analysis Service. However, this wizard is only used the first time to make a connection to the Analysis Server. Once this connection is created then in the future you will click on the Existing Connection icon instead of the From Other Sources icon.
On the initial Data Connection Wizard dialog,type in the name of the TFS 2010 Analysis Server's name which is
chqpvuw1517\AS02 (see Figure 10). Click on the Next button.
|Connect to Database Server (Figure 10).|
On the next Data Connection Wizard dialog, select the Tfs_Analysis database from the drop down list under the Select the database that contains the data you want: field. Select the check box that says Connect to a specific cube or table: if it is not already selected. Ensure that the Team System cube is displayed and then click Next (see Figure 11).
|Team System database (Figure 11).|
On the next Data Connection Wizard dialog, click on the Authentication Settings... button (see Figure 12).
|Change the Authentication Settings (Figure 12).|
On the Excel Services Authentication Settings dialog, select the SSS radio button and type in the SSS ID of TFS (see Figure 13). This will allow the chart or table to auto-update with the latest data each time it is accessed. Click OK to return to the Data Connection Wizard dialog.
|Set the Secure Store Service ID (Figure 13).|
Click the Finish button to complete the Data Connection Wizard. An Import Data dialog will then appear. Click OK (see Figure 14) to accept the default settings.
|Import Data dialog (Figure 14).|
The PivotTable Field List pane will appear on the right-hand side of the spreadsheet (see Figure 15).
|PivotTable Field List (Figure 15).|
Any field in the upper part of the PivotTable Field List can be dragged down to one of the four fields in the lower part. These fields will show up in the Spreadsheet. Construct your table to contain the data that you wish. If you want to only see the data from your project, then the first filter you will want to add is Project Name. To do this, you need to scroll to the Team Project grouping and expand the More fields item. Then drag the Project Node Name field into the Report Filter box. In the spreadsheet a cell will contain Project Node Name and next to it will be the value that you want to filter on. By default this value will be All, but you can click the drop down dialog to select one or more specific projects (see Figure 16).
|PivotTable showing selected project (Figure 16).|
Any report you create at this point will only contain data from the selected projects. Next, drag the rows, columns, and values that you want in your report to the appropriate box. For example, if the desired table would show the Grand Total of the Bugs in the specified project and a Count of the Bugs in the various states, then here are the steps to do that:
- In the PivotTable Field List, scroll to the Work Item group and drag the Work Item.System_WorkItemType field item to the Row Labels box.
- Drag the Work Item.System_State field item to the Column Labels box.
- Under the ∑ Work Item group, drag the Work Item Count field item to the Values box.
- In the spreadsheet cell containing Row Labels (in Figure 17 below, it would be cell A4), click on the down arrow to display the filtering dialog. Deselect Select All and click on your Bug check box. Click OK.
- If you don't care about Closed bugs, then expand the Column Labels dialog and deselect the Closed state check box.
To see an example of what this table might look like, see Figure 17 below.
|Bug Category Count table (Figure 17).|
Once the table has the desired filters, rows, columns, and values, add a graph or chart by clicking on the Insert tab and selecting one of the many types of graphs available in Excel. A bar chart showing the data in the generated table might look like the one in Figure 18.
|Bug Category Count table with Bar Chart (Figure 18).|
You can save this chart (File > Save) and then e-mail it to your team, your manager, or whoever would like to see your project status.
There are times when you want to create a report that contains other data or has columns that don't exist exactly the way you want them in the cube. You can create your own columns to add to the Pivot Table that are Calculations based on the Pivot Table data from the cube. For example, if you are using a Date field in a Row or Column then you should know that it is not really a date, but a string. You may want to do some calculations based on the field as if it was a date. So, you will need a new column that has a formula to convert the string into a date. If you wanted to take the string in cell A5 and convert it to a lot of additional Excel formulas and functionality that can be found on the Internet to include in your Excel Pivot Table or Pivot Chart.
The next section will explain how to take this Saved Excel Report and post it to your Project's SharePoint Dashboard.
Once the Excel report is created and saved to your local machine, it is likely that you will want to post it somewhere so that others can access it and see the state of your project. This is done by going to your Team Project's Dashboard and adding the Excel report. If you do not know the URL to your Dashboard then the easiest way to get there is from within Visual Studio. Bring up Team Explorer and right click on your project. In the right click menu select Show Project Portal... (see Figure 19).
If you do not have a SharePoint Dashboard for your project, send an e-mail to TFS@ldschurch.org requesting that a Dashboard be created for your project.
|Show the Team Project Dashboard (Figure 19).|
When the Dashboard is displayed, click on the option Excel Reports in the left pane to add the Excel Report just created to the collection of Reports on the Dashboard site (see Figure 20).
|Team Project SharePoint Dashboard (Figure 20).|
At the bottom of the list of Excel Reports that are already available, click on the Add document link (see Figure 21).
|Adding Excel Report to Dashboard (Figure 21).|
Browse for the Excel Report that you want to upload and click OK. The Report will be added to the collection of Excel Reports, but will not be displayed on any of the Dashboard pages. It will show up in the list of Excel Reports and if anyone clicks on it they will see the chart, graph or table in the Excel Report. Another way to do this is in Excel by clicking on the Publish option and choosing Excel Services.
Even though other people who visit your Dashboard can view your new report by clicking on the name of the report, it may be more desirable to add Excel Reports to one of the Dashboard pages. This can be done by going to the Dashboard page where the Excel Report will be displayed and click on Site Actions in the upper left-hand corner of your project's SharePoint Portal, which is your Dashboard website. For example, to add an Excel Report to the MSStack Project Dashboard, go to https://teams.ldschurch.org/tfs/MSStack/Dashboards/Bugs.aspx and click on Site Actions (see Figure 22).
|MSStack Project Bug Dashboard (Figure 22).|
In the drop down menu under Site Actions, click on Edit Page. This will display all of the existing Web Parts in each of the Rows or Zones on the page. Click on the Add Web Part link in the Row that you want to add Excel Report (see Figure 23).
|Editing a SharePoint Dashboard page (Figure 23).|
This shows the Categories and Web Parts available to be added. Click on the Excel Category in the left pane. Then highlight the New Excel Report Web Part and click the Add button (see Figure 24).
|Adding a New Excel Report Web Part (Figure 24).|
This adds an Excel Report Web Part to the page, but you will still need to choose which Excel Report will be placed there. Click on the link Click here to open the tool pane (see Figure 25).
|Select an Excel Report (Figure 25).|
The tool pane will open in the far right-hand side of the web page so you may need to scroll over to see it. Click the … button next to the Workbook: field to browse through the Excel Reports and find the Report that you want to display in the Web Part (see Figure 26). It is likely that you want to clear the Named Item: field and select the check box that says Autogenerate Web Part Title, but that is up to your own personal preference.
|New Excel Report tool pane (Figure 26).|
Click Apply to apply the changes and see what it will look like. If there are other changes you wish to make, then make the changes and click the OK button to close the tool pane. The final Dashboard will look something like Figure 27.
|New Excel Report posted on the SharePoint Dashboard (Figure 27).|
For more information about using Pivot Tables in Excel see the following links:
- Update Pivot Table from Text File
- How to Make a Pivot Table and Chart in Excel
- Excel Pivot Tables Tutorial: What is a Pivot Table and How to Make one
For a video explanation and a demo of the content in this document see the following link:
For other links to helpful information about TFS Reporting, see the links under the Reporting sections of this article: