What is the best approach to generate unique IDs for Issues and Risks in Project Server 2010? I need them available in SharePoint and Project Server Reporting Database? They should be available in database because I have to create a report in SSRS and I don't think that querying SPList is a good idea.
Any Ideas?
I have yet to discover a nice answer for this, and I've been looking for years. Querying the SQL databases directly is oh so tempting, but it leads to a lot of pain whenever a user customizes a SharePoint site: columns will not necessarily have the same meaning from site to site.
The Project Server Reporting database has some out-of-the-box fields that are updated when a project is published, and includes an issues Unique ID. Check out MSP_WSSIssue and MSP_WSSRisk in the Reporting DB.
We needed to use custom fields that had been added to the list, so we ended up using the Enesys SSRS datasource extensions that allow easier reporting across SharePoint sites.