Creating Custom Team System Reports | Professional Team Foundation Server

The default project types in Team System provide a nice, consistent view into the development lifecycle. More often than not, you'll need other types of reports to supplement your project. For example, you may want visibility.

Important

SQL Server 2005 Enterprise provides better field names for your reports than other versions of SQL Server. For example, a field in SQL Server 2005 Standard may be represented as "WorkItem.WorkItem." In SQL Server 2005 Enterprise, it is represented as simply "WorkItem."

Mining Project Data Using an Excel Pivot Table Report

You can view your project data using an Excel pivot table. One of the key advantages of using a pivot table is simplicity; Microsoft Excel mines the data warehouse directly and has the built-in capability to display rich graphs and charts. Here is how you can set up Excel to view your project data:

Open Microsoft Excel.
Select Data⇨PivotTable and PivotChart Report.
Select External Data Source and Pivot Table and then click Next.
Click the Get Data button.
Select the OLAP Cube tab, select New Data Source, and then click OK.

Write a custom name for your data source. (If you are unsure, write in something like TFSDATA.) To use pivot tables, you need to first download the OLEDB 9.0 drivers. If you want to install the drivers without having to install all the SQL client tools, refer to the package at www.microsoft.com/downloads/details.aspx?FamilyID=&displaylang=en.

Important

If the OLEDB drivers have been installed and you encounter an "Initialization of the data source failed" error, simply go to Start⇨Run, and type Regsvr32 "C:\ProgramFiles\Common Files\System\Ole DB\msolap90.dll".

Select the Microsoft OLE DB Provider for Analysis Services 9.0 and click Connect.
A new window entitled Multidimensional Connection 9.0 pops up.
Select Analysis Server; under Server, enter (local).

Enter your administrative credentials in the User ID and Password fields. (The wizard warns you if your credentials are invalid.) Theoretically, you should not need credentials, but you need to be a member of the TFSDataWarehouseReader role on the cube. You can set this up using the SQL Server Management Studio.

Important

If you enter incorrect credentials or credentials that don't have sufficient permissions to access the cube, you may receive an "error occurred in the transport layer" message. You can avoid the error message by entering valid user names and passwords.

Select the TFSWarehouse database and click Finish.

Select Team System, check Save My User ID and Password, and click Yes. You may get a warning that your user name and password will not be encrypted. The wizard will not allow you to move forward until you accept the warning. It's an important consideration if you are not in a secure environment for example, if you are working from a coffee house hotspot.

Important

If you need to create reports in that kind of environment, you should consider replicating your database locally. (Team Edition for Database Professionals will enable you to create a local instance of your Team Foundation Server data warehouse.) For more information, please refer to Chapter 8.

Click OK in the Create New Data Source dialog box; click OK once more.
Click Finish in the PivotTable dialog box.

At this point, you can select any fields you want to view in the pivot table. Select the items that interest you and drag them into the pivot table. For example, you may be interested in seeing ChangeSet information. The information will automagically appear in the Excel spreadsheet within the pivot table (as shown in Figure 16-11). You can then save the sheet or choose to manipulate it at will.

image from book
Figure 16-11

It is important to understand the role of the row and column axes. The row axis defines a collection of dimension members that represent row labels. The column axis represents column labels. Here is the layout of a typical pivot report: the project dimension is used as a filter, the date dimension is used on the row axis, and the measures from many measure groups are sliced up using the same dimension.

Creating Custom Reports with the Report Designer

To create an effective report, it is important to be able to distinguish between the different data sources and show the appropriate use of each. TfsReportDS is used in conjunction with the Team Foundation Server relational database. The relational database is perfect for getting lists and summary data, plus it contains the most recent data. An example of an appropriate use of the relational database is pulling the most recent work items from the data warehouse. Note that you may need to index the relational database in a separate table to get good performance for reports.

Important

You should download and install the latest SQL Server 2005 Service Pack on your data tier before attempting to create a custom report. Otherwise, you may experience some difficulties, such as crashes - Service Pack 1 includes a great number of bug fixes. Here is a direct link to SQL Server 2005 Service Pack 1 (SP1): microsoft.com/downloads/details.aspx?familyid=&displaylang=en.

TfsOlapReportDS is the data source for the Team Foundation Server OLAP cube. OLAP is perfect for obtaining multidimensional data and correlated data without incurring a performance hit (as opposed to having to create a complex spaghetti mess of joins to obtain the same results).

