Share via

Customizing Reports for Team Foundation Server 2010

  • Article

John Socha-Leialoha (Murphy and Associates)

June 2010

In this article I’ll cover some more advanced subjects related to customizing the reports shipped with Team Foundation Server 2010. These reports are much more advanced and use a lot of handwritten queries that are written in either SQL or Multidimensional Expressions (MDX). The majority of the reports use MDX, and many people already know SQL, so in this article I’ll focus just on the MDX side.

Applies To

Visual Studio 2010

Getting Set Up to Customize Reports

Creating a Report Server Project

Adding a Priority Parameter

Introduction to MDX

Using MDX in the Priority Parameter

Improving the Priority Query

Testing Parameterized Queries

Selecting Date Ranges

Restricting Evaluation to a Sub-Cube

Defining Named Sets

Dissecting the Priority Query

In a previous article, Creating Reports for Team Foundation Server 2010, I showed how you can create new reports using either Microsoft Excel or Report Builder 2.0. If you’re not familiar with reports, you should read that article first.

Note

Although this white paper is not designed for Report Builder 2.0, the section about adding the Priority parameter will work with Report Builder.

Getting Set Up to Customize Reports

Before you can customize existing reports, you’ll need to install the required software. The reports that ship with Team Foundation Server 2010 were all written in Microsoft SQL Server Business Intelligence Development Studio (BIDS), and this is the tool you’ll need to use to customize these reports. You could also use Report Builder 3.0 for this task.

You install BIDS using the SQL Server 2008 installer, even if you don’t plan to install SQL Server itself on your computer. Simply walk through the screens until you get to the list of components to install (you’ll actually "install" several sets of support components before you get to this screen) as shown in Figure 1. Then select BIDS at a minimum. Management Tools is also useful; this gives you SQL Server Management Studio (SSMS).

Figure 1: Select the SQL client tools that you want to install

Ff730838.CustomizeReports_TFS2010_Fig1(en-us,VS.100).png

Creating a Report Server Project

Launch SQL Server Business Intelligence Studio and click File, New, Project to display the New Project dialog box. If you’ve used Visual Studio, the interface will be familiar. In fact, if you have Visual Studio 2008 installed already, the BIDS tools are also available directly from within Visual Studio.

The New Project dialog box should look something like Figure 2. After you name your project and choose a location, click Report Server Project under Templates, and then click OK.

Figure 2: The Business Intelligence Projects includes a number of different project templates, but we’ll be using only Report Server Project

Ff730838.CustomizeReports_TFS2010_Fig2(en-us,VS.100).png

Your new project should contain only two empty folders, as shown in Figure 3.

Figure 3: A new empty report project should look like this

Ff730838.CustomizeReports_TFS2010_Fig3(en-us,VS.100).png

Creating the Data Sources

The next step is to add two data sources: one that connects to the relational warehouse and one that connects to the cube. To add the relational data source, follow these steps:

  1. Right-click the Shared Data Sources folder, and then click Add New Data Source.

  2. On the General tab, type TfsReportDS in the Name text box. Many reports in Team Foundation Server expect this data source name, so the name is actually important when you’re customizing Team Foundation Server reports.

  3. In the Type combo box, click Microsoft SQL Server.

  4. Create the connection string for connecting to the SQL Server instance that is hosting the data warehouse. It’s easiest to click the Edit button and enter appropriate information in the fields. You’ll want to select the Tfs_Warehouse database. You will need to make sure your administrator has given you access rights to the database.

  5. Click OK.

Next, you’ll need to create a data source that connects to the cube. To do this, follow these steps:

  1. Right-click the Shared Data Sources folder, and then click Add New Data Source.

  2. On the General tab, type TfsOlapReportDS in the Name text box. Many reports in Team Foundation Server expect this data source name, so the name is actually important when you’re customizing Team Foundation Server reports.

  3. In the Type combo box, click Microsoft SQL Server Analysis Services.

  4. Create the connection string for connecting to the SQL Server instance that is hosting the data warehouse. It’s easiest to click the Edit button and enter the appropriate information in the fields. You’ll want to select the Tfs_Analysis database. You will need to make sure your administrator has given you access rights to the database.

  5. Click OK.

Your project should now have the two data sources, as shown in Figure 4.

Figure 4: After you set up your two data sources, you’ll see something like this in Solution Explorer

Ff730838.CustomizeReports_TFS2010_Fig4(en-us,VS.100).png

Adding a Report

