Creating Reports for Team Foundation Server 2010

John Socha-Leialoha (Murphy and Associates)

June 2010

This article focuses on creating new reports to be viewed through Report Manager, rather than modifying existing reports. The existing reports (Excel, SQL Server Reporting Services) were written by using Microsoft SQL Server Business Intelligence Development Studio (part of the SQL Server client install), which is beyond the scope of this article. This article supplements the MSDN documentation Creating, Customizing, and Managing Reports for Visual Studio ALM.

Applies To

Visual Studio 2010

Team Foundation Server Databases

Dimensions, Attributes, Facts, Stars, and Cubes

Team Foundation Server Cube

Team Foundation Server Relational Warehouse

Reporting Choices

Setting Up Your Computer

Excel Reports

Combining Dimensions and Measures

Creating a Report in Report Builder

Building a Bug Trends Report

Saving your Report

Adding a Graph

Adding Calculated Fields

Adding Parameters

Filters

Where to Go Next

If you’ve used Microsoft® Visual Studio® Team Foundation Server, you may have seen reports and wished you could create your own custom reports. This article is for you. If you want to customize existing reports, you should read this article first, and then read the companion article Customizing Reports for Team Foundation Server 2010.

Several years ago I volunteered to revise some of the standard reports for the Microsoft Solutions Framework (MSF) team at Microsoft. It quickly became apparent that I’d volunteered for more than I’d expected. Working with reports can be very intimidating because it uses different technologies that you may not be familiar with. This article provides an introduction to the important concepts you’ll need in order to learn a "minimal path" through the technologies.

Reporting in Team Foundation Server is built on top of Microsoft SQL Server Reporting Services and Microsoft SQL Server Analysis Services. You’ll also need Excel and Report Builder (a free download). This document covers SQL Server 2008, which is the minimum version required for Team Foundation Server 2010. A previous version of this article addresses SQL Server 2005 tools.

Team Foundation Server Databases

Let’s start by looking at how Team Foundation Server stores information that you might want to use in your reports. The Team Foundation Server database can be broken down into three stores, as shown in Figure 1, with data constantly flowing from left to right. Each store is optimized for a specific type of usage, as explained later in this article (and in more detail at Components of the Data Warehouse for Team Foundation).

Figure 1: Team Foundation Server data flow; current online data is illustrated on the far left, with historical data to its right

Team Foundation Server’s OLTP Database

Team Foundation Server begins with an Online Transaction Processing (OLTP) store that contains all "live" data. The OLTP store contains one or more databases that each holds the data for one team project collection. Examples of data stored in one of these databases are Work Item Tracking, Source Control, and Build. You probably won’t need to know about these individual databases—you just need to know about the existence of an online store that contains multiple databases.

This store is designed to support many simultaneous users, and thus transaction throughput and data integrity are the focus. Additionally, much of the data is stored in normalized tables so that the same information isn’t duplicated in multiple places, which helps with data integrity and transaction performance. However, the normalization means that the information you might want for a report is spread across many tables. If you’ve ever tried to make sense out of the OLTP tables used by Team Foundation Server, you know what I mean—it’s not easy to browse these databases. The schemas are hard to understand and it’s difficult to know which tables to join.

Query performance is also an issue. The more tables you include in a join, the slower the query runs. Additionally, you can imagine that online users might not be happy if your reporting and sleuthing slows down the updating of work items.

The Analysis Services Cube

Fortunately, there is an easy way to get around these issues: using a data store designed specifically for reporting instead of for transactions. The preferred database for reporting isn’t a relational database at all. Instead, it’s an Online Analysis Processing (OLAP) database that contains one or more "cubes." This functionality is provided by Microsoft SQL Server Analysis Services. There is also a new query language named MDX, which is short for Multi-Dimensional eXpressions. This type of database is very useful for analyzing historical data and aggregating values—we’ll come back to this and explain more after a discussion of some concepts that might be new to you. By default, the cube database is called Tfs_Analysis (it was called TFSWarehouse in previous versions).

Additionally, this database can be on a completely different server so queries won’t slow down Team Foundation Server transactions.

For more information, see Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System on MSDN.

Team Foundation Server Relational Warehouse

Behind the cube, Team Foundation Server uses a relational warehouse named, naturally, Tfs_Warehouse (it was called TfsWarehouse in previous versions), that has a schema that is much easier to understand because it is optimized for queries and reporting instead of for transactions.

Data is transferred into this warehouse using Team Foundation Server warehouse data adapters (see Components of the Data Warehouse for Team Foundation). There is one adapter for each Team Foundation Server feature, such as Work Item Tracking, Build, and Source Control. These adapters run periodically (usually within about 3 minutes of a change) to update the data in the warehouse.

For more information, see "Generating Reports Using the Relational Warehouse Database for Visual Studio ALM" on MSDN.

Dimensions, Attributes, Facts, Stars, and Cubes

Before I get into the details of the cube and warehouse, it helps to talk a little about the types of questions you might want to ask and how you would want queries to work. Here are some example questions you might want to ask:

• How many hours of work remain for a specific area of a project?

• How many bugs were opened and closed during the last week?

• Can I see a graph that shows how the remaining work has changed over the last month, plotted daily?

These are just a few examples of the type of information that project managers like to see so they can judge the health and progress of a project.

Questions like these can be broken down into dimensions and facts, which are concepts central to the idea of a star schema, as you’ll see soon. Facts are values, such as a number of hours, and dimensions are the parameters you’re using to control what you see. Quite often, you’ll also want aggregated facts, such as the total number of remaining hours for each area you’ve selected to view.

If you look at these questions, you’ll notice that each has several different dimensions associated with it. For example, the first question uses the Area dimension, as well as Team Project and Work Item Type (the latter isn’t directly in the question, but the question is probably asking how many hours are left in the Task work items).

You could rephrase the first question above to make it explicit that dimensions and facts are used. It might look something like this:

For a specific list of values in the Area dimension, and for a specific value in the Team Project dimension, and for the value Task in the Work Item Type dimension, show me the sum of the Remaining Hours fact.

Figure 2 shows a view of the facts and dimensions for Work Item History.

Figure 2: A subset of the star for Work Item History

Note

In previous versions of Team Foundation Server, the items in red were all dimensions. However, in Team Foundation Server 2010 we’ve reorganized the cube, and now the red items are actually attributes, which you can think of as a "subdimension." For example, Area and Iteration are both attributes of the Work Item dimension. The reason for raising this distinction will become clear later.

