Customizing Report Parameters for Team Foundation Server 2010

Very often you’ll want to change the default settings for parameters in a report, but Reporting Services doesn’t have a user interface that allows you to select parameters, and then save them for use again. There is a way to customize the set of parameter values without learning how to author reports, but it’s a little involved. In this post, I’ll cover the following:

• Changing the default values that are used to render a report
• Creating multiple views on the same report, each with different default parameters

About Report Parameters

First a little bit of background on report parameters. Many of the TFS 2010 reports read from the Analysis Services database (or cube) that, by default, is called Tfs_Analysis (it was called TfsWarehouse in previous releases). Since this is a cube, and not a relational warehouse, default parameter values will look a little odd. As an example, this is what the default value might look like when you want to select “Iteration 1” in the iteration parameter:

[Work Item].[Iteration Hierarchy].[Iteration1].&[10]&[11]

Yikes, you say, how are you supposed to figure that out?

I’m not going to get into the details of why this is so complicated. If you’re interested, I wrote an MSDN article for TFS 2008 that will give you some idea about how this works, although the details have changed since then.

But there is a tool you can use to get this information: SQL Server Management Studio, which you can install from the SQL Server DVD. Alternatively, you can use a free program called MDX Studio, which is what I’ll use here as an example.

Finding the Default Value

The first step is to launch MDX Studio and connect to the TFS cube. You might need to find out the server and database from your TFS administrator, and also have them grant you read access (they need to add you to the TfsWarehouseDataReader role).

Finding an Iteration Value

1. Launch MDX Studio, type the name of the Analysis Services machine into the Connect to Analysis Services dialog box, and then click on the Connect button. You should see a display something like this:
   
2. In the Metadata section, scroll down so you can see Work Item and expand that item.
3. Expand the Work Item.Iteration Hierarchy node.
4. Expand the (All) and then All nodes, and then the collection node that contains your team project.
5. Expand the node that contains your team project. You should see something like this:
   
6. Finally, drag the iteration node (Iteration 1 in this example) into the Query area:
   

You can now copy this “ID” and use it in the following steps.

Setting Parameter Defaults

You’ll be using Report Manager to make these changes:

1. Right click on the Reports node of your team project inside Visual Studio and select Show Report Site…:
    
2. Click on the Properties tab at the top of the report:
    
3. Click on the Parameters link:
   

At this point you’ll see a list of parameters, which should look something like this:

To change the default iteration, for example, you would paste the value you got from the previous step into the text box circled in red above, and then click on the Apply button.

Multiple Parameter Settings – Linked Reports

There are times where you’ll want to have several versions of the report, each with different parameter values. Reporting Services has a feature called Linked Reports that allows you to essentially create sets of parameters that will be applied to a single copy of the report, but appear as a new report.

Here is how you create a linked report:

1. Click on the Properties tab for the report (just like in the previous section)
2. Click on the Create Linked Report button on the Properties page
3. Type in the name you want to use for this new linked report, and then click on the OK button
4. Click on the Properties tab of this new linked report
5. Click on the Parameters link on the left side to customize the parameter values that will be used for the linked report, and click on the Apply button to accept the new default values

Upgrading Team Foundation Server 2008 Reports to 2010, Part II

My previous post, Upgrading Team Foundation Server 2008 Reports to 2010, provides a lot of information on how to upgrade reports to work with the new 2010 version of Team Foundation Server, and even though I wrote it for Beta 1, much of the information still stands. There are, however, changes that have been made since then, so in this post I’ll provide updated information that applies to Beta 2 (and hopefully the final version of 2010).

Cube Changes

There were a few breaking changes to the cube that affected our reports, which are described in the sections below. You can find more details on cube changes in Sunder’s blog post: Team Foundation Server 2010 - Relational Warehouse and Cube Schema Changes.

Measure Changes

In previous versions of the cube, there were two measure groups for work items: Work Item History and Current Work Item. Here is one example of a measure pair from those two groups:

[Measures].[Cumulative Count]
[Measures].[Current Work Item Count]

Having two measures was always a little confusing. Starting in Beta 2, there is just one measure and one measure group. The measure group is now called Work Item, and there is a single measure that is used for both current and historical:

[Measures].[Work Item Count]

Now this measure will show historical information if you have the Date dimension included in your query, otherwise it will show current information. Much simpler. The other work item measures also follow this new pattern, with a single rather than a pair of measures for each of the work item measures.

In general, any measure that had Cumulative at the start before, just drop the Cumulative. Likewise, if it had Current at the start of the name, just drop Current. The Count measure above is, of course, an exception. Here is another example of before and after:

Before: [Measures].[Cumulative Completed Work]
After:  [Measures].[Completed Work]

That being said, there are some cases where measures with Cumulative at the start now have Trend at the end of the name. For example, [Measures].[Result Count Trend]. In general, your best bet is to look at the cube and see what the measures are called now.

Area/Iteration Hierarchies

As I mentioned in the previous post, 2010 introduces team project collections (TPCs), which is a grouping of a set of team projects. Although TPCs were supported in beta 1, the TPC was not included in the area and iteration hierarchies. So if you had two projects with the same name, but in different TPCs, you couldn’t tell which areas/iterations belonged to a specific project.

To fix this, we added the TPC name as part of the area/iteration hierarchy between the root and the top-level node for the team project. As a result, we had to change all the queries that we use to get the area and iteration parameters.

In this process of doing this, however, we also realized we could switch to a single query for each parameter instead of the two queries we had before. These new queries look a lot more complicated, but they’re actually not that bad. A bit of of explanation is in order because we also change the default value that we’re using so we can hard-code it rather than using a query. Doing this allowed us to provide a better experience when the cube doesn’t contain any areas or iterations for the team project, which will happen, for example, with a new team project that has no work items.