First, you’ll need to get a copy of an existing report that you want to customize. You can do this through Report Manager. Here is how to use Report Manager to download a report:

  1. From the Team Explorer window, right-click the Reports node and click Show Report Site. This opens a Web browser with Report Manager shown in the browser, which will look something like Figure 5.

    Figure 5: Report Manager shows a list of folders and controls to manage the reports in your project

    Ff730838.CustomizeReports_TFS2010_Fig5(en-us,VS.100).png

  2. Drill down to the report that you want to customize (we’ll use the Remaining Work report, which is in the Project Management folder) and then click that report.

  3. Once the report renders, click the Properties tab at the top of the report.

  4. Click the Edit link near the bottom of the properties page, and then click Save in the File Download dialog box.

  5. Select the location of your report project in the Save As dialog box to save a copy of the report.

  6. Back in BIDS, right-click the Reports folder in Solution Explorer, click Add, and then click Existing Item.

  7. In the Add Existing Item dialog box, click the report you downloaded in step 5 and then click Add.

You should now see your report in Solution Explorer under the Reports folder. When you open the report by double-clicking it, you’ll see a window with two tabs: Design and Preview, as well as a Report Data tree on the left that has report parameters, data sources, and queries (or datasets).

Adding a Priority Parameter

Some types of customization are fairly common. We’ll cover two in this document: adding a new filter parameter and supporting additional states. In this section we’ll add a filter parameter that will allow you to see the number of hours remaining and completed for a subset of tasks, based on the priority of those tasks. I won’t go into why this might be useful because our goal here is to simply present the steps required to add a new filter.

Here are the high-level steps that are needed to make this work:

  • Create a new dataset that provides the list of priorities used in this project.

  • Add a parameter to display this list of priorities.

  • Update the query that drives the chart so it uses this new filter.

It will take us a little while to accomplish all these goals, and then we’ll come back again to the query. We’ll build the initial parameter query using the MDX query designer, which is fairly easy to use. The query generated by the designer, however, has some limitations that we’ll want to remove, so we’ll return to the query later and replace it with handwritten MDX.

Creating the Priority Query

The query we’ll need to drive the Priority parameter will need to return a set of priority names and values.

  1. Right-click the TfsOlapReportDS dataset and click Add Dataset….

  2. Type dsPriority into the Name text box and then click the Query Designer… button.

  3. If the perspective at the top does not say Team System, click the … button and select Team System.

  4. Optionally, click the Measure Group combo box and then click Work Item to reduce the number of dimensions and measures that will be visible.

  5. Expand the Measures node and then the Work Item node.

  6. Drag Work Item Count to the query results area. At this point you should see the number of work items in your team project.

  7. Expand the Work Item dimension and then the Microsoft.VSTS.Common folder.

  8. Drag Work Item.Priority into the query results area. You should now see something like Figure 6.

    Figure 6: The priority values that are used in the cube (across all team projects)

    Ff730838.CustomizeReports_TFS2010_Fig6(en-us,VS.100).png

  9. Click OK and then OK to save this new dataset.

One potential issue with this query is that it shows all priorities, even if they’re not used by the current team project. We’ll come back to this issue later.

Adding the PriorityID Calculated Member

The query that we have right now returns a list of values. However, when we’re working with the cube, the value alone isn’t enough to use as a filter for our data. Why not?

Analysis Services needs to know the context of the value in order to filter on that value. In other words, it doesn’t know that the value "1" in Figure 6 refers to a priority. To provide the context, we need to provide it with a string formatted using syntax that the cube expects, which is using the MDX language.

The particular value that we want is known as the unique name, which is a string that looks something like this.

[Work Item].[Microsoft_VSTS_Common_Priority].&[1]

The first part, Work Item, is the dimension where priorities can be found. The second part, Microsoft_VSTS_Common_Priority, is the name of the priority attribute. Because this field is not a built-in field, but rather is defined by the Agile and Capability Maturity Model Integration (CMMI) process templates, it has a longer name than some other attributes. The final part is the "key" for this attribute. The "&" character in front of the number indicates that the value after it is a key rather than a name.

The entire string uniquely identifies a member, which is the name used to refer to individual elements of this attribute.

So how do we get these member unique names into our query results? You’ll define a calculated member. As I mentioned briefly in Creating Reports for Team Foundation Server 2010, you can create a new calculated member in the Query Designer dialog box by following these steps:

  1. Right-click the dsPriority dataset and then click Query… to open the Query Designer dialog box.

  2. Right-click the Calculated Members section of this dialog box and then click New Calculated Member. This will display the Calculated Member Builder dialog box.

  3. Type PriorityID into the Name text box.

  4. Type the following into the Expression text box.

    [Work Item].[Microsoft_VSTS_Common_Priority].CurrentMember.UniqueName

  5. You should see something like Figure 7. Then click OK.

    Figure 7: Here is what you should see when you’re creating the PriorityID calculated member

    Ff730838.CustomizeReports_TFS2010_Fig7(en-us,VS.100).png