The warehouse is organized using what is known as a star schema, which makes it easier to answer common questions you would pose when analyzing data in the cube (which I’ll talk about next).The actual fact table includes more dimensions and facts than are shown in the actual table, but the illustration in Figure 2 should give you an idea of why it is referred to as a star schema.

In the example illustrated in Figure 2, Remaining Work and Completed Work are two of the facts, while Team Project, Date, Iteration, and Area are all dimensions or attributes.

Team Foundation Server Cube

Many reports aggregate values. The way values are aggregated changes depending on which dimensions you decide to use to filter and categorize the data you get back (this will make more sense when we start to work with queries in Excel and Report Builder). Because there can be millions of rows in the facts table, aggregations can become slow very quickly. This is where a "cube" can make a huge difference both in performance and in the type and form of data you can retrieve. Figure 3 summarizes the advantages of the different stores used by Team Foundation Server. In this diagram, ETL refers to Extract, Translate, and Load, which is the process used to convert from the OLTP schema into the star schema of the warehouse. Also, process refers to the extra processing that Microsoft SQL Analysis Services performs on the data from the relational warehouse, such as pre-calculating aggregated values.

Figure 3: Characteristics of TFSWarehouse stores

Hierarchies

The advantage of a cube becomes even clearer when you add yet another ingredient to the pot—hierarchies. Let’s begin with a common example. If you look at dates, you can group them in different ways, such as by year, month, or date. These groupings have a clear hierarchy. The year contains 12 months and each month contains a number of days, as shown in Figure 4.

Figure 4: Example of the Year Month Date hierarchy in Team Foundation Server

Hierarchies like this can be very useful when aggregating values because you can answer questions that would otherwise be very hard to answer. For example, how does the percentage of bugs opened versus bugs closed this month compare with the results from one month ago? Amazingly enough, SQL Server Analysis Services (SSAS) pre-calculates a number of aggregated values at the different levels of hierarchies like this one, as long as the hierarchy is defined in the cube. It also makes it very easy to move to the previous and next nodes at a specific level in this hierarchy. For example, the month before Jan 2010 is Dec 2009. It is obvious, but it’s hard to write a query with standard SQL that does this type of moving around, whereas it’s very easy with SSAS and its MDX query language.

Figure 5 shows an example of Date hierarchies as shown in the query builder we’ll be using later. This figure shows three different hierarchies. The first hierarchy really isn’t a tree because it’s just a flat list of dates. The second hierarchy is organized by year, then month, and finally date, just as shown in Figure 4. The last hierarchy is very similar, except it uses the number of the week in the year instead of the month, so weeks go from 1 to 52.

Figure 5: The cube in Team Foundation Server has three different hierarchies for Date (each small dot next to the rightmost nodes shown represents its level in the hierarchy; three dots [arranged in a triangle] are below the node with two dots).

Team Foundation Server Relational Warehouse

Team Foundation Server’s relational warehouse uses different tables to store different collections of facts. For example, FactWorkItemHistory is the name of a table that stores a number of facts that are used to save historical information about a work item. Likewise, each dimension also has a table. Fact tables have foreign keys that link them to the different dimensions.

Rows are added to the FactWorkItemHistory table each time a work item changes in the OLTP store. These rows are linked to specific values in each dimension table, allowing you to slice the data using these different dimensions.

The Tfs_Warehouse relational warehouse actually contains multiple stars; in fact, it also uses another schema referred to as a snowflake that is based on a star. Again, I won’t get into the details of this here because, as you’ll see, you probably won’t need to delve into that level of detail.

Table 1 lists the different fact tables that are at the center of each star in Team Foundation Server.

Table 1: Fact Tables available in the Team Foundation Server relational warehouse

Fact Table Name	Fact Table Name
FactBuildChangeset	FactTestResult
FactBuildCoverage	FactWorkItemChangeset
FactBuildDetails	FactWorkItemHistory
FactBuildProject	FactWorkItemLinkHistory
FactCodeChurn	FactWorkItemTestResult
FactCurrentWorkItem	FactWorkItemToCategory
FactRunCoverage

Table 2 lists all the dimensions (which are implemented in tables).

Table 2: Dimensions available in the TFSWarehouse relational database

Dimension	Dimension	Dimension
DimArea	DimConfiguration	DimTestRun
DimAssembly	DimDate	DimTestSuite
DimBuild	DimFile	DimToday
DimBuildFlavor	DimIteration	DimToolArtifactDisplayUrl
DimBuildPlatform	DimMachine	DimWorkItem
DimBuildQuality	DimPerson	DimWorkItemCategory
DimBuildStatus	DimTeamProject	DimWorkItemLinkType
DimChangeset	DimTestPlan
DimCodeElement	DimTestResult

Not all dimensions are used by all fact tables. You can use Microsoft SQL Server Management Studio (SSMS) to explore these relationships; you can find what facts are in a fact table and which dimensions a fact table uses. Figure 6 shows an example dimension as viewed in Management Studio. You’ll notice it has multiple, redundant pieces of information about a date. These extra pieces of information are used for hierarchies, which are described in the previous section, and help with slicing and dicing of the data in queries.

Figure 6: The Date dimension as viewed in Management Studio

Figure 7 shows an example of a fact table; in this case, Work Item History. You can see that it contains foreign keys; each of which represents a dimension. The other columns represent facts that are being stored in this table. Therefore, each row contains a set of facts and is connected to specific values in the different dimension tables.

Figure 7: The Work Item History fact table as viewed in Management Studio

Reporting Choices

When working with the cube, there are three reporting choices for creating reports (we’ll discuss the first two here):

• Excel

• Report Builder 2.0 or 3.0

• Business Intelligence Development Studio (BIDS)

I’ve listed these in increasing order of the skills required to use them. Excel is by far the easiest tool to use, with Report Builder 2.0 not that far behind. All of the reports included in Team Foundation Server 2010 were built using BIDS as it provides the most complete control and access to all capabilities of Reporting Services.

Generally, Excel is the tool to start with. You can’t beat the experience for creating simple reports, so we’ll cover using Excel first.

Setting Up Your Computer

That’s enough theory for now. In this section, we’ll get your computer set up so you can start working with the data in the relational warehouse and the cube.

