How to: Report Daily Active Work Items by Person
Team Foundation project managers typically use Microsoft Excel to build reports from the data store in the Team Foundation data warehouse. In particular, Excel PivotTables are useful for displaying static work item data. However, there are times when you might want to filter and display a subset of this work item data that contains dynamic values, such as dates. For example, you might want to view only the number of work items resolved in the past month, or the current number of Active work items logged as of today. This subset of data is a cube.
PivotTables do not let you to filter on a dynamic value, so in the case of current number of Active work items, you must manually update the date information each day. Alternatively, you can use Cube Functions, which are available in Excel 2007, to report this data. Even though Cube Functions are more difficult to create, the ability to filter work item data dynamically is a big benefit.
This article explores how to use Cube functions to filter work item data to display active work items as of today by person.
To perform these procedures, you must be a member of the Microsoft Analysis Services TfsWarehouseDataReader security role. For more information, see Securing Access Through Analysis Services.
This article is based on the Teams WIT Tools blog entry As of Today TFS Cube Report in Excel.
Creating the Starter PivotTable
The easiest way to get started with Cube functions is to create a PivotTable and then customize the formulas used in the PivotTable.
To create a PivotTable with Microsoft Office Excel 2007
In Excel, open the workbook where you want to create the PivotTable report, and click the Data tab.
In the Get External Data group, click From Other Sources, and then click From Analysis Services.
The Data Connection Wizard - Connect to Server dialog box opens.
In the Server name box, type the name of the server that is running Analysis Services and the database instance (Server/Instance), and then click Next.
In the Data Connection Wizard - Select Database and Table dialog box, select the TFSWarehouse database, select the Team System cube, and then click Next.
If your server uses SQL Server Enterprise edition, you will have the option of selecting a perspective such as Work Item History that provides a more focused view of the cube.
In the Data Connection Wizard - Save Data Connection File and Finish dialog box, click Finish.
In Import Data, select PivotTable report, and click OK.
In the PivotTable Field List pane, in the Show fields related to box, select the measure group Current Work Item, and then select the measure Current Work item Count.
In the PivotTable Field List pane, drag the field Assigned To.Alias to the Row Labels box.
In the PivotTable Field List pane, drag the field Work Item.System_State to the Column Labels box.
For more information, see How to: Create a Report in Microsoft Excel for Team System.
You might be wondering why this example uses Assigned To.Alias instead of Assigned To.Person. The Person identifier in the cube, for example [Assigned To].[Person].&, is not static; the value can change if the reporting relational warehouse is rebuilt. Although you could work around this issue by querying the relational warehouse for the ID or by updating the report whenever the warehouse is rebuilt, using the alias is simpler.
Displaying Cube Functions
Now that you have your initial PivotTable, you can begin customizing the PivotTable by displaying the formulas so you can edit them.
To view Cube functions
Make sure that the PivotTable is selected so that PivotTable Tools appears.
Click the Options tab.
In the Tools group, click OLAP tools and then select Convert to Formulas.
Customizing the Cube Functions to View Active Work Item Numbers by Person
You can now view and edit the formulas used to generate the data that is displayed in each cell of the PivotTable. For example, the formula for Current Work Item Count is
=CUBEMEMBER("<server name> TfsWarehouse Team System","[Measures].[Current Work Item Count]").
To view only Active work items per person
Select the cell that contains the Current Work Item Count for one person in the PivotTable.
For example, you might see:
=CUBEVALUE("<server name> TfsWarehouse Team System",$A2,B$1)
Add the following expression to the formula:
=CUBEVALUE("<server name> TfsWarehouse Team System",$A2,B$1,"[Work Item].[System_State].&[Active]")
Repeat steps one and two for each person in the PivotTable.
Click the Data tab.
In the Connections group, click Refresh All to update the data in the PivotTable.
The PivotTable now displays only the number of Active work items by person, filtering out other work item states such as Resolved and Closed. You can also chart this information to make it easier to understand, if you so choose.