Create an Aggregate Report using Report Designer and the Analysis Services Cube

You can track your team's progress more easily by creating reports that aggregate data from Visual Studio Application Lifecycle Management (ALM) 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 Report Designer in SQL Server and the SQL Server Analysis Services cube for Visual Studio ALM.

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 Visual Studio ALM before, see Create, Customize, and Manage Reports for Visual Studio ALM. If you have not used Report Designer before, see the following page on the Microsoft Web site: Designing and Implementing Reports Using Report Designer. For information about how to create reports that include line-item details (such as titles of work items), see Create a Detailed Report using Report Designer.

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 the following page on the Microsoft Web site: How to obtain the latest service pack for SQL Server 2008.

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 Grant permissions to view or create reports in TFS.

• The Team Foundation Content Manager role in SQL Server Reporting Services. For more information, see Set Administrator Permissions for Team Foundation Server. 

To create a report

1. In Visual Studio, create or open a Report Server project. For more information, see Create a Report Server Project for Visual Studio ALM.

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

   The Add New Item dialog box appears.

3. Choose Report Wizard, and then choose Add.

   The Report Wizard opens to the Select Data Source page.

4. Choose the Tfs2010OlapReportDS shared data source, and then choose Next.

   The wizard advances to the Design the Query page.

   Note

   The data source that you specified connects to the analysis services database for Visual Studio ALM. Even though you might have installed or upgraded to TFS 2013, the database name of TFS2010ReportDS is used. This is the same name that was assigned to the data sources with the installation or upgrade to TFS 2010. For more information, see Choose the Source of Data and Authoring Tool for Your Reports for Visual Studio ALM. If your project does not have this data source, create it. For more information, see Create a Report Server Project for Visual Studio ALM.

5. Choose Query Builder.

   The Query Build dialog box appears.

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

1. Choose the Team System cube, as the following illustration shows.

   

   Note

   If your data warehouse for Visual Studio ALM 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 and Measure Groups Provided in the Analysis Services Cube for Team System.

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.

   

   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 Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System.

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

   

   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 choose OK.

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

To design the initial report layout

1. Choose Next.

   The wizard advances to the Report Type page.

2. Choose Matrix, and then choose Next.

   The wizard advances to the Design the Matrix page.

3. Choose Cumulative_Count, and then choose Details.

4. Choose State, and then choose Columns.

5. Ch oosePerson, choose Rows, and then choose Next.

   The wizard advances to the Choose the Matrix Style page.

6. Choose any style, and then choose Next.

   The wizard advances to the Completing the Report page.

7. Type a name for the report, choose Preview Report, and then choose 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, choose 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. Open the context menu for the chart, and then choose Properties.

   The Chart Properties dialog box appears.

5. Choose the Data tab, and then choose the Tfs2010OlapReportDS dataset name.

6. Under Values, choose the Add button.

   The Edit Chart Value dialog box appears.

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

8. Under Category groups, choose 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, choose =Fields!Person.Value, and then choose OK.

10. Under Series groups, choose the Add button.

    The Grouping and Sorting Properties dialog box appears.

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

12. Choose 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, choose the report.

2. On the Build menu, choose Deploy ReportName.

   Important

   To successfully deploy the report, your project settings must be set to appropriate values. For more information, see Create a Report Server Project for Visual Studio ALM.

See Also

Tasks

Create a Report Server Project for Visual Studio ALM

Concepts

Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System

Other Resources

Create and Manage Reporting Services Reports for Visual Studio ALM