Installing Required Tools

First, you’ll need to make sure you have all the required software installed on your computer. This article assumes you have Team Foundation Server completely installed on a server that you can access. So this section is just about installing the tools you’ll need in order to communicate with Team Foundation Server’s data stores and to create reports. You will need the following:

• Excel 2007 or Excel 2010

• Report Builder 2.0

In addition, if you want to delve into even more advanced report building, you might want to install some of the following tools:

• SQL Server 2008 (SP1 or R2):

  • Management Tools (optional)

  • Business Intelligence Development Studio (optional)

  • SQL Server Books Online (optional)

The Business Intelligence Development Studio (which is part of the SQL Server installation) will install the Visual Studio 2008 shell and the tools you need inside Visual Studio to create and customize Team Foundation Server reports.

Note

The SQL Server client tools that you need are installed by the same installer as the full SQL Server. Run the installer and then select just the tools above to install only the client-side tools.

Finally, you’ll need to make sure you have read access to the warehouse and cube. You can find more information about permissions in the article Assigning Permissions to View and Manage Reports for Visual Studio ALM.

Excel Reports

Provided you’ve created a team project with a portal site on SharePoint Products, it’s very easy to create a new Excel report. From the dashboard itself, you can simply click the New Excel Report button in the toolbar at the top of the dashboard page.

Alternatively, you can use the report template from Team Explorer, which is called Report Template.xltx, as shown in Figure 8. (For another option, see Creating Reports in Microsoft Excel by Using Work Item Queries).

Figure 8: The file called Report Template.xltx provides an easy way to create a new Excel report for your team project

Using either approach will open a new Excel workbook with a PivotTable report that is connected to your Team Foundation Server cube, as you can see in Figure 9.

Figure 9: Using the template or the New Excel Report button opens an Excel workbook that is already bound to the cube and your team project

Note

Unless you’ve changed the Excel defaults, you’ll get a security warning saying that data connections have been disabled. Click the Options… button and select Enable this content to allow Excel to read from the cube. Once you’ve done this, assuming you have read permissions to the cube, the Team Project Hierarchy filter at the top of the PivotTable report will update to show your team project’s name.

Building a Remaining Work Report in Excel

Showing the number of hours of work over time is a very common type of chart. There are many variations on this chart, such as burndown, but we’ll focus on the traditional remaining work chart, as shown in Figure 10, because it’s very easy to build in Excel.

Figure 10: This is a fairly simple chart to build in Excel that shows remaining work in hours

Because we’re going to be using Work Item data only in this chart, you can make your life simpler by filtering the fields. Select Work Item from the Show fields related to: combo box at the top of the PivotTable Field List, (shown in the upper-right corner in Figure 9) which is set to (All) by default.

The first step is to set up the PivotTable report so it shows the data in a form that we can graph. Here are the steps for doing that:

1. In the PivotTable Field List, scroll down until you find the Date dimension. You should see four entries under this node.

2. Expand the node that says Sets.

3. Drag the entry that says Last 4 weeks into the Axis Fields (Categories) section.

4. Scroll up until you find ∑ Work Item and then check both Completed Work and Remaining Work.

Once you’re finished, you should see something like Figure 11.

Figure 11: Your field list should look like this for the Remaining Work report

You now have all the data you need in order to display the report. The next step is to add a PivotChart report:

1. Click anywhere inside the PivotTable report.

2. Click the Options tab of the ribbon (under the PivotTable Tools section).

3. Click the PivotChart button in the Tools section.

4. In the Insert Chart dialog box, click Area in the list on the left.

5. Click the Stacked Area type, which is the second icon from the left under Area.

At this point you’ll have a remaining work chart that is bound to your team data. You can also use the area/iteration filters to limit what you’re graphing, or add new filters (for example, on work item type) to produce even more refined data.

Combining Dimensions and Measures

Not all combinations of dimensions and measures will produce useful results. The measures must have a relationship to the dimensions in your reports in order for you to get meaningful results. So how do you know which dimensions are connected to a given measure?

If you really want to know, you can always check the fact tables in the warehouse by using SSMS. However, if you’re just working inside Excel, you can use the combo box called Show fields related to in order to select a group of measures and dimensions that cover a specific section of the cube. In the example above we used the Work Item related fields, but there are also a number of other options you can choose from. Working within a single set of related fields is easier because you won’t see all fields in the cube, and you’re much more likely to get numbers that make sense.

You can combine measures from different field groups as long as the measures have a common set of dimensions that is used in the report. The Date dimension, for example, appears in a number of different field groups. So you could combine the remaining/completed work data with test result counts shown by day because both these sets of measures have Date as one of the related dimensions.

Creating a Report in Report Builder

The next step up from Excel is Report Builder 2.0, which provides more power and control than Excel and is easier to use than BIDS. It does, however, have limitations, but the set of limitations is smaller with each release. It’s a good place to start when you’ve outgrown Excel.

You will need to download and install Report Builder, which you can do here:

https://www.microsoft.com/downloads/details.aspx?FamilyId=9F783224-9871-4EEA-B1D5-F3140A253DB6

Creating the Data Sources

Start Report Builder, which will open with a new report.

Figure 12: This is what you’ll see when you first launch Report Builder

The next step is to add a source that connects to the cube. To do this, follow these steps:

1. Click the New dropdown in the Report Data toolbar and select Data Source….

2. Type TfsOlapReportDS in the Name text box.

3. Select the Use a shared connection or report model button.

4. Click the Browse button to select a data source from the server.

5. Type the name of your server using a format like this: http://server-name/ReportServer, and click the Open button.

6. Click the Tfs2010OlapReportDS datasource and then click the Open button.

7. Click OK.

Your report should now have one data source, as shown in Figure 13.

Figure 13: After you set up your data source, you’ll see something like this in Report Builder

Building a Simple Query

We’ll be working with the Report Data section of the report in this section. To create a new dataset attached to the cube, follow these steps:

1. Right-click the TfsOlapReportDS dataset that you just added and select Add Dataset….

2. In the Name text box, type a name. The example for this article uses the name dsTest.

