How to: Create an Aggregate Report for Team System using Report Designer

  • Article

You can track your team's progress more easily by creating reports that aggregate data from Team Foundation into charts and tables. For example, you can create a report that shows how many active work items are assigned to each person on the team. To create this type of report, you use SQL Server 2005's Report Designer and the analysis services database of the data warehouse for Team System.

After you create your first report, you might change it by experimenting with different measures, dimensions, and layouts. For example, you can change the chart from a simple column chart to a stacked-bar chart.

If you have not created reports for Team Foundation before, see Getting Started With Custom Reports for Team System. If you have not used Report Designer before, see Working with Report Designer on the Microsoft Web site. For information about how to create reports that include line-item details (such as titles of work items), see How to: Create a Detailed Report for Team System using Report Designer.

Prerequisites

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

Note

To install Business Intelligence Development Studio, run the Setup program for , and select the Client Components check box when you specify the components to install. To install the most recent service pack for , 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 Analysis Services database on the data-tier server of Team Foundation Server. For more information, see Securing Access Through Analysis Services.

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

To create a report

  1. In Visual Studio 2005, 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 TfsOlapReportDS shared data source, and then click Next.

    The wizard advances to the Design the Query page.

    Note

    The data source that you specified connects to the analysis services database 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 the Team System cube, as the following illustration shows.

    Query Builder - click the Team System cube

    Note

    If your data warehouse for Team System is using SQL Server Enterprise Edition, the list of cubes will include Team System and a set of perspectives. The perspectives provide a focused view of the data so that you do not have to scroll through the dimensions and measures in the whole Team System cube. For this procedure, you can use the Work Item History perspective if it is available. For more information, see Perspectives.

  2. Expand Measures, expand the Work Item History measure group, and then drag the Cumulative Count measure into the data area, as the following illustration shows.

    Query Builder - add Cumulative Count measure

    Note

    Cumulative Count shows how many work items are selected. Because you have not yet applied any filters, the number that appears is the total number of work items. For more information about work item measures, see Work Item History Perspective and Current Work Item Perspective.

  3. Expand the Assigned To dimension, and then drag the Person property into the data area, as the following illustration shows.

    Query Builder - add Person dimension

    The query now returns the number of work items that are assigned to each person.

    Note

    The Assigned To field generally contains Windows accounts. For each Windows account, the Person property contains the display name of that account, and the Alias property contains the alias.

  4. Expand the Work Item dimension, drag the State property into the data area, and then click OK.

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

To design the initial report layout

  1. Click Next.

    The wizard advances to the Report Type page.

  2. Click Matrix, and then click Next.

    The wizard advances to the Design the Matrix page.

  3. Click Cumulative_Count, and then click Details.

  4. Click State, and then click Columns.

  5. Click Person, click Rows, and then click Next.

    The wizard advances to the Choose the Matrix Style page.

  6. Click any style, and then click Next.

    The wizard advances to the Completing the Report page.

  7. 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 replace the table with a chart

  1. In the report document window, click the Layout tab.

    Note

    Report Designer uses three tabs, as the following table describes briefly.

    Tab Description

    Data

    Define the data sets that your report uses.

    Layout

    Design and arrange the visual elements of your report.

    Preview

    Run your report to see how it looks.

  2. Highlight the table, and then press Delete.

    Note

    To highlight the whole table, click anywhere in the table, and then click the upper-left corner of the table.

  3. From the Toolbox pane, drag a Chart element to the report's layout area, and then size the chart to meet your needs.

    Note

    By default, the Toolbox and Datasets panes are tabs on the left of the Visual Studio surface.

  4. Right-click the chart, and then click Properties.

    The Chart Properties dialog box appears.

  5. Click the Data tab, and then click the TfsOlapReportDS dataset name.

  6. Under Values, click the Add button.

    The Edit Chart Value dialog box appears.

  7. On the Value tab, in the Value list, click =Sum(Fields!Cumulative_Count.Value), and then click OK.

  8. Under Category groups, click the Add button.

    The Grouping and Sorting Properties dialog box appears.

    Note

    The category groups appear on the x-axis of the chart.

  9. In the first row of the Expression table, click =Fields!Person.Value, and then click OK.

  10. Under Series groups, click the Add button.

    The Grouping and Sorting Properties dialog box appears.

  11. In the first row of the Expression table, click =Fields!State.Value, click OK, and then click OK.

  12. Click the Preview tab to display a chart that shows how many work items are assigned to each team member, organized by the work item state.

To deploy the report

  1. In Solution Explorer, click the report.

  2. On the Build menu, click Deploy ReportName.

    Important

    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 an Aggregate Report for Team System using Report Designer

Concepts

Shared Dimensions

Other Resources

Getting Started With Custom Reports for Team System
OLAP Design
How to: Create a Report Server Project for Team System