Modifying Pre-Upgrade Reports

To view a pre-upgrade report defined for your upgraded team project, you may need to modify the report. A pre-upgrade report is a report that you customized using Visual Studio 2005 Team Foundation Server or Team System 2008 Team Foundation Server. Required modifications are a result of changes introduced with the way queries are processed by SQL Server 2008 Analysis Services. To use a pre-upgrade report and connect it to the Analysis Services database for Team Foundation Server 2010, you will need to update the report to address the changes made to the schema.

This topic describes how to resolve error messages that appear when running a pre-upgrade report. It also describes the modifications that you will need to make to update a customized pre-upgrade report to interface with the latest data warehouse schema. Prior to updating a pre-upgrade report, you should review the changes that have been introduced to the schema for Team Foundation Server 2010. For more information, see Changes and Additions to the Schema for the Analysis Services Cube.

In this topic

• Correcting Errors that Occur When Viewing Pre-Upgrade Reports

• Updating Pre-Upgrade Reports to Connect to the New Data Warehouse

• Tip for Modifying Queries When Working with Report Designer 2.0

Correcting Errors that Occur When Viewing Pre-Upgrade Reports

Changes made to the way SQL Server 2008 Analysis Services processes queries may cause errors to appear when you view a pre-upgrade report. You can resolve these messages by taking the corrective actions indicated in the following sections:

• Resolving Error Messages That Reference the Complement Operator or the Except Function

• Resolving Error Messages That Reference the Extract Function

• Resolving Error Messages That Reference Tuples

• Resolving Incorrect Syntax Errors Involving an IN Clause

• Rewriting a WHERE Clause to Use a Sub-Cube Expression

Resolving Error Messages That Reference the Complement Operator or the Except Function

Several Team Foundation pre-upgrade reports, and specifically the Scenario Details and Unplanned Work reports, show one of the following errors when run on SQL Server 2008:

The set must have a single hierarchy to be used with the complement operator.

Two sets specified in the EXCEPT function have different dimensionality.

The above messages appear because the WHERE clause in the report query is using a minus or complement operator (-) to exclude a specific attribute from the query. For example, the Scenario Details report includes a WHERE clause with the following syntax:

,STRTOSET( @AreaParam )
,STRTOSET( @IterationParam )
,STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]")
,-{STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")}

The minus sign in front of the last line indicates that the query should remove work items that are in the Proposed state from the results that are returned. This type of syntax structure, which attempts to exclude a specific value without mentioning the other values, is not supported in SQL Server 2008.

To resolve this error, you must remove the exclusion from the WHERE clause and place it into the body of the query. As shown in the following syntax, the EXCEPT function specifies to include all states except for the Proposed state:

,STRTOSET( @AreaParam )
,STRTOSET( @IterationParam )
,EXCEPT(
    Descendants([Work Item].[System_State].[System_State])
    ,STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")
)

Resolving Error Messages that Reference the Extract Function

The following error message may appear when you view the Bugs Found Without Corresponding Tests pre-upgrade report:

The Extract function was called with hierarchy 'System_Id' that does not exist in the set.

To resolve this error, you must replace the WHERE clause with a sub-cube expression, as described in Rewriting a WHERE Clause to be a Sub-Cube Expression later in this topic.

Resolving Error Messages That Reference Tuples

When you view a pre-upgrade report and select to view all iterations, the following error message may appear:

The function expects a tuple expression for the argument. A tuple set expression was used.

More than 4,294,967,296 tuples

The above error message may appear when you view one of the following pre-upgrade reports:

• Bugs by Priority

• Bugs Found Without Corresponding Tests

• Issues and Blocked Work Items

• Related Work Items

• Requirement Details

• Requirements Test History and Overview

• Scenario Details

• Triage

• Unplanned Work

To resolve this error, you must replace the WHERE clause at the end of the MDX query with a sub-cube expression. For more information, see Rewriting a WHERE Clause to be a Sub-Cube Expression later in this topic.

Resolving Incorrect Syntax Errors that Involve an IN Clause

When you view the Load Test Summary pre-upgrade report and there are no values to report, the following error message may appear:

Incorrect syntax near ')'.

The Load Test Summary report contains an IN clause in the WHERE statement, which is used to allow multiple values to be selected and passed to a query. For example:

WHERE (Result.__ID IN (@ResultParam)) 
AND ([Test Result].[Result Record Count] = 1)

You receive a syntax error because the @ResultParam is removed in SQL Server 2008 when there are no results passed to the query. The resulting value becomes something similar to IN (). 

To resolve this error, you must add an expression to the @ResultParam query parameter. For example, you can replace the previous expression with the following expression:

=IIF(Parameters!ResultParam.Count > 0, Parameters!ResultParam.Value, "")

Rewriting a WHERE Clause to be a Sub-Cube Expression

Many pre-upgrade Team Foundation reports use a WHERE clause to filter a query. In the following example, a WHERE clause is used to filter the results for a single team project instead of the entire cube. Because the behavior of the WHERE clause changed in SQL Server 2008, you need to rewrite the WHERE clause as a sub-cube expression. Making this change will not only resolve many errors, but it will also improve the performance of rendering the report.

Pre-upgrade Report Expression that Uses a WHERE Clause to Filter Results for a Team Project

In the following expression, the WHERE clause is applied after the rest of the MDX expression has been evaluated.

FROM [Team System]
WHERE
(
   STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),
   STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")
)

