Exactly a year ago while stuck at home during a Seattle snowstorm I wrote this post How to report Project Risks at a Program Level?
Basically the need is: how can you easily create a report that aggregate multiple SharePoint lists (typically Issues and Risks in the Project Server world but not only if you have created custom lists in your Project Workspaces)? My solution was hardcore, not flexible and not easy to administer. Well, it must be Christmas very soon because i-PMO from down under Australia has actually built a solution that solves this problem and more in an elegant way: i|PMO DataMiner 2009 for SharePoint
No they have not paid me to post this and if you also have a solution that addresses this reporting problem let me know and I will gladly update my post!
I downloaded the trial version of i-PMO SharePoint Data Miner in my favorite PPM V4 demo image and within minutes was able to mine all my project workspaces for all Issues and Risks.
and voila all your items from all your lists are now stored in one reporting database and you can then easily create powerful dashboards using you favorite reporting tool (SQL Server Reporting Services, Excel Services, PerformancePoint Services to name a few :)) What I liked about this solution beside solving the problem at hand is that it was easy to install and configure and I did not need to purchase additional services to be up and running. In the end you might still want to work with a Business Intelligence expert to build the exact report/dashboard/KPI that you need but at least this solution does all the work for you of storing all the items from the all the SP lists.
Please note that this solution is applicable to not only Project Server 2007/SharePoint 2007 but also the upcoming 2010 release.