Here are the new default values:

Parameter	Default Value
AreaParam	[Work Item].[Area Hierarchy].[All]
IterationParam	[Work Item].[Iteration Hierarchy].[All]

 
What we’re trying to do is return query results that look something like this:

ParameterCaption	ParameterValue
All (No Filter)	[Work Item].[Area Hierarchy].[All]
(ProcessTest)	[Work Item].[Area Hierarchy].&[67]&[67]
Iteration 1	[Work Item].[Area Hierarchy].&[67]&[78]

 
So how do we get this? Using a few tricks:

• The All member is always returned. We use a Union function to ensure that we always return this member.
• The NonEmpty/CrossJoin near the end of this query returns all the members at the Area1 level and below. Area0 is the level of the team project, and there is a level above this for the TPC. By going directly to Area1 and all children of that level, we’re getting all the children of the project. We also cross join with the team project to ensure we only get areas for the current team project.
• The reason for the Except/Extract function is much less obvious, but it has to do with the outer Descendants function. Basically, we want to get all the first children of the project, and then all children of these members. In order for this to work when there are no matches (in which case the NonEmpty returns no members), we Union the results with the All member, but then we need to remove that member and also return only members in the Work Item dimension. That is what the combination of Extract and then Except accomplishes.

dsArea

WITH
    MEMBER [Measures].[Add Spaces] AS
        VBA.Space(([Work Item].[Area Hierarchy].CurrentMember.Level.Ordinal - 1) * 2)
    MEMBER [Measures].[ParameterCaption] AS
        IIF([Work Item].[Area Hierarchy].CurrentMember
               IS [Work Item].[Area Hierarchy].[All],
            @AllNoFilter,
            [Measures].[Add Spaces]
                + [Work Item].[Area Hierarchy].CurrentMember.Member_Caption
        )
    MEMBER [Measures].[ParameterValue]
        AS [Work Item].[Area Hierarchy].CurrentMember.UniqueName
    MEMBER [Measures].[ParameterLevel]
        AS [Work Item].[Area Hierarchy].CurrentMember.Level.Ordinal
SELECT
{
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
} ON COLUMNS,
{
    Union(
        [Work Item].[Area Hierarchy].[All],
        Descendants(
            //
            // We use Except to remove the extra [All] member so we get the descendants
            // only of the team project.
            //
            Except(
                Extract(
                    Union(
                        //
                        // We add [All] so Extract always has at least one tuple to
                        // work with. Otherwise it produces an error when there are no
                        // tuples returned by the NonEmpty below.
                        //
                        CrossJoin(
                            [Team Project].[Project Node GUID].[All],
                            [Work Item].[Area Hierarchy].[All]
                        ),
                        NonEmpty(
                            CrossJoin(
                                StrToMember("[Team Project].[Project Node GUID].&[{"
                                    + @ProjectGuid + "}]"),
                                [Work Item].[Area Hierarchy].[Area1]
                            ),
                            [Measures].[Work Item Count]
                        )
                    ),
                    [Work Item].[Area Hierarchy]
                ),
                [Work Item].[Area Hierarchy].[All]
            )
        )
    )
} ON ROWS
FROM [Team System]

dsIteration

WITH
    MEMBER [Measures].[Add Spaces] AS
        VBA.Space(([Work Item].[Iteration Hierarchy].CurrentMember.Level.Ordinal - 1) * 2)
    MEMBER [Measures].[ParameterCaption] AS
        IIF([Work Item].[Iteration Hierarchy].CurrentMember
                IS [Work Item].[Iteration Hierarchy].[All],
            @AllNoFilter,
            [Measures].[Add Spaces]
                + [Work Item].[Iteration Hierarchy].CurrentMember.Member_Caption
        )
    MEMBER [Measures].[ParameterValue]
        AS [Work Item].[Iteration Hierarchy].CurrentMember.UniqueName
    MEMBER [Measures].[ParameterLevel]
        AS [Work Item].[Iteration Hierarchy].CurrentMember.Level.Ordinal
SELECT
{
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
} ON COLUMNS,
{
    Union(
        [Work Item].[Iteration Hierarchy].[All],
        Descendants(
            //
            // We use Except to remove the extra [All] member so we get the descendants
            // only of the team project.
            //
            Except(
                Extract(
                    Union(
                        //
                        // We add [All] so Extract always has at least one tuple to
                        // work with. Otherwise it produces an error when there are no
                        // tuples returned by the NonEmpty below.
                        //
                        CrossJoin(
                            [Team Project].[Project Node GUID].[All],
                            [Work Item].[Iteration Hierarchy].[All]
                        ),
                        NonEmpty(
                            CrossJoin(
                                StrToMember("[Team Project].[Project Node GUID].&[{"
                                    + @ProjectGuid + "}]"),
                                [Work Item].[Iteration Hierarchy].[Iteration1]
                            ),
                            [Measures].[Work Item Count]
                        )
                    ),
                    [Work Item].[Iteration Hierarchy]
                ),
                [Work Item].[Iteration Hierarchy].[All]
            )
        )
    )
} ON ROWS
FROM [Team System]

Handling the Turkish “I”

It turns out that there are several different “i” letters in Turkish, and this creates problems with one of the queries that we had in Beta 1. You’ll need to change dsProjectGuid to use all caps for the GUID in column name:

SELECT ProjectNodeGUID FROM GetProjectNodeInfoFromReportFolder(@ReportPath)

This is to match the case of the actual column name as defined in the warehouse.