Customizing Report Parameters – SQL Reports

In a previous post I described how to customize the parameters in reports that ship with TFS 2010. That post, however, applies to most, but not all of the reports. Most of the reports use the cube, but some of the reports use the warehouse instead, which means two things:

• The queries are written in SQL instead of MDX
• The parameter values will be integer ID values instead of the long MDX names.

In this post I’ll describe how to get the values you need for areas and iterations in your team project so you can customize your reports.

First, here is an easy way to identify that a report uses the warehouse instead of the cube.

Determining if a report uses the warehouse

The instructions here are for SQL Server 2008. The process is slightly different if you’re using SQL Server 2008 R2.

1. Open Report Manager (right click the Reports node in the Team Explorer in Visual Studio and click Show Report Site…)
2. Navigate to and click your report
3. Click the Properties tab
4. Click the Parameters link

At this point you’ll see the list of parameters. The report will be a SQL report if you see a “*” for the default value of IterationParam or AreaParam, as shown here:

The single character “*” in the parameter means that it should not filter, which is displayed as All (No Filter) in the parameter bar.

Finding the Area/Iteration ID

Now that you’ve identified that a report uses the warehouse instead of the cube, you’ll need to find the ID value to use for the area/iteration. This will require some knowledge of the TSQL query language and a tool like SQL Server Management Studio (SSMS) that you can use to run queries.

1. Open SSMS and connect to the SQL Server machine that hosts the warehouse
2. Expand the Databases folder in Object Explorer and click Tfs_Warehouse (this is the default name, but your TFS administrator might have chosen a different name)
3. Click the New Query button

You can then use the following query to find the iteration paths and their corresponding ID values for your team project (just substitute the name of your team project for ‘project name’ in the query):

SELECT IterationSK, IterationPath, ProjectPath, ProjectNodeName
  FROM DimIteration a
INNER JOIN DimTeamProject tp
  ON a.ProjectGUID = CAST(tp.ProjectNodeGUID AS NVARCHAR(256))
 WHERE ProjectNodeName = 'project name'
ORDER BY IterationPath

This query will produce output that looks something like this:

Simply find your iteration path in the second column, and then use the value in the first column.

Note: I included the ProjectPath to handle the case where you have the same project name used in different Team Project Collections. When this happens, you’ll need to make sure you use areas/iterations from the project path that matches your project.

To find areas instead of iterations, simply change “Iteration” to “Area” wherever it appears in the query.

You can now copy this number into the parameter text box in Report Manager.