asked on

TFS 2008 customization

We have a process where when the developers change or add a database script and check it into the project. At the time of deployment the release manager needs to know what work items have database script checked in against it. Is there a way where we can query or create a custom report in TFS to get a list of work items that have a file in a changeset with a particular file extension (.sql). This way the release manager will get a list of the work items that she can then give to the DBAs to analyse, check and apply to the server.

We are using TFS 2008.

James Murrell

we use a third party product called subversion svn - so any updates, people get emailed, we can test/check code against db, or what we are working on...

Hope this helps if not - apologies

ValentinoV

Team Foundation Sidekicks is a really powerful bunch of tools. I'm not 100% sure if what you're asking is possible with it (I can't try it out right now) but I'm sure it's worth having a look, it's freeware anyway: http://www.attrice.info/cm/tfs/

shahprabal

ASKER

Thanks cs97jjm3 and ValentinoV. I haven't looked into subversion svn... will check it out. I have used Sidekicks for other purposes in my previous company and am in process of getting it approved at my present company.

I have however seen that the file field is exposed in the TfsWarehouse database datasource that is available out of the box.

Are you familiar with the Report Manager / Builder ? From the TFS Report site I can click on Report Manager and create report but it doesn't allow me to set parameters. Will I be able to use the same datasource and add parameter to it if I create a report in Visual Studio or Sql Management Studio?

ValentinoV

I do know the Report Manager/Reporting Services, from a SQL Server perspective. I had a quick look at the TFS reports and noticed that they are actually using the SQL Server Report Manager.

And I found following Microsoft link about creating and customizing TFS reports: http://www.microsoft.com/downloads/details.aspx?FamilyID=A74486B2-F7DB-4A85-97BD-46BF478BDA60&displaylang=en

I haven't tried this out myself, but I am noting it down on my TODO list, out of curiosity :-)

shahprabal

ASKER

Hi Valentino, I have already gone through the info in the link... I am finding it a pain to work with TFS reports... multiple dbs... different ways of accessing the info (directly and via OLAP cube). Core information like State is hard to find. I even found a table with file names, extensions and last updated time. Which is what I need, partly. I am able to connect it to a work item but not state.

shahprabal

ASKER

I got the query but it is returning WI's which had the 'Ready for Deployment' status currently or previously. I just need the records with WI in that state currently.

Thanks for your help.

SELECT DISTINCT dbo.[File].[File], MAX(dbo.[File].__LastUpdatedTime) AS LastUpdated, dbo.[Work Item].System_Id, dbo.Person.Person
FROM         dbo.[File] INNER JOIN
                      dbo.[Code Churn] ON dbo.[File].__ID = dbo.[Code Churn].Filename INNER JOIN
                      dbo.Changeset ON dbo.[Code Churn].Changeset = dbo.Changeset.__ID INNER JOIN
                      dbo.[Work Item Changeset] ON dbo.Changeset.__ID = dbo.[Work Item Changeset].Changeset INNER JOIN
                      dbo.[Work Item] ON dbo.[Work Item Changeset].[Work Item] = dbo.[Work Item].__ID INNER JOIN
                      dbo.Person ON dbo.[Code Churn].[Checked In By] = dbo.Person.__ID
WHERE     (dbo.[File].[File Extension] = N'.sql')
GROUP BY dbo.[File].[File], dbo.[Work Item].System_State, dbo.[Work Item].System_Id, dbo.Person.Person
HAVING      (dbo.[Work Item].System_State = N'Ready for Deployment')
ORDER BY dbo.[Work Item].System_Id

Open in new window

ASKER CERTIFIED SOLUTION

shahprabal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial