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:
- Release #
- Work Stream
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.
- Edited by n_j Thursday, March 10, 2011 1:45 AM added more info
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.
HiIf 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 queryGood luck!
Rene van Osnabrugge T: @renevo
- Proposed as answer by renevoMVP Monday, October 01, 2012 9:48 AM