You can use M$ SQL Server Report Builder to generate reports. Check out the link below for an example.
You can get Report Builder and learn more about it through the forum at http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/threads/
Here is an Enterprise PDM report (see Tools, Report Generator) to get you started.
You can open this in notepad and examine the T-SQL query.
TransitionHistory.crp.zip 840 bytes