Now you should see this new calculated member in your Query Designer dialog box. You can drag it into the query results area to add it to the results that will be returned by this query (see Figure 8).

  1. Drag the PriorityID calculated member into the query results area.

  2. Click the OK button to save these changes to the query.

Figure 8: The PriorityID column will appear in the query results once you’ve dragged it from the Calculated Members area to the query results area

Ff730838.CustomizeReports_TFS2010_Fig8(en-us,VS.100).png

Creating the Priority Parameter

Now that your query contains all the elements required for a filter, you can add a parameter that allows you to select which values will be used in the filter. (Of course the report won’t be filtered using these values until we update the main query to use the parameter we’re about to create.)

Here are the steps for creating the priority parameter:

  1. Right-click the Parameters node of the Report Data tree and then click Add Parameter….

  2. Type PriorityParam into the Name text box.

  3. Type Priority into the Prompt text box.

  4. Check Allow multiple values.

  5. Click the Available Values section and then click the Get values from query option button.

  6. Select dsPriority in the Dataset combo box.

  7. Select PriorityID in the Value field combo box.

  8. Select Priority in the Label field combo box.

  9. Click the Default Values section and then click the Get values from query option button.

  10. Select dsPriority in the Dataset combo box.

  11. Select PriorityID in the Value field combo box.

  12. Click OK.

If you click the Preview tab you should see a new parameter in your report with the list of priority values (see Figure 9). Initially it will have all the possible values checked, but you can change this.

Figure 9: The new priority parameter initially has all the values checked

Ff730838.CustomizeReports_TFS2010_Fig9(en-us,VS.100).png

You may have noticed that this parameter behaves differently from the other parameters in the report. Most of the other parameters have an additional item at the top called All (No Filter) that is equivalent to selecting all the values. The new Priority parameter just shows the list of values, which are all checked by default.

Using the All (No Filter) allows the report to work better when there isn’t much data in the cube. However, adding it to the query requires replacing the existing query with handwritten MDX, which we’ll do later. With handwritten MDX, we’ll also be able to remove the Unknown member, which represents work items where the priority field was left blank or isn’t present.

We should make some other improvements to the parameter query. Here is a list of improvements we’ll make later:

  • Add All (No Filter) means we don’t care what value the work item has, or even if it has a value (Unknown member).

  • Remove the Unknown member from the list.

  • Limit the list of values to ones used in the current team project.

  • Filter the list of values against the work item type the report is showing, which is Task by default for the Remaining Work report.

  • Ensure that this parameter will have at least one value, and at least one default value, when the cube doesn’t have enough data (we’ll go into this more later).

Many items in this list will help you make more professional and robust reports, which should help reduce the edge cases that would cause complaints from your users. If you’re the only one using the report, these steps may not be important.

Filtering on Priority

