There are pieces of information spread out on this in various postings for SQL Server Reporting Services used in Team Foundation Server. Here are two:
I will attempt to pull together the essential information.
First, there are two ways that SSRS reports filter on iteration and area path:
- …MDXParam (i.e. Query Based) parameter. For example, the Stories Overview report.
- Not using …MDXParam (i.e. Query Based) parameter. For example, the Bug trends report
In both, you must first:
- Right-click the report drop-down (in SQL Server Reporting Services in a browser; for example from Home > TfsReports > Admin > MSF-Agile-v1
- Select Manage
- Click the Parameters tab on the left panel
When the MDXParam is used.
- Execute the following query from SQL Server Management Studio on the TFS_Warehouse table:
select IterationSK, IterationPath from DimIteration
- Sort out the records you don’t want
- Enter the IterationSK values for the path or paths you want to pre-select into the parameter (i.e. IterationParam) default value field. One number per line.
Make sure to hit the Apply button to save your changes before attempting to view the revised report.
Non Query Based
When the MDXParam is NOT used.
- Download and install the MDX Studio (I’m currently using v0.4.10.0 Beta version)
- Run MDX Studio and connect to the TFS SQL Server instance
- Expand Work Item > Work Item.Iteration Hierarchy > Team Project Collection > [your collection] > [your team project]
- Select the desired node and drag it to the query window.
- Copy the record or records in the query window you want to pre-select into the parameter (i.e. IterationParam) default value field. One record per line.
Again, make sure to hit the Apply button to save your changes before attempting to view the revised report.