How to get the Check-in Notes in a report?

Question

Hi,

Im' about to create my first report to prove that TFS2010 is the right tool :)

However I need to create a small demo report including changeset number and especially the Check-In Notes, but the notes elludes me.

Do anyone know where to find them?  and/or where to retrieve additional example reports for this? 

Thanks in advance!

Answer 1

I think you will find the Check-in notes concatenated with the changset Id in Desription field of the dimension Version Control Changeset or the ChangesetTitle field of dbo.DimChangeset if you using the tfs relational warehouse database.

 

For example reports you could take a look at Ewalds report on check in violations http://www.ewaldhofman.nl/post/2010/03/15/Check-in-violations.aspx

 

Answer 2

Hi Mattias,

Thank you very much for your reply.

Sadly the Check-in Notes are not concatenated in the Description nor the ChangesetTitle fields.

The only hint that I can find, is when looking in the dbo.tbl_ChangeSet table, i find a column named CheckInNoteId, which must be it - now I "only" need to find where those notes are stored and how...

Also I cannot get Ewald's report to work, but will try to fiddle with that

 

Answer 3

Oh, I think I understand, you need the Check In Notes you can define in SOurce control settings, not the comment for the check-in ?

One thing firtst, the Tbl_changeSet table is not located in the TFS Warehosue database, It not recomended or supported doing sql agains the TfsDatabases, other then the tfs warehouse database and cube.

 

Answer 4

Oh, I think I understand, you need the Check In Notes you can define in SOurce control settings, not the comment for the check-in ?

Yes, fully correct :)

However I need to include the Check-In notes on reports as a part of our QA program to ensure that all assessments were done before the checkin ... and I would expect that I'm not the only one with that requirement?

So if those informations are not populated to the Tfs_Warehouse, I see no other solution than to have to access the Tfs_*Collection tables directly (?)

Are there a more correct way?

All comments are appreciated :)

Answer 5

You should use the tfs api to query tfs prod data. You can use code form SSRS or simply wrap your code with a webservice interface and use it as datasource for your report.

One other way would be to write a custom adapter for the warehouse extending the warehouse with Check In Notes.

 

Answer 6

Mattias, first thanks for your help.

I have worked as a developer for windows in many years, but are brand new/unexperienced with TFS and SSRS - however after being really impressed with the other reports in TFS, I would have expected that to create my small report would be a "walk in the part" :)

Your recommendations are probably very sound and correct, but sadly not what helps me since I wouldn't know how to do so.

My simple purpose are to generate a small report, only using MS SQL Server Report Builder (or MS Excel) - a basic and simple report only containing rows with Date, Description and Check-In Notes for each changeset (in a period of time).

No-one that have had the same requirement?

Kind (but desperate) regards :)

Answer 7

I am a very similiar requirement. We are using the check-in note to record the hours worked on the check-in. We are trying to pull the data into a report and have not been successful.

 

Is there any guidance on writing a custom adapter for the warehouse extending the warehouse with Check In Note?

Answer 8

If your are a windwos developer neither of the solutions would should be that hard to accomplish. The biggest obstical might actually be to get the permissions to either copy an assembly to the SSRS server or host a webservices somewhere.

If you have access to the SSRS /TFS Server, either a custom warehouse adapter or a codesnipet for SSRS would be the easist way, i think.
Thier is nothing to stop you form accessing the prod databases as long as you are aware of that its nots supported/best practise and you take the appropiate meassures.

I havent runned into any one with the same requirements, mostly I think, because if you have those needs you probably using work item tracking and handle the information/review process in work items.  

Answer 9

The same goes for time reporting, most people tends to use Work Item tracking for time reporting, you could for example checkout the tfs timesheet at codeplex http://tfstimesheet.codeplex.com/

For guidance about tfs2010 Datawarehouse adapters please refer to this post http://blogs.msdn.com/b/team_foundation/archive/2010/03/03/team-foundation-server-2010-cube-schema-changes-in-rtm.aspx , it also contains links to an example adapter.

 

 

Answer 10

While i agree it's not too hard to develop a solution to this problem....I'd still like to have a referenced implication that's more complete with regard to writing a custom warehouse adapter for TFS...a code sample with checkin notes would go a long way.  

I should mention that I researched the TFSTimeSheet before I posted....the TfsTimesheet example doesn't actually WORK for recording the hours worked from the custom check-in note.

Also, saying that  "If your are a windwos developer neither of the solutions would should be that hard to accomplish." is a bit condescending. Some of us have a huge backlog of work to accomplish and running up against limited documentation/samples/examples with Microsoft software isn't our cup of tea. 

Hopefully some sample/reference from MSDN will be made available at some point.

 

Answer 11

Hi Allen,

A very small help from here:

The table tbl_ChangeSet have all the check-in changesets (and the system generated changesets); the column CheckInNoteId is a reference to the column ReleadeNoteId in the table tbl_ReleaseNoteDetails where you will find all the CheckIn notes and their values.

This should enable you to make the report - how to write a custom adapter however; i'm completely blank.

Answer 12

If you plan to query the Tfs Prod database directly, you could consider adding WITH (NOLOCK) to your SQL statemens. It can also be a good idea to use SSRS cto schedul report execution to low traffic hours.

 

Answer 13

If you plan to query the Tfs Prod database directly, you could consider adding WITH (NOLOCK) to your SQL statemens.

Cool, thanks!

It can also be a good idea to use SSRS cto schedul report execution to low traffic hours.

I was not aware of a scheduling posibility in SSRS, again a MS product that keeps growning on me :)

/René

Answer 14

Is there any sample/reference from MSDN that's available for this type of customization?

Answer 15

Any MSDN guidance?

Answer 16

still looking....

Answer 17

still looking ++

Answer 18

bump. Still looking

Answer 19

bump++

Answer 20

This is something I am looking for as well.  We have several TFS 2010 Collections (8 total).  So, what I am trying to do is write a report using the Warehouse/Analysis databases that provide details on Check-Ins for a given period of time, including the "Check-In Comments".  Is this possible in TFS 2010?  Examples?

thanks.

---

Cheers, Alan

Answer 21

In TFS Server > TFS_TeamProjectDB>tbl_ReleaseNote, tbl_ReleaseNoteDefinition, tbl_ReleaseNoteDetails