Now that you have a way to select the priority values, the next step is to actually use that set of values as a filter in the query used to drive the graph. This query is called dsBurndown (I know, it should have a different name, but that’s the name it has). Here are the steps required in order to filter on the PriorityParam:

  1. Double-click the dsBurndown query. This will open the Dataset Properties dialog.

  2. Click inside the Query text box (it has a gray background), press Ctrl-A to select all the text, and then press Ctrl-C to copy it.

  3. Click the Query Designer… button.

  4. Select the entire query, press the DEL key to delete it, and then press Ctrl-V to paste the query from step 2 into the query text box.

    Note

    Steps 2 and 4 are required whenever you edit a query in order to keep the formatting for the query. If you don’t do this, your query will look like Figure 10, where all the lines are jumbled together. The query will still run, but it will be harder to read and modify.

    Figure 10: The query designer often ignores line formatting when you open the query for editing. The steps above describe how to avoid this.

    Ff730838.CustomizeReports_TFS2010_Fig10(en-us,VS.100).png

  5. Modify the query by adding @PriorityParam, as shown here, between two existing lines.

    StrToSet(@IterationParam), StrToSet(@PriorityParam), Except(

  6. Click the Query Parameters icon Query Parameters Icon to display the Query Parameters dialog box. This is the set of parameters that is used in the query. The parameter values you set here are used only when you run the query inside the query designer.

  7. Add the PriorityParam to the list of parameters:

    • Click the <Enter Parameter> entry in the last row of the Parameter column and type PriorityParam.

    • Click in the Dimension column on this line and select Work Item.

    • Click in the Hierarchy column and select Work Item.Priority. The values are alphabetized within each group, so you’ll have to search a bit to find it.

    • Check the Multiple values check box.

    • Click in the Default column and check the box next to All. When you’re done, you should see something like Figure 11.

  8. You’ll need to change the ProjectGuid parameter to the value used for your team project (you’ll find instructions below on how to look this up).

  9. Set the StartDateParam and EndDateParam defaults to dates where you have data that will show up in your report.

  10. Click OK.

Figure 11

Ff730838.CustomizeReports_TFS2010_Fig11(en-us,VS.100).png

Finding the Project GUID

In the previous section, I instructed you to fill in the GUID for your team project. Here is one way you can find that project GUID:

  1. Using SQL Server Management Studio (SSMS), connect to the SQL Server instance that has the warehouse.

  2. In the Object Explorer of SSMS, expand the Databases node and click Tfs_Warehouse (or the name of your warehouse if it’s different from the default), and then click the New Query button.

  3. Enter the following query and then run it.

    SELECT ProjectNodeName, ProjectNodeGUID, ProjectPath FROM DimTeamProject ORDER BY ProjectPath

  4. Find your team project in this list. Right-click the GUID and select Copy to copy the GUID to the Clipboard.

Testing the Priority Filter

You should now be able to run your query inside the Query Designer dialog by clicking the red ! in the toolbar at the top of the dialog box. You can then try different sets of priority values to make sure the query results change:

  1. Open the Query Parameters dialog box.

  2. Click twice on the Default cell for the PriorityParam parameter to see the list of values.

  3. Uncheck and check values to change the default for testing the query. See Figure 12.

  4. Click OK to close the Query Parameters dialog box.

  5. Click the red ! to run the query with the new values.

  6. Click OK twice to close the remaining dialog boxes.

Figure 12: Select different sets of priority values to ensure that your query is filtering on these values

Ff730838.CustomizeReports_TFS2010_Fig12(en-us,VS.100).png

You now have a priority parameter fully connected to the report. You can click the Preview tab to try out this parameter.

Introduction to MDX

In the next few sections we’ll be improving the Priority parameter to work the same way as the standard report parameters for Team Foundation Server 2010. In fact, when we’re done, this parameter query will be identical to queries you’ll find in the shipping reports.

Entire books are written about MDX, so obviously we won’t cover a whole lot about MDX in here. However, Reporting Services limits you to a subset of the available syntax, and some simple patterns will help you understand the MDX that's found in the Agile and CMMI reports that are shipped with Team Foundation Server.

The most basic pattern you’ll see in the shipping reports looks something like this.

SELECT
{
measures
} ON COLUMNS,
{
dimensions
} ON ROWS
FROM [Team System]

The first part of the query selects one or more measures that will be shown in the columns of the query results. In general MDX, columns don’t have to be measures, but Reporting Services restricts this section to measures only.

The next part is a list of dimensions/attributes that will be used for the rows of the query results. Each row represents a combination of these dimension/attribute members that is called a tuple. The following is an example of a tuple that is a result of combining date and state dimensions.

([Date].[Date].&[2009-12-28T00:00:00], [Work Item].[System_State].&[Active])

So this tuple refers to the combination of one date member and one state member. Values inside a set of parentheses like this define a single tuple, and each value in a tuple must come from a different dimension/attribute combination.

Finally, the FROM keyword is used to specify which cube we want to use for retrieving our data. The only cube created by Team Foundation Server is called Team System. Later on we’ll show you how to define a sub-cube to reduce the data evaluated by the query, which will improve performance for many queries.

Let’s see this in action:

  1. Open SSMS and connect to the server that is running Analysis Services and that is hosting the Team Foundation Server cube.

  2. Expand the Databases node and click the Tfs_Analysis database (or whatever your cube is called) and then click the New Query button. This will open an MDX query window.

  3. Enter the following MDX into the query editor.

    SELECT
{
    [Measures].[Work Item Count]
} ON COLUMNS,
{
    [Work Item].[System_State].[System_State]
} ON ROWS
FROM [Team System]

  4. Click the ! Execute button and you should see something like Figure 13.

    Figure 13: You’ll see something like this when you run a simple query in SQL Server Management Studio

    Ff730838.CustomizeReports_TFS2010_Fig13(en-us,VS.100).png

This query is actually not that far from a query we could use to get the states for a State parameter. However, it’s missing the unique name for each member.

Defining a Calculated Measure

In front of the query we can define zero or more calculated measures, and then use these in the query like this.

 1: WITH
 2:     MEMBER [Measures].[ParameterValue] AS
 3:         [Work Item].[System_State].CurrentMember.UniqueName
 4: SELECT
 5: {
 6:     [Measures].[Work Item Count]
 7: } ON COLUMNS,
 8: {
 9:     [Work Item].[System_State].[System_State]
10: } ON ROWS
11: FROM [Team System]

Rows 1–3 define a new measure that exists for this query only. The WITH statement marks the start of a section that is used for defining custom measures for this query, as well as named sets (which we won’t cover here). Each new measure starts with MEMBER and then has a name, which is of the format [Measures].[measure name]. Finally, the AS keyword marks the start of the actual definition of the measure. Line 3 returns the unique name of one member. But which member does it evaluate?

The ROWS section (lines 8–10) defines which tuples will be evaluated by the query. Here we’re looking at the Work Item dimension, the System_State attribute, and the System_State level of this attribute. Figure 14 shows a visual representation of this path, as seen in the MDX query window of SSMS. The strings shown in this tree, however, are the names of these items rather than the actual MDX syntax you need to use. You can see that by hovering the mouse over the level, as shown in Figure 15.

Figure 14: When you expand the Work Item.State attribute, you’ll see another level called State

Ff730838.CustomizeReports_TFS2010_Fig14(en-us,VS.100).png

Figure 15: Hover the mouse over the State level and you’ll see a tooltip that has the full MDX path to this level

Ff730838.CustomizeReports_TFS2010_Fig15(en-us,VS.100).png

As Analysis Services evaluates this query, it will apply the measures against each tuple, which is where the CurrentMember function in line 3 comes into play. Because the function is applied to a "path," current member will give us the current state that is being evaluated. Finally, the Unique_Name function returns the extract string we want, and the results now look like Figure 16.

Figure 16: The UniqueName function returns the "full path" to each State member returned as a row

Ff730838.CustomizeReports_TFS2010_Fig16(en-us,VS.100).png

Using MDX in the Priority Parameter

The MDX query we just created can be turned very easily into a priority query that returns exactly the same results as the current query we built using the MDX query builder.

WITH
    MEMBER [Measures].[PriorityID] AS
        [Microsoft_VSTS_Common_Priority].CurrentMember.UniqueName
SELECT
{
    [Measures].[PriorityID],
    [Measures].[Work Item Count]
} ON COLUMNS,
{
    [Work Item].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority]
} ON ROWS
FROM [Team System]

