I'm looking for a way to run a couple of audit reports;

1. Items currently Checked Out
2. Check Out History by Item

I've done some searching, and found some info on the Schemas available here.  But, my problem is that I am not yet well versed in the Reporting Services side of SQL Server 2005.  In Reporting Services, I've set up a Data Source and Model for the TfsVersionControl database.  However, I haven't had any success yet with creating one of the two reports mentioned above, and am not even sure if I am going about this the right way.  My problems are likely permissions-related, but my main question regarding Reporting Services is, am I supposed to create my own Model against the TfsVersionControl database?

If anyone has already done something similar, and is willing to share, I would appreciate it.  Also, if I am going about this the wrong way (from a Reporting Services standpoint), a little direction would also be apprciated.

Thanks,

Chuck

Some additional food for thought...

I've taken the approach of writing SQL directly against the TfsVersionControl database, probably not a good idea.  I have put together the following SQL, and am looking for suggestions/answers to some questions.

1. Can anyone tell me if this is a bad idea, i.e. are you aware of potential schema changes, etc. coming down the pike.  I would assume that there are, but am looking for alternatives (view, use of Reporting Services, etc.)
2. I haven't yet searched, but if anyone has the "decode" values for LockStatus and LockType, feel free to let me know.

SELECT  MAX(V.VersionFrom) VersionFrom
  ,V.ItemId
  ,V.FullPath
  ,I.DisplayName
  ,L.LockStatus
  ,L.LockType
FROM dbo.tbl_Version V (NOLOCK)
INNER JOIN dbo.tbl_Lock L (NOLOCK) ON V.FullPath = L.FullPath
INNER JOIN dbo.tbl_Workspace W (NOLOCK) ON L.WorkspaceId = W.WorkspaceId
INNER JOIN dbo.tbl_Identity I (NOLOCK) ON W.OwnerId = I.IdentityId
GROUP BY  V.ItemId
   ,V.FullPath
   ,I.DisplayName
   --,W.Computer
   --,W.WorkspaceName
   ,L.LockStatus
   ,L.LockType

Hi Chuck,

As you probably already learned you are not going to be able to get this kind of reports from the Warehouse and you will have to go directly against the TfsVersionControl database. I'm not very familiar with the details of the implementation of that database so I think that if you post your question to the Version Control forum here http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=478&SiteID=1 someone will be able to provide better feedback about the schema of those tables and how you can extract the data you need. I also can say that because the TfsVersionControl database was not meant to be accessed directly through SQL but instead through the existing APIs and tools it is not guaranteed that its schema will remain the same in future versions so you may want to consider creating views and build your queries on top of the views.

As far as creating reports a great resource where to start if you are new is this MSDN location http://msdn2.microsoft.com/en-us/library/ms170246.aspx.

I hope this helps you to get started.

Thanks.

Federico

Federico,

Thanks for the reply and the links. I will repost as suggested.

Chuck