Improved Sub-cube Expression that Filters Results for a Team Project

In the following expression, the second expression defines a subset of the cube that will be used to evaluate the rest of the expression. In other words, using a sub-cube, you restrict the set of cells to a smaller set before evaluating the query.

FROM
(
   SELECT
   CrossJoin(
     STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),
     STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")
    ) ON COLUMNS
   FROM [Team System]
)

Updating Pre-Upgrade Reports to Interface with the Current Schema

You must make a number of modifications to a customized pre-upgrade report in order to continue to use it with your upgraded team project. The details of these changes are described in the following sections:

• Update Reports to Connect to the New Data Warehouse

• Replace References to the Team Project to Use the Project GUID

• Replace Expressions that Use the Complement Operation

• Replace References to Measure Groups that Have Changed

• Replace References to Measures that Have Been Renamed

• Replace References to Dimensions that Have Been Renamed or Removed

• Replace Parameters that Reference Area and Iteration Hierarchies

• Update Test Result Reports that Use Product Hierarchies

• Update Test Result Reports that Use Product Hierarchies

Updates to be Made to Reports that Reference the Analysis Services Cube

To update a pre-upgrade MDX report to connect to the Analysis Services Cube for Team Foundation Server 2010, you must make the following modifications:

• Update the report to use the new cube resource name, which is Tfs2010OlapReportsDS.

• Update the report to reference the team project GUID. This change requires that you add some new parameters and a new data set.

• Replace WHERE clauses with sub-cube expressions.

• Update or replace queries that reference area or iteration parameters. The schema for these parameters has changed.

• Rename or replace measure groups, measures, dimensions, or attributes whose names have changed. For more information, see Changes and Additions to the Schema for the Analysis Services Cube.

Updates to be Made to Reports that Reference the Relational Database

To update a pre-upgrade report to connect to the relational database for Team Foundation Server 2010, you must make the following modifications:

• Update the report to use the new relational database resource name, which is Tfs2010ReportsDS.

• Update the report to reference the team project GUID.

• Use the new public views when possible.

• Make sure you use joins with unique keys. For example, work item IDs are no longer unique within the warehouse.

• Rename all table references because all table names have changed.

For more information, see What's New for Reporting for Visual Studio ALM.

Update Reports to Connect to the New Data Warehouse

You need to modify pre-upgrade reports to connect to the new data warehouse resources. The following table lists the names that you should use to connect the report to the new Reporting Services data resources.

Database resource	Name assigned to resources for Team Foundation Server 2010
Relational database	Tfs2010ReportsDS
Analysis Services cube	Tfs2010OlapReportsDS

Replace References to the Team Project to Use the Project GUID

You need to use the GUID for a team project, and not just the project name as a filter. In Team Foundation Server 2010, team project names must be unique across a team project collection, but not a Team Foundation deployment. In earlier versions, you could obtain the default project name from the report path and use that to filter query results. Now, you must use the GUID for the team project.

Replace all project filter instances within the report query. For example, replace the following syntax:

STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

with the following syntax that references the team project GUID:

STRTOMEMBER("[Team Project].[Project Node GUID].[{" + @ProjectGuid + "}]"),

To determine the GUID for a team project

1. Open SQL Server Management Studio and connect to the instance of SQL Server for the data warehouse.

2. In Object Explorer, expand the Databases node, right-click Tfs_Warehouse or the custom name that was defined for your data warehouse, and then click New Query.

3. Type the following query and then click Execute:

   SELECT ProjectNodeName, ProjectNodeGUID, ProjectPath
   FROM DimTeamProject
   ORDER BY ProjectPath
   

4. In the Results tab, locate your team project under ProjectNodeName, right-click the GUID under ProjectNodeGUID, and click Copy to copy the GUID to the Clipboard.

Replace Expressions that Use the Complement Operation

You must replace the complement operator (-), which performs a similar function as the EXCEPT function in MDX, because queries that use the complement operation inside a WHERE clause generate an error in SQL Server 2008. For example, replace the following syntax:

,STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),-{STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")}

with syntax that excludes a value from a list of values, similar to the following syntax:

EXCEPT(    Descendants([Work Item].[System_State].[System_State])    ,STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]"))

Replace References to Measure Groups that Have Changed

The measure groups in previous versions of the Analysis Services cube, Work Item History and Current Work Item, have been replaced with a single measure group, Work Item. Here is one example of a measure pair from the previous two groups:

[Measures].[Cumulative Count]

[Measures].[Current Work Item Count]

In the current version of the Analysis Services cube, a single measure is used for both current and historical information:

[Measures].[Work Item Count]

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

Replace References to Dimensions that Have Been Renamed or Removed

Several dimensions have been renamed or removed. For more information, see Changes and Additions to the Schema for the Analysis Services Cube.

Replace References to Measures that Have Been Renamed

The names of measures that were pre-pended with the label Cumulative or Current in previous versions of the cube have been changed. These labels have been dropped. For example:

Previous version: [Measures].[Cumulative Completed Work]

Current version [Measures].[Completed Work]

In some cases, the pre-pended label of Cumulative has been replaced with the label of Trend appended to the name. For example:

Previous version: [Measures].[Cumulative Result Count]

Current version: [Measures].[Result Count Trend]

You should review the current version of the cube to determine the current measure names. For more information, see Generating Reports Using the Analysis Services Cube.

Replace Parameters that Reference Area and Iteration Hierarchies

In earlier schema versions, Area and Iteration were defined as dimensions within the cube. A parent-child hierarchy was used that allowed you to use the project name as a key to generate the list of available areas and iterations for a team project, as well as the default values.

In the current schema, however, area and iteration hierarchies are defined as attributes within the Test Case and Work Item dimensions. In the new structure, team project names are not guaranteed to be unique, so you cannot use the project name as a key as the report may return results for more than one team project. In addition, a natural hierarchy replaces the parent-child hierarchy. The new hierarchies have the equivalent built into the hierarchy so that queries do not need to check for DATAMEMBER values and format them differently.

These changes require that you modify all queries in pre-upgrade reports that reference area and iteration parameters. You want to restructure your queries to return the default area or iteration based on the project GUID by looking only at the direct children of the root. The default values for these parameters are as follows:

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

You can use the following code examples to return a single member of the default area or iteration based on the project GUID by filtering for the direct children of the root. By limiting the scope to just the children of the root, you improve the performance of rendering the query. Once you have the default area or iteration, you can then apply a further filter that is scoped to the default area or iteration.

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]

Note the following:

• The All member is always returned. You can use a Union function to ensure that you always return this member.

• The NonEmpty/CrossJoin function near the end of dsAreaDefault returns all the members at Area1 level and below. Area0 represents the level of the team project, and there is a level above this for the team project collection. By filtering directly on Area1 and all children of that level, you obtain all the children of the project. You can also cross join with the team project to ensure you only get areas for the current team project.

• You can use the Except and Extract functions to support the outer Descendants function. Basically, you want to return all the first children of the project, and then all children of these members. When there are no matches, the NonEmpty function returns no members, and you must request a Union of the results with the All member. Then you 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.

Update Test Result Reports that Use Product Hierarchies

If you have any pre-upgrade reports that use the area or iteration path hierarchies in the Test Result dimension, you will need to update these reports. You can use the Area Hierarchy from the Test Case dimension and Iteration Hierarchy from the Test Plan dimension to filter test result reports.

Use the Area Hierarchy for the Test Case Dimension

In the current version of the schema and for Visual Studio ALM, you can associate test cases with product areas by assigning the area path. Since the test case defines the area that is being tested, it is appropriate to use the Area Hierarchy for the test case to filter the test results.

Another option for you to use is the Area Hierarchy for the Test Plan dimension. However, test plans often cover multiple product areas, so this may not be the best choice.

You may see test results with an area that is listed as Unknown. This indicates that the test result is not associated with any area, which occurs when the test case associated with the unit test results were not assigned to an area path.

Use the Iteration Hierarchy for the Test Plan Dimension

You can use the Iteration Hierarchy from the Test Plan dimension to filter test results by iteration. Because test plans are usually tied to a specific iteration, filtering test results by the test plan iteration is useful.

Tip for Modifying Queries When Working with Report Designer 2.0

The query designer in Report Designer 2.0 does not apply color formatting to query syntax, a function that was available in Report Designer 1.0. You may find that it is more difficult to modify queries without the color formatting. You can use the following approach to work around this issue.

1. In Report Designer 2.0, in the Dataset Properties window, copy the contents of the Query text box. Note that this query does not include line breaks.

2. Paste the query into a session of SQL Server Management Studio, which does apply color formatting to query syntax.

3. Modify the query.

4. When you have made all your modifications, copy the modified query and paste it into the Query Designer dialog box in Report Designer 2.0.

See Also

Concepts

Changes and Additions to the Schema for the Analysis Services Cube

Locating Reports After the Upgrade to Team Foundation Server 2010

What's New for Reporting for Visual Studio ALM

Updating an Upgraded Team Project to Access New Features