Team Foundation Server 2010 is a “server-side” version control system. Developers used to Subversion and other “client-side” systems are often frustrated in certain situations where they want to see only the changed items in the Pending Changes window and TFS shows all checked-out items. This happens when an entire directory structure is copied to the workspace and only some of the items have changed. The rest are identical to the existing items in the workspace.
The typical procedure taken is:
- Check-out the folder structure
- Copy the files somewhere else and change some items
- Copy the structure back to the workspace
At this point the Pending Changes windows shows all the items in the structure. The problem: the developer cannot confirm via the Pending Changes window which files have changed before check-in.
Solution: after the copy back to the workspace, do an Undo Pending Changes on the structure. TFS will prompt you asking if you want to undo the items that changed. Click “No.” Now bring up the Pending Changes window and only the files that have changed will be displayed. For additions and deletions use the TFS Power Tools PowerShell command “TFPT online.”
This type of situation can also arise when coming back online after being offline.
Visual Studio and Team Foundation Server (TFS) 2010 provided support for system testing. Test cases were added as a work item type. But test plans and suites are not work items and this greatly limits the visibility into the management of test artifacts. For example, there is no easy way out-of-the-box to find test cases that don’t belong to a test plan.
Here’s the SQL that creates a list of test cases providing the test case:
- ID
- Title
- State
- Assigned To
- Area Path
- Iteration Path
- Plan and Suite Path
You can use the output to identify what test cases are in what plan and suite; and more to the point, which test cases are not in a plan.
Using the Amrein SQL Viewer Web Part you can easily throw this up as a web part on the project portal site where users can globally access, sort and filter the data.
SELECT tco.System_Id as ID
,tco.System_Title as Title
,tco.System_State as State
,ISNULL(p.Name,'null') as Assigned
,tco.AreaPath as Area
,tco.IterationPath as Iteration
,ISNULL(s.SuitePath,'null') as 'Plan'
FROM Tfs_01Warehouse.dbo.vDimTestCaseOverlay tco inner join Tfs_01Warehouse.dbo.DimPerson p
ON tco.System_AssignedTo__PersonSK=p.PersonSK left outer join Tfs_{collection DB}.dbo.tbl_SuiteEntry se ON tco.System_Id=se.TestCaseId left outer join Tfs_01Warehouse.dbo.vDimTestSuiteOverlay s
ON se.SuiteId=s.TestSuiteID
WHERE tco.TeamProjectSK={#} /* team project */
ORDER BY tco.System_Id DESC
What a long title! Anyway, I didn't add LM until sometime after I had setup my Team Foundation Server 2010 instance. There are 2 important realities to face when you do this:
- The LM permissions in the process template are not applied because LM was configured when the team project was created
- There's no UI to set LM permissions (arrrgh)
All you have are the permissions established by the accounts used to setup LM (i.e. TfsSetup, etc...).
Here are the commands I developed to grant permissions to existing team projects after you enable LM for them.
Lookup TfsLabConfig in the MSDN library to get command line details.
Grant permissions for a team project group to have environment read, start, pause and stop permissions:
1. Grant read access for all lab management objects in the collection to all team project members:
TfsLabConfig permissions /collection:{your collection url} /group:"[{team project namel}]\Team Members" /allow:Read
2. Grant start, pause and stop permissions to project administrators, team leaders and contributors
TfsLabConfig permissions /collection:{your collection url} /group:"[{team project namel}]\{project group name}" /allow:Start,Pause,Stop
TfsLabConfig permissions /collection:{your collection url} /group:"[{collection name}]\{collection group name}" /allow:Start,Pause,Stop
NOTE: you can display permissions simply by omitting the /allow parameter
syntax highlighted by
Code2HTML, v. 0.9.1
The reality is that project managers need work status in real-time. The TFS Excel reports and web parts are great, but because they are sourced from the TFS SSAS cube the information can be up to 2 hours old.
The User Story goes like this...
"As a project manager, team lead or team member I want to see what work is currently active and what the burndown is by team and by person is for that work so I know when work delays or time reporting failures are taking place. I need to know this 30 seconds before the stand-up meeting every day and the information must be accurate in real time."
Here's how I created a burndown chart web part that's:
- Real time
- Is from a specific/fixed start to end date
- Can be by team or by individual
- Skips weekend days
First I developed T-SQL to give me:
- A query listing of sprints that have active work
- A query listing of team members that have active work
- A stored procedure that list burndown chart data as of start and end dates
Then I used the Amrein SQL Query Viewer Web Part (enterprise license for $200) to run the listing queries and display them on a TFS project portal page. As part of the query I assimilated the URL that calls the SharePoint 2010 Query String (URL) Filter web part, which I connected to the Amrein Google Chart Web Part (free). This used the chart data query to display the burndown chart from a common web part page. I also used the same query list for the chart data using the Query Viewer Web Part.
Here's the reports...
Web part page listing the Active Work. Each Team Project needs it's own page and the queries vary by area name.
A burndown chart is displayed in a new browser window by clicking on the "burndown" link.
Here's the code. I developed the T-SQL from SQL Server Management Studio.
Sprint Active Work Report
SELECT task.IterationName as Sprint
,CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101) as Start
,CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101) as [End]
,SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) as Remaining
,SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) as Completed
,SUM(task.Microsoft_VSTS_Scheduling_OriginalEstimate) as Estimate
,'http://[ your TFS SP server ]/sites/tfs/XXXX/SiteAssets/BurndownReport.aspx?start='
+ (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101))
+ '&end=' + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101))
+ '&iName=' + task.IterationName + '&aName=XXXUI-v1&assigned=' as Burndown
FROM Tfs_01Warehouse.dbo.CurrentWorkItemView task, Tfs_01Warehouse.dbo.CurrentWorkItemView sprint
WHERE task.ProjectPath LIKE '\XXXX%'
AND task.System_WorkItemType = 'Task'
AND task.AreaName = 'XXXUI-v1'
AND task.System_AssignedTo > ' '
AND task.System_State <> 'Closed'
AND sprint.System_WorkItemType = 'Sprint'
AND sprint.IterationPath = task.IterationPath
GROUP BY task.IterationName, sprint.Microsoft_VSTS_Scheduling_StartDate,
sprint.Microsoft_VSTS_Scheduling_FinishDate
HAVING (SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) > 0
OR SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) >0)
ORDER BY task.IterationName DESC
Team Member Active Work Report
SELECT task.System_AssignedTo as Assigned
,SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) as Remaining
,SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) as Completed
,SUM(task.Microsoft_VSTS_Scheduling_OriginalEstimate) as Estimate
,task.IterationName as Sprint
,'http://[ your TFS SP server ]/sites/tfs/XXXX/SiteAssets/BurndownReport.aspx?start='
+ (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101))
+ '&end=' + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101))
+ '&iName=' + task.IterationName + '&aName=XXXUI-v1&assigned=' + task.System_AssignedTo as Burndown
FROM Tfs_01Warehouse.dbo.CurrentWorkItemView task, Tfs_01Warehouse.dbo.CurrentWorkItemView sprint
WHERE task.ProjectPath LIKE '\XXXX%'
AND task.System_WorkItemType = 'Task'
AND task.AreaName = 'XXXUI-v1'
AND task.System_AssignedTo > ' '
AND task.System_State <> 'Closed'
AND sprint.System_WorkItemType = 'Sprint'
AND sprint.IterationPath = task.IterationPath
GROUP BY task.System_AssignedTo, task.IterationName,
sprint.Microsoft_VSTS_Scheduling_StartDate, sprint.Microsoft_VSTS_Scheduling_FinishDate
HAVING (SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) > 0
OR SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) >0)
ORDER BY task.IterationName DESC, task.System_AssignedTo
Burndown Stored Procedure
SP Call to SharePoint 2010 Query String (URL) Filter web part
-----------------------------------------------------------------------
dbo.WorkItemHistoryView_Burndown;start={start};end={end};iName={iName};aName={aName};assigned={assigned}
dbo.WorkItemHistoryView_BurndownData;start={start};end={end};iName={iName};aName={aName};assigned={assigned}
SP Code
----------
USE [Tfs_01Warehouse]
GO
/****** Object: StoredProcedure [dbo].[WorkItemHistoryView_Burndown]
Script Date: 09/28/2011 09:49:53 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[WorkItemHistoryView_Burndown]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WorkItemHistoryView_Burndown]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Bob Hardister
-- Create date: 9/27/2011
-- Description: Produces a burndown table
-- =============================================
CREATE PROCEDURE dbo.WorkItemHistoryView_Burndown
@i int = 0,
@Start date = '9/16/2011',
@End date = '10/6/2011',
@dayRange int = 0,
@iName nvarchar(256) = 'Sprint 03',
@aName nvarchar(256) = 'XXXUI-v1',
@assigned nvarchar(256) = '',
@currentDay date = '01/01/0001'
AS
BEGIN
DECLARE @Burndown TABLE ([Date] date, Remaining float, Completed float)
SET @dayRange = DATEDIFF(DAY, @Start, @End)
WHILE (@i <= @dayRange)
BEGIN
SET NOCOUNT ON
IF (DATENAME(WEEKDAY, @Start) <> 'Saturday' AND DATENAME(WEEKDAY, @Start) <> 'Sunday')
BEGIN
INSERT INTO @Burndown ([Date], Remaining, Completed)
SELECT @Start as Dte
,SUM(Microsoft_VSTS_Scheduling_RemainingWork) as Rem
,SUM(Microsoft_VSTS_Scheduling_CompletedWork) as Act
FROM Tfs_01Warehouse.dbo.WorkItemHistoryView
WHERE ProjectPath LIKE '\XXXX%'
AND System_WorkItemType = 'Task'
AND (DateSK <= @Start AND DateSK >= @currentDay)
AND IterationName = @iName
AND AreaName = @aName
AND System_AssignedTo LIKE @assigned + '%'
END
SET @Start = DATEADD(day, 1, @Start)
SET @i = @i + 1
IF @Start >= GETDATE() SET @currentDay = @End
END
SELECT * FROM @Burndown
END
GO
GRANT Execute ON dbo.WorkItemHistoryView_Burndown TO TfsReports
syntax highlighted by
Code2HTML, v. 0.9.1
Team Foundation Server 2010 includes support for Data Warehouse and Analysis Cube Views.
The company, Amrein Engineering, sells a web part that uses SQL statements to source data.
You can implement this web part very easily to do some very nice reporting of TFS data.
For example, you cannot use "time" as a query criteria in Team Explorer. You can only use the date. That is, you can ask for all work items changed by Joe Black on 9/11/2011. But you cannot ask for changes between 3 and 4 PM on that date. However, you can qualify SQL queries by date and time.
Using the web part from Amrein I was able to:
- Develop a SQL query for work items changed between two timestamps
- Implement the query in the web part to display on the TFS team portal
- Add filters and formating to the web part
I was able to display the work item ID as a hyperlink that brought up the work item in the browser or in the Team Web Access editor.
Here's a couple examples of SQL queries against the TFS_Warehouse DB, dbo.WorkItemHistoryView view. Note: using ASOF queries with this view requires the use of the "System_RevisedDate > [ASOF Date]" and the "RecordCount > 0" conditions because of compensating records. "Whenever a work item is updated, a pair of records is added to the warehouse. The first record negates the previous record. This makes querying faster." See book 'Professional Team Foundation Server 2010'
/****** WORK ITEMS CHANGES IN THE PAST 24 HOURS ******/
SELECT [System_ChangedDate] as Changed
,[System_ChangedBy] as ChangedBy
,[System_WorkItemType] as WIT
,[Microsoft_VSTS_Common_StackRank] as Seq
,[System_Id] as ID
,[System_Title] as Title
,[System_State] as Ste
,[System_Reason] as Reason
,[System_AssignedTo] as Assigned
,[Microsoft_VSTS_Scheduling_StoryPoints] as Points
,[Microsoft_VSTS_Build_FoundIn] as FoundIn
,[Microsoft_VSTS_Build_IntegrationBuild] as FixedIn
,[Microsoft_VSTS_Common_Priority] as Pri
,[Microsoft_VSTS_Common_Severity] as Sev
,[Microsoft_VSTS_Scheduling_OriginalEstimate] as Est
,[Microsoft_VSTS_Scheduling_RemainingWork] as Rem
,[Microsoft_VSTS_Scheduling_CompletedWork] as Act
,[IterationPath] as iPath
,[AreaPath] as aPath
FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView]
WHERE [AreaPath] LIKE '\Cw%'
AND ([System_ChangedDate] BETWEEN DATEADD([DAY], -1, GETDATE()) AND GETDATE())
AND RecordCount > 0
AND [System_ChangedBy] <> 'Build Admin'
ORDER BY [System_ChangedDate] DESC
/****** WORK ITEMS CHANGED HISTORY ******/
SELECT System_Id as ID
,max(System_WorkItemType) as WIT
,max(System_Title) as Title
,max(System_ChangedBy) as ChangedBy
,max(System_ChangedDate) as Changed
,max(AreaPath) as aPath
FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView]
WHERE [AreaPath] LIKE '\Cw%'
AND [System_ChangedBy] = 'Joe Black'
AND ([System_ChangedDate] BETWEEN '9/1/2011 15:00' AND '9/1/2011 16:00')
AND [System_RevisedDate] >= '9/1/2011 16:00'
AND RecordCount > 0
GROUP BY [System_Id]
ORDER BY [System_Id]
syntax highlighted by
Code2HTML, v. 0.9.1
Now using the following URL syntax I was able to format the ID column in the web part as a link to the work item:
- Display work item in browser: http://[app server name]:[port number]/tfs/[collection name]/WorkItemTracking/Workitem.aspx?artifactMoniker=[work item id number]
- Display work item in Team Web Access editor: http://[app server name]:[port number]/tfs/[collection name/web/wi.aspx?id=[work item id number]
Team Foundation Server 2010 does not support publishing history data to Excel. However, there is an easy work around to this problem.
However, this is limited to the last recorded History comment for a work item. This is what displays in the query return set when the History column is included.
- Create a query with History as a column
- Run the query
- Click the pulldown on the "Open in Microsoft Office" button
- Select "Send Query to Microsoft Outlook"
An email pops up with the data in a table that includes (unlike Excel) the last history comment for each work item. Simply copy the table to Excel and format.
It's not a comprehesive record, but it's very nice as a CCB meeting record of changes just made.
You can also use OneNote for this.
If you are logged on to the local machine (not the domain) you will be prompted to enter network credentials when you open MTM to connect to TFS. However, if you receive a connection error when you attempt to run a test case, you may want to try the following work around.
- Go to the Test Plan
- Select properties
- Select you Test settings under Manual Runs
- Uncheck the ASP.NET Client Proxy collection
- Save and use that Test Settings Configuration to run a test case
It seems there are many issues that can cause this error. But in my situation it turned out to be a permissions issue.
It appears that other TFS 2010 reports use the service account (i.e. TFSReports) to access SSAS. However, it seems that the burndown chart in the TFS MSF v5.0 Agile Iteration Backlog Excel file passes the current user credentials to SSAS to get chart data. As only the TFSReports account had access, the burndown chart request was denied resultign in the error noted above.
To fix this I:
- Opened SQL Svr Mgmt Studio on the data tier
- Connected to Analysis Services
- Expaned the TFS Analysis DB
- Expanded Roles
- Right-clicked on TfsWarehouseDataReader and selected Properties
- Clicked on Membership
- Add the group (or users) needed
After that I no longer got the error and was able to pull data into the burndaown chart.
Earlier this Summer I had the privilege of working for 16 days onsite at the Microsoft offices in Redmond and Vancouver. The assignment, a "Rangers Sabbatical," is part of the Microsoft Visual Studio ALM Rangers program. It provided me the opportunity to develop a Microsoft Word "Technology Provider" or plug-in for the Visual Studio 2010 ALM Coded UI (CUI) feature. Being onsite I was able to work closely with Microsoft Ranger and Program staff.
The Sabbatical was a great success both professionally and personally. I was the developer on the team. We had a prototype of a Excel plug-in for CUI. I used that as a starting point for the creation of a Word plug-in. The approach involved using the CUI extension classes , a Visual Studio Word 2010 (VSTO) Add-in project and the Microsoft 2010 Word API to enable document specific UI recording, playback and assertions to be developed using the Coded UI Test Builder. I had done some preliminary investigations before going onsite, but the level of effort to actually make this work was still uncertain.
This made for early mornings and late evenings. However, the hard work was quite enjoyable. My accommodations in Redmond were less than 5 minutes’ walk from the office. Bijan Javidi and Chuck Sterling were extremely gracious hosts. They provided me a pleasant work environment and introduced me to many of the Visual Studio program team members. Chuck included me in a test drive of Dev11, and had me over for dinner where I got to meet and talk with one of my all time TFS hero's, Grant Holiday.
As it turned out, the planning, support and hard work paid off. With the help of two key team members, Andrew Whitechapel, VSTO guru, and Mathew Aniyan, the CUI program manager, I was able to get the plug-in working. The plug-in was put to immediate use to create CUI tests for the Rangers Word4TFS product. I have been able to take the knowledge and skills gained during the Sabbatical back to my employer for use and for developing additional plug-ins as needed.
Word CUI plugin document record, playback and selection properties
I wrapped up the Sabbatical by developing training material for the plug-in and leading a training session at the Microsoft Vancouver offices. While in Vancouver I was treated to the special care and hospitality of Willy-Peter Schaub, the Rangers Solution Architect. Willy set me up in the Microsoft offices in Vancouver and showed me around a bit. I could not been better welcomed and treated as a guest.
The Rangers Sabbatical program is a great opportunity to deepen skills and become better equipped as a Ranger and Visual Studio ALM professional. I want to express my sincerest thanks to Bijan, Willy and Chuck for giving me this opportunity and for making it a great experience for me.
There are at least three scenarios that deserve mention:
1. Existing data
It doesn't cause an issue. TFS caches the name->SID mapping in the database. If the accounts go away, existing work items/source control history aren't changed.
2. Checked out files
Bottom-line: we need to ensure that there are no files checked out for an account that is to be deleted. You could run into trouble if files are checked out under an account that is deleted and a new account with the exact same name (but different SID) is created before an admin forces an "undo checkout" on those files. What you got was files that were permanently checked out.
3. Migration to TFS
When the TFS Integration Platform tools are used to migrate source code to TFS and the adaptor cannot find the account ID in the source record, the account running the migration is used as the “default” account. So for example, when migrating the change records made by BGONE from Subversion to TFS, the change records are tagged in TFS with TFSTOOLS because my account was used to run the migration.