Filtering TFS OLAP report by custom Iteration Path parameter

Question

I am building a report that is primarly based on OLAP test data in the TFS2010 Team cube.  The key filtering criteria is by Iteration Path.

My iteration path structure is as follows:

Project Root
   - Release #
       - Work Stream
            - Sprint
                 - Team

Based on user requirements to have the ability to select each of these from seperate drop down boxes, I construct a string paramater @pIterationPath which looks like this \Project Root\Release\WorkStream\Sprint\Tream.

I am trying to adapt a report included with the MS Agile TFS Process Template "Test Plan Progress"  to use this custom iteration path as a filter instead of the default.

Is this possible?  If so, please include code samples.  Thank You.

Answer 1

Hi n_j,

Thank you for your question.

I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
 
Thank you for your understanding and support.

---

Cathy Kong [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi

If you use the Iteration (and Area) paths from the OLAP cube, the underlying values are actually integers.

What I did to use default reports (that use these integer values from the OLAP Cube) is create a query that creates these OLAP values from the relational dtabase and show the iteration(or sprint) information in a dropdown

SELECT 
 parent.IterationGuid as ParentIterationGuid
,parent.IterationName as ParentIterationName
,parent.IterationPath as ParentIterationPath
,i.IterationGuid
,i.IterationName 
,i.IterationPath
,Iteration13GUIDInt1 --Always the last one to make sure it is right
,Iteration13GUIDInt2 --Always the last one to make sure it is right
,Microsoft_VSTS_Scheduling_StartDate as SprintStartDate
,Microsoft_VSTS_Scheduling_FinishDate as SprintEndDate
,'[Work Item].[Iteration Hierarchy].[Iteration'	+ Convert(varchar(255),i.Depth) + 	'].&['+ Convert(varchar(255),Iteration13GUIDInt1) 	+']&['+ Convert(varchar(255),Iteration13GUIDInt2) +']' as IterationCubeParameter
,'[Work Item].[Area Hierarchy].[All]' as AreaCubeParameter
,'[Work Item].[Iteration Path].&[' + i.IterationPath + ']' as IterationPathParameter
FROM Tfs_Warehouse.dbo.DimIteration i
INNER JOIN Tfs_Warehouse.dbo.vDimIterationOverlay ito ON ito.IterationSk = i.IterationSK
INNER JOIN Tfs_Warehouse.dbo.CurrentWorkItemView cv ON cv.IterationGUID = i.IterationGUID
INNER JOIN Tfs_Warehouse.dbo.DimIteration parent ON parent.IterationGUID = i.ParentIterationGUID
WHERE System_WorkItemType = 'Sprint'

The WHERE clause filters on work item type sprint but you can also remove this.

The IterationCubeParameter shows the value that you would get if you used the OLAP query

Good luck!

---

Rene van Osnabrugge T: @renevo