0

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?

Greg
  • 942
  • 10
  • 18

1 Answers1

1

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.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • It is just too bad. Unique IDs from MSP_WSSIssue and MSP_WSSRisk are guids and they are not useful in my case. – Greg Sep 03 '13 at 19:10
  • I thought about adding new UID (autoincrement) column to MSP_WSSIssue and MSP_WSSRisk and trying to display it in SharePoint but I don't know if this makes sense at all. – Greg Sep 03 '13 at 19:15
  • I would be surprised if it worked. I think the rows are deleted and recreated when a project is published. You could have some code mapping the Guids to your own table, and have an identity column there to use. Then if a line is deleted from the MSP_WSS* tables, you won't have a problem. – Jamie F Sep 03 '13 at 19:40
  • Hmm I've added autoincrement column to MSP_WSS* tables, also I've added event receiver which reads (using BDC) unique id from DB and stores it in SharePoint Risk/Issue List. And I think it looks good. The question is what could go wrong, where should I expect problems? (I'm not Project Server expert) – Greg Sep 04 '13 at 10:37
  • Edit an issue, and then republish the project file associated with that site. See if your auto incremented column still has the same value for the issue ((I'm guessing it won't.) Also, we are off into unsupported territory here: Service packs and cumulative updates may delete your column, and MS may not support issues around this. – Jamie F Sep 04 '13 at 13:53
  • What do you mean by "edit issue". I've opened a project in Project Professional 2010 and when I click on Issues or Risks, SharePoint list opens in my web browser. – Greg Sep 04 '13 at 15:01
  • Some SharePoint lists use versioning, so editing an item, like one of those issues, can generate new IDs. The publish process forces Project Server to update the reporting database. (It isn't updated before that point.) – Jamie F Sep 04 '13 at 15:56
  • The ID is the same after edit and publish. So the only problems are service packs and updates. The answer for my question is: There is no good way to provide unique IDs for Risks and Issues. Thank you for helping me. – Greg Sep 05 '13 at 07:45