How to: Create a Detailed Report for Team System using Report Designer

You can track your team's progress more easily by creating reports that contain detailed information from Visual Studio Team System. For example, you can create a report that includes details such as titles of work items. To create this type of report, you can use SQL Server's Report Designer and the relational database of the data warehouse for Visual Studio Team System.

After you create your first report, you might change it by experimenting with different data and layouts. For example, you could group the table by the Assigned To field.

If you have not created reports for Team System before, see Getting Started With Custom Reports for Team System. For more information about how to use Report Designer, see Working with Report Designer on the Microsoft Web site. To create reports that primarily show aggregate information, see How to: Create an Aggregate Report for Team System using Report Designer and the Analysis Services Database.

Prerequisites

To perform this procedure, you must have Visual Studio and SQL Server Business Intelligence Development Studio installed on the same computer.

Note

To install Business Intelligence Development Studio, run the Setup program for SQL Server, and select the Client Components check box when you specify the components to install. To install the most recent service pack for SQL Server, see "How to obtain the latest service pack for SQL Server 2005" on the Microsoft Web site.

Required Permissions

To perform this procedure, you must be a member of the following roles:

• The TfsWarehouseDataReaders security role in the relational database from the data warehouse for Team System on the data-tier server of Team Foundation Server. For more information, see How to: Grant Access to the Databases of the Data Warehouse for Team System.

• The Publisher role in SQL Server Reporting Services on the application-tier server of Team Foundation Server. For more information, see "Securing Reporting Services" on the Microsoft Web site.

To create a report

1. In Visual Studio, create or open a Report Server project. For more information, see How to: Create a Report Server Project for Team System.

2. On the Project menu, click Add New Item.

   The Add New Item dialog box appears.

3. Click Report Wizard, and then click Add.

   The Report Wizard opens to the Select Data Source page.

4. Click the TFSReportDS shared data source, and then click Next.

   The wizard advances to the Design the Query page.

   Note

   The data source that you specify connects to the relational database from the data warehouse for Team System. For more information, see Choosing the Source of Data in a Report for Team System. If your project does not have this data source, create it. For more information, see How to: Create a Report Server Project for Team System.

5. Click Query Builder.

   The Query Build dialog box appears.

To create the query that will retrieve the data for the report

1. Click Generic Query Designer on the query builder toolbar to enable the query designer.

2. Click Add Table on the query builder toolbar.

   The Add Table dialog box appears.

3. Click the Current Work Item table, and then click Add.

   This fact table contains the current state of each work item. For more information about how fact and dimension tables relate to each other, see Fact Tables and Dimension Tables.

4. Click the Work Item table, and then click Add.

   This table contains the Work Item dimension. For more information, see Work Items Schema.

5. Click the Person table, click Add, and then click Close.

   This table contains the Person dimension. The fact table for current work items has foreign keys to this table for the Assigned To, Changed By, and Created By fields. For more information, see Work Items Schema.

6. In the Work Item table, select the check boxes for System_Title and System_State.

7. In the Person table, select the check box for Person.

8. In the query pane, delete the clauses that use Changed By and Created By so that you now have the following query.

   SELECT Person.Person, [Work Item].System_State
   FROM   [Current Work Item] INNER JOIN
          [Work Item] ON [Current Work Item].[Work Item] =
          [Work Item].__ID INNER JOIN
          Person ON [Current Work Item].[Assigned To] = Person.__ID
   

9. Click Run on the query builder toolbar to verify that the query works, and then click OK.

   The Query Builder is closed, and the Design the Query page of the Report Wizard reappears.

To design the report layout

1. Click Next.

   The wizard advances to the Report Type page.

2. Click Tabular, and then click Next.

   The wizard advances to the Design the Table page.

3. Click System_Title, and then click Details.

4. Click Person, and then click Details.

5. Click System_State, click Group, and then click Next.

   The wizard advances to the Choose the Table Layout page.

6. Click the layout options that you prefer, and then click Next.

   The wizard advances to the Choose the Table Style page.

7. Click any style, and then click Next.

   The wizard advances to the Completing the Report page.

8. Type a name for the report, click Preview Report, and then click Finish to create the report.

   The wizard closes, and the report document window appears with the Preview tab active.

To deploy the report

1. In Solution Explorer, click the report.

2. On the Build menu, click Deploy ReportName.

   Important Note:
   To successfully deploy the report, your project settings must be set to appropriate values. For more information, see How to: Create a Report Server Project for Team System.

See Also

Tasks

How to: Create a Report Server Project for Team System

How to: Create an Aggregate Report for Team System using Report Designer and the Analysis Services Database

Concepts

Fact Tables

Dimension Tables

Work Items Schema

Fact Table Relationships

Getting Started With Custom Reports for Team System

Other Resources

Relational Schemas