Here are the instructions for replacing the existing query with this MDX:

  1. Double-click the dsPriority dataset and click the Query Designer… button.

  2. Click the Design Mode icon in the toolbarDesign Mode Icon. At this point the MDX query designer will change to show the MDX behind the graphical query, as shown in Figure 17.

    Figure 17: This is the MDX that is generated by the graphical MDX query designer that you’ll see when you turn off Design Mode

    Ff730838.CustomizeReports_TFS2010_Fig17(en-us,VS.100).png

  3. Delete this MDX and replace it with the MDX above.

  4. Click the Click to execute the query hyperlink in the query results (or the red ! in the toolbar) to run this query. You should see the same results as before.

  5. Click OK and then OK to save these changes.

Improving the Priority Query

As mentioned above, we’ll make several improvements to the query to make it act like the other queries in the Remaining Work report. Each section below deals with a different refinement.

Adding the All Item to the Parameter

This step will make the query a little more complicated. Right now the query returns all priority values that you would expect to see. One other member is basically the parent of all the members you see in the query results, as shown in Figure 18. This member is the All member, and its unique name looks like this:

Figure 18: The All member is the parent of the members with specific values (and also Unknown) as seen in the MDX query window of SSMS

Ff730838.CustomizeReports_TFS2010_Fig18(en-us,VS.100).png