3. The Data source combo box will already have TfsOlapReportDS selected. This connects this query to the cube (later we’ll use another data source.

4. Click the Query Designer… button to open the query designer.

At this point, you’ll see a query window that is very different from other query windows you may have seen before, as shown in Figure 14.

Figure 14: This editor appears when you edit a dataset that is connected to the cube; the list on the left will be longer if you’re not using SQL Server Enterprise Edition

This window consists of four main areas: Metadata, Calculated Members, query results, and the dimension filters. We’ll ignore the Calculated Members area for now, but as you’ll see later, it’s very useful for doing calculations on the data.

Before we create a query, note that the Metadata area you see in Figure 14 shows a combination of measures, KPIs (key performance indicators, which we’ll ignore in this article), and dimensions. Right now, the list of dimensions is restricted to a subset named Build. SQL Server Enterprise Edition supports "perspectives" that allow you to restrict your view of dimensions to a subset that are relevant to the type of query you want to build. SQL Server Standard Edition supports only one perspective named Team System. If you see Build above the Metadata tab, click the ellipsis button (...) to the right of Build, and then click Team System in the Cube Selection dialog box.

Now you should see a very long list of dimensions, as shown in Figure 15.

Figure 15: This is what you see when you view the entire cube instead of a perspective

Currently, the query results window doesn't show anything. We can change that by dragging a measure from the Metadata area into the query results area. To do this, follow these steps:

1. Expand the Measures branch of the tree.

2. Open the Work Item folder.

3. Drag the Work Item Count measure into the query results area, as shown in Figure 16.

Figure 16: Dragging a measure into the results area will show an aggregate value

You should now see something like Figure 16, but the value you see under Work Item Count will almost certainly be different. This value is the total number of work items in the cube. And you may have noticed that this number appeared almost instantly, which is a result of the cube pre-calculating a number of aggregated values.

Adding Some Dimensions

Having the total number of work items is fun, but it is not very useful. Let’s say you want to see how many work items are in a specific project. To do this, follow these steps:

1. Expand the Team Project dimension.

2. Drag the Team Project Hierarchy attribute (a child of the dimension) into the filter area above the query results area.

3. Click the Filter Expression cell, and then click the down arrow in the box that looks like a combo box.

4. Select a project that you want to view, as shown in Figure 17.

Figure 17: Results of filtering for only the ProcessTestProjectAgile0 project in my test server for Team Foundation Server

You’ll notice that the number is now lower (assuming you have more than one project on your server that is running Team Foundation Server) because it now shows the number of work items just in that one project.

Showing Multiple Rows

So far, we’ve seen only a single number returned from the query. This isn’t very useful. After all, it’s hard to graph a single number and have it look very interesting. The next step is to add a dimension attribute as a new column in the results area. To do this, follow these step:

1. In the Metadata area, scroll down to the Work Item dimension, and then expand it.

2. Drag the Work Item.Work Item Type attribute into the results area. Before you release the mouse button, notice that a dark blue line shows where the column will be placed in the results. In this case, it will only place the column to the left of the current column.

Figure 18: The results now show how many work items of each type are in the project

The result will be a list of work item types used in your project, along with the number of each type. The example results look like Figure 18, but your results will certainly look different.

Building a Bug Trends Report

With these fundamentals, we’ll create a real report. We’ll use the updated Bug Trends report I created some time ago for the MSF group at Microsoft as an example. However, we won’t create the full report—just enough so that you’ll know how to add all the details. Figure 19 shows what the full report looks like that ships with Team Foundation Server 2010. As you can see, seven different parameters control what you see in the report. For the report we’ll build here, we’ll add only one parameter so you can see how it’s done.

Figure 19: The newer Bug Trends report that is shipped with Team Foundation Server 2010 looks like this when run inside Visual Studio BIDS; seven parameters drive what you see

Let’s think about what type of data we’ll need to create this chart. We’re graphing historical data; in this case, we're graphing data over a period of a month. The data is the number of bugs in each state on each day of the report. To make things more interesting, this graph is also using a rolling average to smooth the lines—a 7-day moving average.

Initially, we’ll build a query that returns the raw data without smoothing. For this query, we’ll need to think about which measures and dimensions we’re going to need. We want results returned for each day of the date range we’ll be using, so we’ll want to include the Date.Date dimension. Recall from Figure 5 that there are several dimension hierarchies for Date, but we don’t need anything other than just the date.

We’re really going to want to start over with a new query. You can delete the existing query if you want (to do this, right-click the dsTest query and then click Delete). To create a new query, follow these steps:

1. In any event, create a new dataset named dsBugTrends using the TfsOlapReportDS dataset.

2. Make sure you’re viewing the Team System perspective instead of Build.

3. In the Metadata tree, expand the Measures branch.

4. Open the Work Item folder.

5. Drag the Work Item Count measure into the query results area.

6. Drag the Team Project Hierarchy attribute under the Team Project dimension into the filter area, and then select the project you want to view.

7. Finally, drag the Date.Date dimension into the query results area. To find this dimension, expand the Date dimension in the Metadata area.

At this point, you should see a number of rows in the result area, with one row for each date. Assuming you’re using a project with some history, this result set could be quite long. Before adding more dimensions, which will cause the result set to grow even larger, it’s a good idea to add some filters. To do this, follow these steps:

1. Drag the Date attribute under the Date dimension into the filter area, as shown in Figure 20.

2. Click in the Operator column for Date, and then click Range (Inclusive) in the combo box.

3. Click in the Filter Expression column and then click a start date in the left combo box and an end date in the right combo box. The example in Figure 20 shows a date range for a month between the middle of September and the middle of October of 2009.

4. Finally, drag the Date attribute of the Date dimension into the query results area.

Figure 20: Filtering based on a date range

You should now see a month of work items. At this point, we’re looking at all work items, but for a report named Bug Trends, we should really be looking at only the Bug work item type. You can add another filter to do just this. To do this, follow these steps:

1. In the Metadata area, expand the Work Item dimension.

2. Drag the Work Item.Work Item Type dimension into the filter area.

3. Click in the Filter Expression cell and select the check box for the Bug work item type (or whatever name you’re using in your project). You can select check boxes for more than one work item type if you have more than one you use for bugs.

You’ll notice that the query results window will update after you’ve added this filter and the numbers will most likely be different. In fact, they should be lower, assuming you have other types of work items in your project.

Thinking About What to Retrieve

So far, the results have shown the total number of bugs that were in your project on each day shown. Because Team Foundation Server doesn’t have a way to delete work items, this number should increase over time, so plotting this information really isn’t very useful.

At this point, it’s good to stand back and think about what you’re trying to accomplish with the report. When you’re looking at bug reports, you might want to look at how the totals change over time, which is the query we have so far. Or you may want to look at the rate of change over time. The latter means you want to see how many new bugs are added, resolved, closed, etc. for each day of the report. Because this is referred to as a Bug Trends report, the word "rates" implies we’re interested in looking at the trend. In other words, we want to view the number of bugs that changed into each state instead of the total number of bugs in each state on a particular day.

Fortunately, the cube has a measure named State Change Count that we can use to get this information. To do this, follow these steps:

1. Click and drag the Work Item Count header out of the result area. This removes this column from the query. You’ll notice the results area is now blank because we’re not asking for any measure.

2. In the Measures branch of the Metadata area, open the Work Item folder, and drag the State Change Count measure into the result area.

At this point, you should see a very different set of numbers. Chances are you’ll see the numbers going lower and higher throughout the month. You’ll also notice that some dates will be missing, which is the case when no work items changed state in your project on that date.

Adding the State Dimension

Now let’s break down the state changes for each day into the different states. The Bug work item can have many states. Active, Resolved, and Closed states are supported out of the box, but your work item might have more, or different, states. These states are defined in the Work Item, so we’ll drag the state attribute and add it as a column in the results. To do this, follow these steps:

1. In the Metadata area, expand the Work Item dimension.

2. Drag the Work Item.State attribute into the result area. You’ll notice that the blue vertical line will allow you to drop it either to the left or right of the date. Drop it on the right side so you’ll see the results grouped by date first and state second, as shown in Figure 21.

Figure 21: Part of the results returned after running the query with Date and State dimensions

Finally, click OK to close the Query Designer dialog, and then OK again to close the Dataset Properties dialog box (you can get back to this dialog at any time by double-clicking the dataset).

That’s all the data you need at the moment to create a graphical report.

Saving your Report

This is probably a good time to save your report; it’s also a good idea to save it frequently as you modify it The first time you save the report, you’ll be asked to select a location and name for the report using a standard file save dialog box. However you’ll be saving your report directly to Reporting Services instead of to a file on your computer. You should save your report to a location inside your team project or to any folder under this team project.

Adding a Graph

All your work so far has been in the Report Data section of Report Builder. There is also an area for designing the reports that will allow you to show your data. In this section, you’ll add a graph to the report and set it up so it shows the results from your query. To do this, follow these steps:

1. If this is the first chart, you can click the Chart icon in the report area. Alternatively, you can click the Insert ribbon and then click the Chart icon and finally Chart Wizard… to achieve the same thing.

2. Make sure the dsBugTrends dataset is selected and then click the Next button.

3. Select Line for the chart type and click the Next button.

4. Drag Date to the Categories section.

5. Drag State to the Series section.

6. Drag State Change Count to the Values section.

7. Click Next.

8. For the chart style, select Generic and click Next.

9. Drag the bottom right adorner (corner) on the graph to make it whatever size you want.

At this point, you’ll see something like Figure 22. When the chart is selected (you might have to click twice), you’ll see three drop zones for data, series, and category fields. These will be explained later in this article.

Figure 22: An chart, when selected, has several drop zones, which you’ll use to add data you want graphed

The report is now ready to test:

1. Make sure the Home ribbon is selected.

2. Click the Run button.

At this point, you should see an actual report running in Report Builder, using data from your server that is running Team Foundation Server, and it should look something like Figure 23.

Figure 23: The report showing state change count by date (but missing some dates)

Look at this graph carefully. Do you notice anything wrong? Do you see anything missing? Look at the dates at the bottom. Dates are missing from this graph. Why?

Showing All Dates

The query we created shows only rows where the measure results are not null. Some of the dates had no work items change states. How do you get all days to show up? You can add another column, such as Work Item Count, that won’t be null. To do this, follow these steps:

1. Click the Design button in the Run ribbon to return to design mode.

2. In the Metadata area, expand the Measures node.

3. Expand the Work Item folder.

4. Drag the Work Item Count measure into the result area (the order of this column doesn’t matter, but it’s a little easier to add it to the far right).

Now your results will include rows for days when all state change values are null. If you click the Run button again, the report should update to show all days of the range you selected. However, as you can see in Figure 24, the image still isn’t right because it has gaps in the lines.

Figure 24: The chart now shows all the dates in the range, but there are gaps in the line where data is missing

Working with Missing Data

The gaps that show up in Figure 24 result when no bugs in this team project change into a state on a day, as I mentioned before. This often happens on weekend days and holidays, for example. If you open the query designer again, you might see data such as that shown in Figure 25.

Figure 25: Some days might have no activity. For example, no work items were closed on 9/16, resulting in a null value being returned.

Whenever there is null data, the graph will not show a line. We can get around this by treating null values as zeros. This is actually fairly easy, thanks to Report Builder’s support for expressions.

1. Click the graph twice so that you see the three data wells appear.

2. Right-click the "button" in the top well that says [Sum(State_Change_Count)] and then click Series Properties....

3. Click the fx button to the right of the Value field text box. This will display an expression editor dialog.

4. Replace the expression with the following expression (as a single line): =IIF(Sum(Fields!State_Change_Count.Value) Is Nothing, 0, Sum(Fields!State_Change_Count.Value)).

5. Click OK, and then click the OK buttons to close the two dialog boxes.

6. Click Run and you should see a graph with the lines going to zero instead of gaps in the lines.

Tip

Expressions that you enter into the Expression dialog box are VB.NET expressions. This means you can use the capabilities of VB.NET expressions (but not statements) to change data before it’s graphed.

Let’s break down this expression so you can see what’s going on.

• Fields!State_Change_Count.Value contains several things. First, the Fields part says that we’re dealing with data obtained from the dataset that is attached to this graph. The State_Change_Count says which field we want to work with, and the Value says we want to work with the number (or null) that is returned from this field.

• The Sum function adds all the records returned that could be included in this data point. Right now that is only one record. However, if you removed State from the series data well, this function would add the values from the Active, Resolved, and Closed records.

• Finally, the IIF is a conditional statement that looks at the first parameter. If the first parameter is true, it returns the second parameter; otherwise, it returns the third parameter. So if the value to be graphed is null, this expression will return 0.

There are many places where you can find more information about using expressions. Any book about Reporting Services will have this information, and many examples are available on the Web as well. Here I’m trying to give you an overview of the different pieces that make up reports rather than going into all the details, which would require a book.

Here is where you’ll find the online help for Report Builder 2.0: Designing and Implementing Reports Using Report Builder 2.0.

Adding Calculated Fields

This article mentioned earlier that we would eventually switch to using a rolling average to smooth out the lines. We can do this by creating a new column in the results that is calculated using values in the cube.

Right now the dsBugTrends query should have four fields under it: Date, State, State_Change_Count and Work_Item_Count. Calculated fields appear in this same list and look exactly like fields returned from the query, and this is where we’ll create a definition for our rolling average. The syntax will look a little odd because it’s actually using a snippet of a query language that’s specifically designed to work with cubes. This query language is named MDX, which stands for Multi-Dimensional eXpressions, and is designed specifically to work with cubes, using knowledge of dimensions and measures. To create a definition for our rolling average, follow these steps:

1. Open the Query Designer dialog box for the dsBugTrends query.

2. Click the Calculated Member icon in the Query Designer dialog box. This opens the Calculated Member Builder dialog box.

3. In the Name text box, type Rolling Average.

4. Enter the following in the Expression text box:

   Avg( [Date].[Date].CurrentMember.Lag(6): [Date].[Date].CurrentMember, [Measures].[State Change Count] )

5. Click OK to finish creating this calculated member.

6. Drag the Rolling Average member into the result area.

7. Click OK in each dialog box to close them.

8. Drag the Rolling_Average column from the dsBugRates dataset into the top drop area of the graph.

9. Right-click <<Expr>> in the top drop area, and then click Delete to remove the old column from the graph.

The preceding expression calculates a 7-day rolling average of the values, and I’ll explain the syntax a little later. At this point, you should see a set of numbers that changes a little more smoothly than the raw State Change Count numbers. Likewise, the graph should be a little smoother now. Figure 26 shows the result of running this on our server.

Figure 26: Graph with a 7-day rolling average; notice that the lines are a little smoother

You can make the lines even smoother by changing the chart type. Right now it’s a Simple Line graph, but if you change it to a Smooth Line graph, you won’t see any kinks in the graph. To do this, follow these steps:

• On the Layout tab, right-click the chart, point to Chart Type, point to Line, and then click Smooth Line (the second line icon from the left).

A Snippet of MDX

Let’s take a look at the expression that calculated the rolling average:

Avg(

[Date].[Date].CurrentMember.Lag(6): [Date].[Date].CurrentMember,

[Measures].[State Change Count]

)

First, Avg is a function (and there are many more) that takes two parameters. The first parameter is actually a "set" of dimension values, where a set can contain zero or more items. And the second parameter is a fact we want used in the expression.

This expression uses values from the Date.Date dimension (see Figure 5), and you’re probably wondering why square brackets enclose each name in this dimension. The square brackets in this specific case are optional. However, names are allowed to have spaces in them, such as in State Change Count. In these cases, the square brackets make it possible for the parser that processes the expression to know where a name starts and ends.

All queries you build by using the cube generate MDX behind the scenes, so that the MDX expression here is combined with the rest of the MDX for the query. As Analysis Services processes an MDX query, it performs calculations for each row returned by the query. CurrentMember in the preceding expression refers to the current "instance" of a dimension for the row being calculated. CurrentMember is attached to the Date.Date dimension, so CurrentMember is the date (such as 1/1/2009) in the row that’s being processed.

After the CurrentMember is another function, Lag, which moves between values at the same level in the hierarchy. Lag will move to the value that is six before the current value. For example, if CurrentMember is 1/10/2009, Lag(6) will return 1/4/2009, which is six days earlier. Finally, the colon specifies a range. The following expression returns a set that contains a week worth of dates, ending with the date from the current row that’s being processed:

[Date].[Date].CurrentMember.Lag(6):[Date].[Date].CurrentMember

The Avg expression in the earlier expression therefore averages the values from the set of seven days, ending in the date from the current row. But what is it averaging? After all, it could be averaging different facts. The answer is in the second parameter, which tells the expression which fact, or "measure," to use in calculating the average. The expression uses [Measures].[State Change Count], which means that it will return the average of State Change Count over a one-week period, ending in the date of the current row.

Finding the Names of Measures and Dimensions

You may have also noticed that the name [Measures].[State Change Count] doesn’t directly match the hierarchy shown in the Measures branch of the tree in Figure 16. So how do you get the correct name? Figure 27 shows how you can use the Metadata tree to get the full name for a measure.

Figure 27: Hovering over a member in the Metadata area displays the MDX name and a short description

If you create this expression from scratch instead of using the provided expression, it’s often easier to use the two trees at the bottom of the Calculated Member Builder dialog box. You can find a name by hovering over an item, as shown for State Change Count in Figure 27. You can also double-click any node of the tree to add the text to the expression text box. The left tree provides access to all the measures and dimensions, while the right tree provides access to various functions and properties.

Adding Minor Tick Marks

The chart needs some polishing to make it look nice. For example, it would be nice to change the title and axes labels, which you can do by clicking several times on each label and then just typing when you’re in design view.

Here we’re going to add minor tick marks so you can count days more easily on the chart. Here are the steps to add these tick marks:

1. Click the date axis until it is selected, as shown in Figure 28.

   Figure 28: The X axis is selected in the designer

   

2. Right click this axis and click Axis Properties….

3. Click Minor Tick Marks in the list on the left side.

4. Click Hide minor tick marks to uncheck it.

5. Enter 1 into the Interval text box.

6. Click OK.

Now when you run the report you should see something like Figure 29.

Figure 29: The final appearance of the graph that you’ll see (with your own data, of course) if you’ve followed along with the preceding steps

Adding Parameters

This report is all very nice, except it’s not very general. In other words, you have to modify the report in Query Designer to change the date range. Obviously, you don’t want to have every user of your report load it into Visual Studio’s Report Designer just to change the date range. What you really want is to allow users of the report to simply select the range of dates.

You can use parameters to perform this type of operation. You’ll be adding two parameters to this report: start date and end date. These parameters will control the date range shown in the report. Additionally, you probably don’t want the project hard-coded into the report, so you can use the same report in any project.

Making Start and End Dates Parameters

At this point, the report query contains three filters that you’ll probably want to convert into parameters, and the process takes a few steps. The first step is to mark which expression values you want to make into parameters. In the Query Design window, select the two check boxes in the row that contains the Date dimension, as shown in Figure 30.

Figure 30: Select the two check boxes in the Parameters column for the Date dimension to make them parameters

There are two check boxes in this row because the filter is over a range of dates. Therefore, there is a check box for the two extremes of the filter.

Report Builder will create two new parameters when you close the dialog boxes. You can find these parameters under the Parameters node in the Report Data section, and they’ll be called FromDateDate and ToDateDate. In case you’re wondering about these strange names, the From and To come from the Range operator, while the DateDate comes from combining the Dimension and Hierarchy columns. As you’ll recall, Date.Date means that we’re using the Date dimension, and we’re looking at the Date attribute of that dimension, instead of another attribute like [Year Month Day].

Important

When Report Builder created the two parameters, it also created two datasets, which we’ll delete later. You can see these datasets by right-clicking the TfsOlapReportDS node and clicking Show Hidden Datasets.

Before we go any further, you can click the Run button to run the report. You’ll see there are two parameters that allow you to set the start and end dates, as shown in Figure 31. However, there are a couple of problems with these parameters. First, they appear as combo boxes that have a very long list of dates instead of appearing as a calendar control. And second, the labels show the internal names and aren’t very user friendly.

Figure 31: The two date parameters appear as very long list boxes

The date parameters appear as a long list instead of a calendar control because they’re defined as a String instead of a DateTime type in the reports parameter dialog box. To see this, double-click one of the date parameters.

Unfortunately, you can’t just change the parameter type to DateTime and have it work. Why not? Well, this gets a little tricky. The Query Builder interface builds an MDX query behind the scenes that assumes that any values coming from parameters will be strings and not any other type. Additionally, the strings often have to be in a very specific format, as you’ll see soon. The solution is to create a new set of parameters that will be the visible set, with the data type of DateTime. The FromDateDate and ToDateDate parameters will then be hidden and will receive their values from the two visible parameters, using some expressions to format the strings correctly.

Start by adding two new parameters and hiding the two existing parameters. To do this, follow these steps:

1. Right click the Parameters node, and then click Add Parameter.

2. In the Name box, enter FromParameter.

3. In the Prompt box, enter Start Date.

4. In the Data type box, select Date/Time.

5. Click Default Values on the left side and then click Specify values.

6. Click the Add button, and then enter the following expression into the Value text box:

   =DateAdd("m",-1,Today())

7. Click the up arrow on the Report Data toolbar so that this new parameter is above the FromDateDate and ToDateDate parameters in the list.

8. Repeat these steps to add a second parameter with Name set to ToParameter, the Prompt of End Date, and the following expression:

   =Today()

You can see what this looks like by clicking the Run button. You’ll see two sets of parameters. The new set of parameters will show a nice pop-up calendar that you can use to select start and end dates. However, at this point they’re not connected to the query. By the way, you’ll soon see why these two new parameters had to be added above the two parameters created by the Query Builder (it’s to control calculation order, which is top to bottom).

The next step is to modify the two auto-generated parameters so they convert the values from the two new parameters into correctly-formatted strings. To do this, follow these steps:

1. Double-click the FromDateDate parameter.

2. In the Available values section, click the None option button.

3. In the Default values section, enter the following text into the Value text box:

   ="[Date].[Date].&[" + CDate(Parameters!FromParameter.Value).ToString("s") + "]"

4. Repeat steps 1–3 for ToDateDate, but use the following expression:

   ="[Date].[Date].&[" + CDate(Parameters!ToParameter.Value).ToString("s") + "]"

5. Click OK.

6. Click the Run button.

The values in the FromDateDate and ToDateDate parameters now have values that look something like the following:

[Date].[Date].&[2009-11-23T00:00:00]

Why this strange value? This value is formatted in MDX. The first Date is the name of the dimension. The second Date indicates the use of the date attribute instead of one of the other attribute options that are available (such as [Year Month Day]). The ampersand character indicates a key value, and the value at the very end is the date formatted in a nonlocale-specific format. Whew! You can learn what format to use for strings like this with the help of the Metadata browser in the Query Builder window. Navigate down the dimension tree that you want and then drill down into the Members/All branch of the tree. When you hover the mouse over one of the values, a tooltip appears that shows an example of what the strings must look like for that dimension.

Now that this is all working, you can hide the two auto-generated parameters and delete the two ugly MDX queries. To do this, follow these steps:

1. Double-click the FromDateDate parameter, and then click the Internal option button.

2. Double-click the ToDateDate parameter, and then click the Internal option button.

3. In the Dataset combo box, click the FromDateDate dataset.

4. Right-click the TfsOlapReportDS node and ensure that Show Hidden Datasets is checked.

5. Right-click the FromDateDate dataset and click Delete.

6. Right-click the ToDateDate dataset and click Delete.

To summarize, you start creating custom parameters by selecting the check box(es) in the Parameters column of the filter area in the Query Designer window. This creates both one or more datasets and one or more report parameters. If you want to delete the auto-generated MDX, you’ll have to create a new set of parameters and hide the old set. Additionally, you’ll modify the old set to format the values from the new set into the correct MDX format as a string.

Using the "Default" Project

The query you created is specific to a single project, which doesn’t make it a very general report. Ideally, this report should show results for the current project. When you deploy a report to Team Foundation Server, you’ll be deploying it to a single project on the server, so the current project is the project that contains the report that you’ve deployed. How can you get this information? You can get it by using expressions and a value from the Globals collection named ReportFolder, along with a call to the SQL warehouse.

The solution shown here isn’t ideal, but it works. One issue is that we need a project parameter that you can use when you’re developing to set the report path, but you’ll want to ignore it when the report is run from the server. You need this because the ReportFolder value is empty when you run the report inside Report Builder—it has a value only when the report is running on SQL Server Reporting Services. To make your report automatically use the project that contains your report, follow these steps:

1. Right click the Parameters node and click Add Parameter, and then create a parameter named ExplicitProject (you can leave all the other settings as they are).

2. Click the Internal option button (which will make this parameter invisible).

3. Click Default Values, click Specify values and then click Add.

4. In the Value text box, type the path of your report, which will look something like this:

   /TfsReports/DefaultCollection/YourProjectName

5. Click OK.

6. Move this parameter to the top of the Parameters list.

7. Add another parameter named ReportPath:

   • Click the Internal option button under Select parameter visibility.

   • Click Default Values and then click the Specify values option button.

   • Click Add and enter this expression (as one line):

     =IIF(LEN(Globals!ReportFolder) > 0, Globals!ReportFolder,Parameters!ExplicitProject.Value)

   • Move this parameter so it is immediately after ExplicitProject.

Finding Project Information

In order to filter on the team project, we’re going to have to look up some information about the project. The parameters we added above will provide us with the path to the report in Reporting Services, and will be something like this:

/TfsReports/DefaultCollection/MyProject/Bugs

The dsBugTrends query, however, requires a value for the team project that looks more like this:

[Team Project].[Team Project Hierarchy].&[{B4A67F25-C7CF-4D03-AEA3-151F21D9A030}]

The first two parts have a pattern that should be somewhat familiar: the dimension followed by the hierarchy name. The last part is the key, which in this case is a GUID (Globally Unique ID) for the team project. We’ll have to write a SQL query to retrieve this GUID from the warehouse. Here are the steps for doing this:

1. Click the New button in the Report Data toolbar and click Data Source….

2. In the Name text box, enter TfsReportDS.

3. Click the Use a shared connection or report model option and then click Browse….

4. Click Tfs2010OlapReportDS and then click Open to select this data source from the server.

5. Click OK to create this data source in your report.

6. Right-click TfsReportDS and click Add Dataset….

7. Enter dsProjectGuid into the Name text box.

8. Enter the following query into the Query text box:

   SELECT ProjectNodeGUID, ProjectNodeName FROM GetProjectNodeInfoFromReportFolder(@ReportPath)

9. Click OK. When you’re prompted to provide a value for @ReportPath, you can just click OK again.

10. Add another parameter called ProjectGuid:

    • Click the Internal option box under Select parameter visibility.

    • Click Available Values and then click the Get values from a query option box.

    • Select dsProjectGuid in the Dataset combo box.

    • Select ProjectNodeGUID in the Value field combo box.

    • Select ProjectNodeName in the Label field combo box.

    • Click Default Values and then click Get values from a query.

    • Select dsProjectGuid for the Dataset, and select ProjectNodeGuid for the Value field.

    • Click OK.

    • Move the ProjectGuid parameter so it is just below the ReportPath parameter.

At this point you should be able to click the Run button and see the report without errors (although the query will still be using the hard-coded project filter).

The next step involves updating the query so it will use the project GUID that we’ve retrieved from the warehouse. Here is a brief overview of what this will involve:

• Tell the query that it should use a parameter.

• Modify parameter information in the query so it uses a "calculated" value based on the ProjectGuid parameter.

• Delete the extra dataset that the query designer will have created "for us."

Follow these steps to make this happen:

1. In the dsBugTrends query designer, check the box in the Parameters column for the Team Project Hierarchy dimension.

2. Click OK in the dialog boxes to close them. Note that it added a new parameter called TeamProjecctTeamProjectHierarchy (which is the name of the dimension plus the name of the hierarchy under this dimension concatenated).

3. Double-click dsBugTrends and click Parameters on the left side.

4. Click the fx button to the right of the TeamProjectTeamProjectHierarchy parameter to open the expression editor for this input parameter.

5. Replace the expression with this one:

   ="[Team Project].[Team Project Hierarchy].&[{" + Parameters!ProjectGuid.Value + "}]"

6. Right-click the TeamProjectTeamProjectHierarchy parameter and then click Delete to remove the parameter that we won’t need anymore.

7. Right-click TfsOlapReportDS and ensure that Show Hidden Datasets is checked.

8. Right-click the TeamProjectTeamProjectHierarchy dataset and click Delete.

The Report Data tree in your project should now look something like Figure 32.

Figure 32: You should see all these elements in your report after you’ve set it up to retrieve the default project from the path of the report on Reporting Services

At this point, if you click Run, your report should run just as it did before we added all these parameters. However, now you could copy this report to another team project and it would show that project’s data instead.

If the report path that you provided has some errors in it (such as a team project collection name that doesn’t exist), you may see an error message like the one shown in Figure 33.

Figure 33: Error message you might see when the path isn’t valid

Warning

The full reports that ship with Team Foundation Server 2010 have the same three parameters at the start of the report, but the queries used in those reports are much more sophisticated. They use handwritten SQL and MDX queries that are designed to handle error conditions such as those above much more gracefully.

Filters

Sometimes you’ll get more data back from a query than you really want. This often happens when you add new columns and receive rows with null values in the old columns. Usually, the query results don’t include rows that have null values in all the columns, so adding a column that has values can show other rows that weren’t returned before because the values were null.

How do you delete these extra rows? You delete them by using filters, which remove rows from the dataset before passing them on to the report.

Filters are added to a dataset using the properties dialog box for a dataset. To open this dialog box, click the ellipsis button (…) that is to the right of the dataset combo box, and then click the Filters tab. You’ll want to add a filter that looks something like the following (where the expression depends on the actual column you want to filter):

=IsNothing(Fields!Current_Work_Item_Count.Value)=False

One very important thing to notice is the equal sign in front of the value. Without the equal sign, the value will be treated as a string. However, the IsNothing function returns a Boolean operator, so you’ll get a type mismatch error.

Where to Go Next

Report Builder 2.0 provides a lot more power and control over your reports than you have with Excel. You get support for multiple data sources, parameters, expressions, and a lot of other functionality that is provided by Reporting Services. Even so, you may eventually run into limits on what you can accomplish. The next step is to use Microsoft SQL Server Business Intelligence Developer Studio (BIDS), which is built on top of Visual Studio 2008.

You’ll need BIDS to modify any of the standard Team Foundation Server 2010 reports, because the handwritten MDX queries are beyond the abilities of Report Builder (you’ll get an obscure error message if you attempt to run one of these reports from within Report Builder). BIDS also provides integration with source code control systems and project management, which are useful features if you’re going to be developing and maintaining a number of reports.

This article just scratches the surface of SQL Server Reporting Services and Analysis Services. We plan to continue to provide more information on our Web site.

Process Templates and Tools

Designing and Implementing Reports Using Report Builder 2.0

Choosing the Source of Data and Authoring Tool for Your Reports for Visual Studio ALM

What's New for Reporting for Visual Studio ALM

Changes and Additions to the Schema for the Analysis Services Cube