TFS Report Issues with SQL Server 2008

Some of the reports that shipped with Team Foundation Server 2005 and 2008 run into problems when you attempt to run them on SQL Server 2008 Reporting Services. Here is an overview of of the problems and fixes for those problems. Most of these problems are a result of changes in how Analysis Services processes queries in 2008 vs 2005.

Complement and Except Errors

Several of the TFS reports show one of these errors when running under SQL 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 reports that are affected are Scenario Details and Unplanned Work.

What does this mean? Basically, it means that the WHERE clause in the query is using the "-" operator to exclude a specific attribute from the query. For example, the Scenario Details reports includes the following inside the WHERE clause:

,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 we want to filter out work items that are in the Proposed state. However, this doesn't work in SQL 2008. You'll notice that we're trying to exclude an a specific value without mentioning the other values.

The solution is to remove the exclusion from the WHERE clause and put it into the body of the query:

,STRTOSET( @AreaParam )

,STRTOSET( @IterationParam )

,EXCEPT(

    Descendants([Work Item].[System_State].[System_State])

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

)

 

The key here is that we need to include all states except for the Proposed state, which is what the Except function above does.

WHERE vs Sub Cube

Many of the reports were written using the WHERE clause to filter the results. For example, so the results would be filtered on a single team project instead of the entire cube. However, the behavior of the WHERE clause changed somewhat in SQL Server Analysis Services 2008. The simplest solution is to rewrite the WHERE clause as a sub-cube expression instead. Not only will this fix some of the problems, but it should also make reports run faster.

Old Version:

FROM [Team System]

WHERE

(

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

  STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")

)

New Version:

FROM

(

  SELECT

    CrossJoin(

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

      STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")

    ) ON COLUMNS

  FROM [Team System]

)

 

The WHERE clause above is applied after the rest of the MDX expression has been evaluated. But 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're restricting the set of cells to a smaller set before evaluating the query.

Extract Problems

Here is the error message you might see:

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

That's certainly not a very helpful error message. This message appears in the report called Bugs Found Without Corresponding Tests. The solution, as it turns out, is to use a sub-cube instead of a WHERE clause (described above).

Tuple Problem

In several of the reports, selecting all of the iterations produces the following error message:

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

More than 4,294,967,296 tuples

This error occurs with the following 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

The solution to this problem is also to replace the WHERE clause at the end of the MDX query with a sub-cube expression, as described above.

SQL Reports and the IN Clause

The Load Test Summary report is a SQL report, and it contains an IN clause in the WHERE statement. This is used to allow multiple values to be selected and passed to the query. However, a change in behavior in SQL 2008 Reporting Services means that you'll see the following message when you have no values:

Incorrect syntax near ')'.

Here is why you see this message. The WHERE clause in the Load Test Summary report looks like this:

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

When there are no results passed into this query, Reporting Services 2008 simply removes @ResultParam, so you get something like "IN ()", and you get a syntax error, preventing this query from running.

The solution is to add an expression to the @ResultParam query parameter. This expression is evaluated in order to determine what is passed to the query. Here is what works:

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