Austin Agile DevOps

DevOps in the Cloud
posts - 70 , comments - 7 , trackbacks - 0

How I Customized Report Parameters for Team Foundation Server 2010

My goal was to have the burndown chart on the team project portal main page display the current iteration.

The default report displays the tasks for the entire team project starting 5 days before the current date and ending 25 days after the current date. FRUSTRATING, to say the least! I mean, what a useless report! Who's Agile book did they get that one from?

What I wanted was to see the burndown for the current iteration. That is, tasks for that iteration only and that iteration starting on a fixed start date and stopping on a fixed finish date.

First, I tried to implement this by passing in the parameters in the web part link url. I talked to about 4 different Microsoft people at PDC; specialist in VS ALM, SSRS, SharePoint. No one knew how to do this. See my original article on this here.

I have yet to be successful on the first approach. Which seems the most obvious way to approach it. I hope that this secret makes its way into the TFS community at some point. No wonder so few use TFS SSRS reporting! Can you sense the frustration?

Then, I came across at article by John Socha-Leialoha on Customizing Report Parameters for Team Foundation Server 2010. This article describes how I used his approach to be successful.

  1. First, save a copy of the report definition so you can restore it if things get messed up.
  2. To access the report definition, from the team project main page, select Reports from the left column and then navigate through the SQl Server Reporting Services (SSRS) location hierarchy to find the report. Click on it to open it. Go to the Properties tab and select the General panel. Click Edit to save a copy of the report definition file. You will need to click the Update button to browse, select and then apply the file to restore the definition
  3. Now, to the point: select the Properties panel and set the iteration path on the report parameters page as John's blog specifies. Note: the parameter string for iteration path [Work Item].[Iteration Hierarchy].[All] must be changed to [Work Item].[Iteration Hierarchy].[Iteration1].&[{ParentIterationSK}]&[{IterationSK}]. I had to use the MS SQL Server Mgmgt Studio (SSMS) to find out what these were. I connected to my TFS server using the SSMS, opened the Tfs_Warehouse Database and queried the rows in the dbo.DimIteration table. From here you can see what the SK values are based on the IterationPath you are interested in. Note2: I don't know what "[Iteration1]" means for sure. It may have to do with the depth of the IterationPath Hierarchy. In my case, I'm only one level down from the root of the iteration path. So, in this case, I must use "[Iteration1]" regardless of the real iteration I want to report on. So, if the full parameter for my first iteration is: [Work Item].[Iteration Hierarchy].[Iteration1].&[14]&[17] and the SK for my second iteration is 18, my parameter for my project's second iteration looks like this: [Work Item].[Iteration Hierarchy].[Iteration1].&[14]&[18]
  4. Next, click the "Override Default" for StartDateParam and EndDateParam. Note: I think this is permanent for the active defintion. I think you can restore the original defintion to reset the default. Type in the start and end date you want (mm/dd/yyyy). The time will default to 12 AM, but you can change this after you have applied the first explicit date values.
  5. Now view and display the report. You should see the report you want :)
  6. Next, rename the report to reflect the iteration it's for
  7. Now create the linked reports as described by John in his blog
  8. Finally, you can easily modify the web part link url to containt the correct report name.

In subsequent work I confirmed the syntax of the IterationParm string. For example, take -

[Work Item].[Iteration Hierarchy].[Iteration1].&[14]&[18]

The [Iteration1] node does prescribe the depth of the hierarchy. [Iteration1] means root to 1st level. [Iteration2] means root to 1st level to 2nd level] and so forth. The node IDs correspond to the nodes at each level.

So [Work Item].[Iteration Hierarchy].[Iteration4].&[14]&[18]&[54]&[72] specifies an iteration path node at the root>1st>2nd>3rd level.

You can obtain the SSRS report properties IterationParm value using SQL Server Management Studio. Connect using SQL Server Analysis Services and create an MDX query of the work item.iteration hiearchy path node you want to report on. See this great article by John Socha-Leiloha for more details.

Good hunting!

Print | posted on Tuesday, May 15, 2012 8:46 AM | Filed Under [ Agile SCM Talk Blog ]


No comments posted yet.
Post A Comment

Powered by: