Filtering TFS OLAP report by custom Iteration Path parameter


  • 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.

    • Edited by n_j Thursday, March 10, 2011 1:45 AM added more info
    Thursday, March 10, 2011 1:44 AM

All replies

  • 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.

    Tuesday, March 15, 2011 9:45 AM
  • 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
     parent.IterationGuid as ParentIterationGuid
    ,parent.IterationName as ParentIterationName
    ,parent.IterationPath as ParentIterationPath
    ,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
    Wednesday, May 04, 2011 4:01 PM