blogger counters

Thursday, October 22, 2009

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:
    MDXStudio
  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:
    Iteration1
  6. Finally, drag the iteration node (Iteration 1 in this example) into the Query area:
    IterationMDX

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…:
    ShowReportSite 
  2. Click on the Properties tab at the top of the report:
     PropertiesButton
  3. Click on the Parameters link:
    ParametersLink

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

Parameters

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

9 Comments:

At 2/17/10, 12:23 PM, Blogger Shane said...

I downloaded:
Microsoft® Visual Studio® 2010 and Team Foundation Server® 2010 Beta 2for Microsoft® Virtual PC 2007 SP1 Image

When I go into the default installation of TFS project site in Sharepoint
(http://vs2010beta2/sites/DefaultCollection/IBuySpy/Dashboards/ProjectDashboard_wss.aspx)

I get this error on the webparts.

There are no accessible team projects in this Team Project Collection. Contact your Team Foundation Server administrator.

In the Team Server Admin Console - Team Project Collection - Team Projects tab the projects are there. This is the default installation. Any suggestions???

 
At 8/23/10, 1:52 PM, Anonymous Anonymous said...

Unbelievable... that's crazy. I've been trying to figure that out for some time now with no luck. I wouldn't have figured that out on my own - thanks so much for your post!

 
At 9/28/10, 9:32 AM, Blogger Tim said...

There are several other posts that explain this, but yours was the one that made sense to me. I wonder when Microsoft will make this easier...

 
At 10/6/10, 8:36 AM, Blogger Gary said...

This should simply be a dropdown list in the properties page rather than having to manually enter this info. Plus, if you're doing scrum in short cycles (2-3 weeks) that means you have to do this every 2-3 weeks for EVERY report you're going to use... a major waste of time!

 
At 3/14/11, 1:56 PM, Blogger Mahesh said...

I followed the steps to customise the reports in tfs reports section as suggested, but it does not work.

I have a collection say A. Within the collection i have multiple projects. One of these projects has the following hirearchy, please guide.

6 \B \A\B
1 \B\Backlog \A\B
148 \B\Iteration1 \A\B
150 \B\Iteration2 \A\B
149 \B\Iteration3 \A\B

If I have to go to Iteration 1, i tired with folloiwng parameters, which never worked. I dd not change any thing else:

[Work Item].[Iteration Hierarchy].[Iteration1].&[6]&[148]

In reports section of the portal, when i change the parameter as above, the Area Parametere and the State parameter goes blank when I apply the changes. On the View page the Area parameter is editable bu the state parameter is not editable due to which the rports do not run. The reports are SQLreports and do not use OLAP.

Please guide.

Thanks

 
At 4/21/11, 8:29 AM, Blogger Glenn Hurley said...

Big help. Thanks for the post.

 
At 5/27/11, 4:13 AM, Blogger John Miles said...

I would suggest a slightly easier way to achieve this is to create a subscription to the report (e.g. the burndown) with your iteration parameters.

Have this subscription deliver an MHTML file to a shared folder and then modify your page viewer web part to view this file.

Using the subscription editor within Reporting Services you get a check list box drop down of iterations which you can select from, which makes life easier.

Just set the subscription interval to whatever refresh frequency you need.

Regards,
John

 
At 9/19/11, 9:16 PM, Blogger vgaltes said...

Hi John,

It's possible to obtain these values with an MDX query? For example, it's possible to obtain the area identificator given an area path like \Tailspin toys\Web development?

Thank you very much,

 
At 5/17/12, 8:42 AM, Blogger Unknown said...

You can also get these values by opening the burndown, setting it as you want, and then clicking the "Export to data feed" orange button in the middle of the page next to the print button. Then you just have to just parse the values out if you open the data feed in something like notepad.

 

Post a Comment

Links to this post:

Create a Link

<< Home