I was plugging away at the Project Server newsgroup when one of the contributors asked an interesting question. After trying to aggregate Issues from each of the project sites to a single centralized repository using Content Query Webparts, Bart E. asked, “So there is no OOTB solution for a sortable aggregation, I take it?”
….well, that got me to thinking. Is there an easy out of the box solution to deliver centralized risks and issues from Project Server? The obvious solution would seem to be through the use of External Content Types, which I have come to regard as the Swiss Army knife of the SharePoint 2010 world. Using External Content Types and External Lists would allow the development of easily customizable lists of aggregated risks.
So I sat down with a virtual image to see if I could make it work. To my surprise, 20 minutes later, I pretty much had it working – with some minor formatting issues that took me back to the drawing board.
Hence my next couple of posts will be about this topic, specifically how to use External Content Types to surface the risk information embedded in the Project Server reporting database. This approach is only suitable in the following circumstances:
- Projects are published primarily from the Microsoft Project client – as that is the action which seems to refresh the database from the Project site data.
- Risks are managed without custom fields, or at least the aggregation does not require those custom fields to work. The Reporting database only captures the default fields.
- Project schedules are updated regularly. It is through the update process that the risks are populated within the Reporting database.
First, let’s start with a little review of the available literature. What options are currently available? Here are the ones I’ve come across so far.
- SSRS Reports – as documented by Christophe Fiessinger here.
- Third party tools such as this one from iPMO.
- Utilizing the Content Query Webpart within SharePoint – which required a little more coding the last time I played with it than I was comfortable with. Still, probably worth a blog post at some point in the future. A variant of this approach would cross site collections and might include something like the Content Monster Webpart.
This series of posts will focus on a fourth method, using External Content Types and an External List to surface Reporting database data. You could probably do the same with a Web Service pulling data from SharePoint content databases, but I would defer to the SharePoint folks on that.
Hence, over the next couple of days, watch out for the following posts:
- Customizing SQL views to create the dataset
- Creating an External Content Type from a customized view
- Creating an External List with Risk data
(My plan is to come back and add hyperlinks to this post once each of those individual posts have been published.)