SQL Server Reporting Services reports are based on the .rdl file format (which stands for Report Definition Language). The reports can be built within Visual Studio 2005 and managed in Team Foundation Version Control (alongside all the other code assets). Your custom reports can also be deployed within a process template, to make them available every time you create a new Team Project.

Important

The effective use of Report Designer is not for the faint of heart. If your custom reports are primarily based on OLAP, you should learn how to use multidimensional expressions (MDX). Some of your queries will require Visual Basic expressions, and the manipulation of parameters. Before attempting your first Team System report, take the time to learn the basics and try changing existing reports. The best way to learn is through trial and error, and don't expect to be creating amazing reports overnight (unless you are SQL Server Reporting Services expert and you code MDX in your sleep).

Here are the steps to create a report in Report Designer that pulls from the Work Item History fact table, specifically bug assignments by priority.

First, create a new project by clicking File⇨New⇨Project.
Select Business Intelligence Projects.
Under templates, select Report Project. Add a custom name for your project and select OK.
In the Reports Project, right-click the Reports folder, and select Add New Report.
This triggers the Report Wizard. Click Next.
Next, you have to select a data source. Select Microsoft SQL Server Analysis Services (to leverage the OLAP cube). Click Next.
Select the New data source option. Under name, enter VSTSRC. Under type, make sure Microsoft SQL Server Analysis Services is selected. Under data source, enter the following: Data source=VSTSRC;initial catalog=TFSWarehouse. Click Next.
Click Query Builder. The Query Builder for Analysis Services appears.
In the top-left corner, you see a label that says AssemblyFact. Click the button next to it.
You see a list of fact tables to select. Select WorkItem_Fact table and click Next.
Now you can drag metadata from the Work Item History cube into the filter area or the measures level.
Expand the Measures node in the left menu. Drag RecordCount into the Measures area on the right.
Expand the Assigned To node. Drag Alias to the Measures area on the right of the screen.
Scroll down and expand Work Item Type. Further expand WorkItemType⇨WorkItemType⇨Bug, and then drag Bug into the Dimension window at the top-right of the screen.
Expand the State node. From there, further expand State⇨State⇨Active. Drag Active to the top right window on the screen
Drag the Priority node into the Measures area on the right of the screen
Click OK
Select the Matrix type of report. Click Next.
Select RecordCount to be in the Details area.
Select Alias for the Column level.
Select Priority for the Row level. Click Next.
Select a style for your report. If you are unsure what style, select Bold.
Enter a name for your report (Bug Assignments by Priority), and click Finish.

Using the Report Builder

We would be remiss if we didn't include the Report Builder in this chapter. Report Builder is a ClickOnce application that is integrated in SQL Server Reporting Services. It's designed for business users and developers who wish to create custom reports quickly and easily. You can launch it by clicking the Report Builder option on the Report Manager menu bar. (You can bring up the Report Manager by right-clicking on a project Reports folder in Team Explorer and selecting Show Report Site.)

Before you can access Team System data, you'll have to create a Semantic Model Definition Language (.SMDL) model. The SDML model can be created against both the relational warehouse and the OLAP cube. To get a usable model with the relational warehouse, you'll need to customize it considerably to get any useful data out of it. On the other hand, you can use the OLAP data source (TfsOlapReportDS), but keep in mind that creating reports against the model will have some impact from a performance and usability perspective. The rule of thumb is, if you want to create a custom report, use the Report Designer. In case you are curious, here is the process for creating an SDML model and setting up a Report Builder report:

Go to the Home page of SQL Reporting Services (the URL is http://<your application tier>/Reports/).
Click the TfsOlapReportDS data source link.
Click the Generate Model button. (It's at the bottom of the page.)
You are then be prompted for a name, description, and location. Name the model TfsOlapReportDS.
Once you are finished, you are ready to start building a report. Launch the Report Builder by clicking on the link from the front page of the Report Manager site. (You can access the Report Manager by right-clicking on your Team Project reports folder in Team Explorer)
Once the application launches, you can choose whether your report is a matrix, chart, or columnar table. Select the TfsOlapReportDS model and select your report type, then click OK.
A report template appears with an area where you can drag data fields onto it. To create a build report, click the Build entity and select the fields you want to appear on the report. You can also drag entities. (Entities are a logical collection of fields and other data types.) You can then save your report by clicking File⇨Save. Once you click Save, you'll have the option of placing your report in any Team Project you want.

You can mix and match the entities and fields to get unique views of your project data. For example, you can mix your build data with changeset data.