It turns out that a function is available that will return the All member and all child nodes of this member: AllMembers. Here is how you can modify the query to use this function:

  1. Change the ROWS section of the query to look like the following, where we’ve changed the last part to just AllMembers.

    {
    [Work Item].[Microsoft_VSTS_Common_Priority].AllMembers
} ON ROWS

  2. Add the following calculated measure.

    MEMBER [Measures].[PriorityCaption] AS
  IIF([Work Item].[Microsoft_VSTS_Common_Priority].CurrentMember
        IS [Work Item].[Microsoft_VSTS_Common_Priority].[All],
      @AllNoFilter,
      [Work Item].[Microsoft_VSTS_Common_Priority].CurrentMember.Member_Caption
  )

  3. Add this measure to the list of measures returned from the query.

    {
    [Measures].[PriorityCaption],
    [Measures].[PriorityID],

  4. Open the Query Parameters dialog box and add the AllNoFilter parameter with the default value of All (No Filter). You can leave all the other columns blank.

  5. Click OK to save the new query parameter and then run your query again. You should see something like Figure 19.

    Figure 19: The query results now display the All member with a custom caption

    Ff730838.CustomizeReports_TFS2010_Fig19(en-us,VS.100).png

  6. Close the dialog boxes.

  7. Double-click the PriorityParam parameter and then the Available Values section.

  8. Change the Label field to use ParamCaption.

  9. Save these changes and then run the report.

These are probably a few more changes than you were expecting. However, the All member doesn’t have a caption (notice the null in the Priority column of Figure 19). Without adding a new calculated measure and using this new column for the parameter values, we would see [Work Item].[Microsoft_VSTS_Common_Priority].[All] in the list of priorities instead of the friendlier All (No Filter).

Let’s take a look at how this all works. The new calculated measure uses an MDX function called IIF that takes three parameters. The function returns the value of the second parameter if the first parameter is true; otherwise, it returns the value of the second parameter. So in this case we’re saying that if the current priority member is the All member, return the value of the AllNoFilter parameter. Otherwise, just return the caption for the current member.

Notice one other thing. Query parameters that appear inside the MDX query start with the @ character and must be listed in the Query Parameters dialog box in order for you to run the query.

Changing the Default Priority to All

Now that we have the All member in the query, we can change the Priority parameter so it selects only this value by default. This is actually very easy:

  1. Double-click the PriorityParam parameter and then click Default Values.

  2. Click the Specify values option button.

  3. Click the Add button and enter the following into the Value text box.

    [Work Item].[Microsoft_VSTS_Common_Priority].[All]

Now when you preview the report, you’ll see that only All (No Filter) is selected, just like the other parameters in the Remaining Work report.

Testing Parameterized Queries

The Report Designer dialog boxes in BIDS are not the friendliest places to test queries. SSMS provides a better environment, but it doesn’t support Reporting Services query parameters. Fortunately, another tool does support parameters, called MDX Studio. You can download a copy here: http://www.mosha.com/msolap/mdxstudio.htm.

The program is pretty easy to use but provides a lot more information than you might want. Figure 20 shows what you’ll see when you run a query that has parameter values. In the next section we’ll provide more details about how to run this parameter. Here we’ll focus instead on just using the tool.

Figure 20: This is what MDX Studio looks like after you execute the parameterized query presented in the next section

Ff730838.CustomizeReports_TFS2010_Fig20(en-us,VS.100).png

When you first start MDX Studio, it displays a Connect to Analysis Services dialog box. You’ll probably just need to enter your server name into the Server name text box and then click the Connect button to connect.

If you have more than one cube on the server, you might have to change the Catalog and Cube combo boxes. Select the name of your "catalog," which is the name of the AS database, in the Catalog combo box. Then select Team System from the Cube combo box. At this point you can enter and execute queries in the Query tab.

Selecting Date Ranges

We’re going to put the Priority query aside for a few sections to talk about some other patterns you’ll find in MDX. In this section we’ll show how to select a range of dates based on data parameters. The Filter function allows you to apply a filter to a set of members. Here is what it looks like for dates.

Filter(
[Date].[Date].[Date],
[Date].[Date].CurrentMember.Member_Value >= CDate(@StartDateParam) AND
[Date].[Date].CurrentMember.Member_Value <= CDate(@EndDateParam)
)

The first parameter specifies the members that you want to filter. Here we’re looking at the date dimension, the date hierarchy (which is just a flat list), and the date level, which contains all the dates in the flat list. In other words, [Date].[Date].[Date] is a list of all the dates defined in the cube.

The second parameter is an expression that determines whether a specific member will be included in the list returned by the Filter function. The CurrentMember function returns the date member that is being evaluated, and the Member_Value property returns the date/time value of this member. Dates in the Team Foundation Server cube are always as of midnight, so the time element is always zero. This function, therefore, returns all date members between the start and end dates.

Restricting Evaluation to a Sub-Cube

The main query that drives reports is filtered using the different report parameters. Common filters are team project, area and iteration hierarchies, state, etc. You can make queries run a lot faster if you limit the scope of the main part of the query, which you can do with a sub-cube. Here is an example of the sub-cube definition from the Bug Status report.

SELECT
... ON COLUMNS,
... ON ROWS
FROM
(
SELECT
    CrossJoin(
        StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
        StrToMember("[Work Item].[System_WorkItemType].[" + @BugName + "]"),
        StrToSet(@AreaParam),
        StrToSet(@IterationParam),
        StrToSet(@StateParam),
        StrToSet(@PriorityParam),
        StrToSet(@SeverityParam)
    ) ON COLUMNS
    FROM [Team System]
)

Notice that the FROM now contains a SELECT statement. This SELECT statement uses a CrossJoin to combine all the different filters that define a subset of the cube. The StrToMember functions use a single member, and the StrToSet turns the checked values in parameters into a list of members. The tuples returned by this CrossJoin function define the sub-cube. The rest of the query (the COLUMNS and ROWS sections) will evaluate only cells (facts) that are contained in this sub-cube.

Defining Named Sets

Some queries in the Team Foundation Server reports use named queries, which makes it easier to work with the same set more than once or just to make the query easier to read. Here is an example of a named set and its use from the dsBugCount query in the Bug Status report.

WITH
    SET [Date Range] AS
        Filter(
             [Date].[Date].[Date],
             [Date].[Date].CurrentMember.Member_Value >= CDate(@StartDateParam) AND
             [Date].[Date].CurrentMember.Member_Value <= CDate(@EndDateParam)
        )

    MEMBER [Measures].[DateValue] AS
         [Date].[Date].CurrentMember.Member_Value

SELECT
{
     [Measures].[DateValue],
     [Measures].[Work Item Count]
} ON COLUMNS,
{
    CrossJoin(
         [Date Range],
         [Work Item].[System_State].[System_State]
    )
} ON ROWS
...

I’ve not shown the rest of the query, which is the sub-cube definition described in the previous section.

The SET keyword is used to define a new named set. As you can see here, the [Date Range] named set is the list of all date members between the start and end dates, inclusive. You can then use this named set in the ROWS section as shown here.

Working with Dates

You may have noticed in this query that we also defined a calculated measure called DateValue. Why did we do that when this query is already returning the date in the ROWS section? It turns out that dimension values are always returned as strings to Reporting Services. However, our reports are designed so that the person viewing the report can see dates and times formatted using their settings. In order to do that, the query needs to return dates as a date/time value, not a string. This is precisely what the DateValue calculated measure accomplishes.

Dissecting the Priority Query

The actual Priority query that you’ll find in the Team Foundation Server reports is a lot more complicated than the query we built above. The extra code is designed to handle a number of special cases, which we’ll go into later.

At first this query will seem very intimidating, but I’ll break it down into pieces that are easier to understand in the following sections.

WITH
MEMBER [Measures].[PriorityCaption] AS
  IIF([Work Item].[Microsoft_VSTS_Common_Priority].CurrentMember 
        IS [Work Item].[Microsoft_VSTS_Common_Priority].[All],
      @AllNoFilter,
      [Work Item].[Microsoft_VSTS_Common_Priority].CurrentMember.Member_Caption
  )
MEMBER [Measures].[PriorityID] AS
    [Work Item].[Microsoft_VSTS_Common_Priority].CurrentMember.UniqueName
SELECT
{
    [Measures].[Revision Count],
    [Measures].[PriorityID],
    [Measures].[PriorityCaption]
} ON COLUMNS,
{
    Except(
        Extract(
            Union(
                CrossJoin(
                    [Work Item].[Microsoft_VSTS_Common_Priority].[All],
                    [Team Project].[Team Project Hierarchy].[All]
                ),
                NonEmpty(
                    CrossJoin(
                        [Work Item].[Microsoft_VSTS_Common_Priority].AllMembers,
                        StrToMember("[Team Project].[Team Project Hierarchy].&[{"+@ProjectGuid + "}]")
                    ),
                    [Measures].[State Change Count]
                )
            ),
            [Work Item].[Microsoft_VSTS_Common_Priority]
        ),
        [Work Item].[Microsoft_VSTS_Common_Priority].[All].UnknownMember
    )
} ON ROWS
FROM [Team System]

Let’s start at the top, where you’ll find two calculated measures. These are exactly the same as we had in the previous section.

Next you’ll find the columns section. Again, there shouldn’t be any surprises here.

The bulk of the MDX is actually in the rows section, and this is where we’ll spend our time in the next few sections. It will be easier to understand and explain this by working our way from the inner parts out.

You can execute this query in MDX Studio by pasting the query into the Query window and then adding the following XML after the query.

<Parameters xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
            xmlns:xsd=http://www.w3.org/2001/XMLSchema
            xmlns="urn:schemas-microsoft-com:xml-analysis">
    <Parameter>
        <Name>ProjectGuid</Name>
        <Value xsi:type="xsd:string">B4A67F25-C7CF-4D03-AEA3-151F21D9A030</Value>
    </Parameter>
    <Parameter>
        <Name>AllNoFilter</Name>
        <Value xsi:type="xsd:string">All (No Filter)</Value>
    </Parameter>
</Parameters>

Of course, you’ll need to replace the GUID value with the one for your team project.

Filtering by Team Project

Let’s look at the most deeply nested code.

CrossJoin(
                        [Work Item].[Microsoft_VSTS_Common_Priority].AllMembers,
                        StrToMember("[Team Project].[Team Project Hierarchy].&[{"+@ProjectGuid + "}]")
                    ),

The CrossJoin function creates a set of tuples using all possible combinations of the values from the dimensions you provide. In this case we’re combining all possible Priority values (including the All member) with a single member from the Team Project dimension.

The StrToMember function takes a string as input and attempts to find a matching member. This method won’t fail if the member doesn’t exist, although you can set a flag telling it to fail if the member doesn’t exist. Here we’re using this to construct the unique name for our team project based on the GUID retrieved from the ProjectGuid parameter. You’ll see another function called StrToSet that will take a list of string values and turn them into a set of members instead of a single set.

The result of cross-joining all the priorities with the team project will be a set of tuples, with one tuple for each priority member.

Some of these tuples may not actually be used by the team project, which is where the next layer comes into play.

NonEmpty(
                    CrossJoin(
                        [Work Item].[Microsoft_VSTS_Common_Priority].AllMembers,
                        StrToMember("[Team Project].[Team Project Hierarchy].&[{"+@ProjectGuid + "}]")
                    ),
                    [Measures].[Revision Count]
                )

The NonEmpty function will ignore tuples that are "empty." The second parameter to the NonEmpty function describes what we mean by empty, which is the revision count measure here.

The revision count measure looks at the revision count value for work items, which is incremented each time you save a work item. So if a priority has ever been used, the revision count measure for that priority will have an aggregate value. We don’t care what the value is, as long as it’s not null. Because we’re cross-joining with our team project, Analysis Services will aggregate the revision count only for work items in that team project. In other words, you’ll get a list that contains only priorities used at some point (anytime) in the team project, even if they’re not being used by the current work items.

Handling Missing Data

Moving out another level, the query starts to get more complicated in order to deal with scenarios where doesn’t have enough data. Here are the scenarios this query is designed to handle:

  • No Work Items: A new project won’t have any work items. We want this parameter query to always return at least the All member in this case.

  • Before Cube Update: It takes some time for data to reach the cube (by default, about two hours), but people often try to run the reports right after they’ve created a team project. We wanted to make sure the query would return the All member in this case.

Let’s back way out and look at the outer layers.

Except(
        Extract(
            Union(
                CrossJoin(
                    [Work Item].[Microsoft_VSTS_Common_Priority].[All],
                    [Team Project].[Team Project Hierarchy].[All]
                ),
                NonEmpty(
                    CrossJoin(
                        [Work Item].[Microsoft_VSTS_Common_Priority].AllMembers,
                        StrToMember("[Team Project].[Team Project Hierarchy].&[{"+@ProjectGuid + "}]")
                    ),
                    [Measures].[State Change Count]
                )
            ),
            [Work Item].[Microsoft_VSTS_Common_Priority]
        ),
        [Work Item].[Microsoft_VSTS_Common_Priority].[All].UnknownMember
    )

To explain what happens with the Extract function, let’s look again at the NonEmpty function. In this case it returns a set of tuples with a dimensionality of two—it returns pairs from priority and team project. However, we don’t really need the team project returned by this query. The Extract function takes a set of tuples and returns a new set that includes only the dimensions we want. In this case we’re asking the Extract function to return only priority, which throws away the team project (we don’t need to return it from this query, so that’s okay).

The first CrossJoin, which joins the All members for priority and team project, ensures that this query will always return at least the All priority member. So even if the team project has no work items, the query will return the All member. Recall that the NonEmpty function is filtering out values that have never been used in this team project, so it will return an empty list if no work items are found. Performing a union with the All member ensures that we’ll always have at least this one member.

As to why we had to cross-join, the Union operation requires that all sets being combined have the same dimensionality. So we had to cross-join the All priority with the All project so this tuple would have the same dimensionality as the set returned by the NonEmpty function below it.

Note

What happens if the All member is also returned by the rest of the query? If a member is returned by more than one part of a query, that member is included just once by the union function (or + operator, which is another way to perform a union).

Whew. We have just one more piece to go. The Except function allows us to remove a set of tuples from the results. Here we’re removing the UnknownMember member (no, that’s not redundant) from this list. This member showed up as Unknown, and it represents all work items where the Priority field either doesn’t exist (because it’s not defined for that work item type) or is set to blank.

This introduction to customizing Team Foundation Server reports is by no means complete. However, I hope it’s provided you with enough of a road map to get started. Here I’ve provided some further resources in case you want to move your knowledge and skill forward.

See Also

Other Resources

Creating Reports for Team Foundation Server 2010

http://mdxpert.com/ Here you’ll find on-line reference to MDX.

Microsoft SQL Server 2008 MDX Step by Step, Bryan Smith, et. al., Microsoft Press, 2009.

Creating, Customizing, and Managing Reports for Visual Studio ALM for TFS 2010 on MSDN

Multidimensional Expression (MDX) Reference for SQL Server 2008 on MSDN