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.
LVL 14
shahprabalAsked:
Who is Participating?
 
shahprabalAuthor Commented:
Got the right query to do the job...
SELECT     main.[File], main.LastUpdated, main.Person, sub.System_Id AS [Work Item]
FROM         (SELECT DISTINCT dbo.[File].[File], MAX(dbo.[File].__LastUpdatedTime) AS LastUpdated, dbo.Person.Person, [Work Item Changeset].[Work Item]
                       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.Person ON dbo.[Code Churn].[Checked In By] = dbo.Person.__ID
                       WHERE      (dbo.[File].[File Extension] = N'.sql')
                       GROUP BY dbo.[File].[File], dbo.Person.Person, [Work Item Changeset].[Work Item]) AS main INNER JOIN
                          (SELECT     one.ID, one.System_Id
                            FROM          (SELECT DISTINCT MAX(__ID) AS ID, System_Id
                                                    FROM          dbo.[Work Item]
                                                    GROUP BY System_Id) AS one INNER JOIN
                                                       (SELECT     __ID AS ID, System_Id
                                                         FROM          dbo.[Work Item] AS [Work Item_1]
                                                         WHERE      (System_State = N'Ready for Deployment')) AS two ON one.ID = two.ID) AS sub ON main.[Work Item] = sub.ID

Open in new window

0
 
James MurrellProduct SpecialistCommented:
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
0
 
ValentinoVBI ConsultantCommented:
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/
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
shahprabalAuthor Commented:
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?
0
 
ValentinoVBI ConsultantCommented:
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 :-)
0
 
shahprabalAuthor Commented:
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.
0
 
shahprabalAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.