Friday, July 20, 2007

Project Server Reporting DB - reporting against WSS data

Finally the end of the financial year is here and I have finally some time to attend to my blog again. It has been a couple of weeks.

Over the last couple of weeks I have started to use the Project Server Database extensively and I must say that I am very impressed. I really like what the Microsoft Project Server team has done. Any information regarding the Project schedule, resource schedule and time sheet I can report against and thanks to the Project Server 2007 SDK I have started to write some valuable Reporting Services reports.

You should also have a go at the report builder in SQL 2005. All you need to do is create a Model. The easiest way to do this is to create a model against a Project Server Reporting Database view. The rest is done more or less by SQL Server. Once you have created the model you can allow business users to create their own report. So far so good. THANK YOU MICROSOFT :)

BUT as soon as you want to report against WSS data you will find it most challenging. The Project Server Reporting DB does transfer Risk, Issue and Deliverable data from WSS to the PS Reporting DB, but only the fields that are pre-defined in the Project Workspaces. That is not very helpful at all. Most Organisations ( if not all) do change at least the Risk and Issue list to suite their business. Any new field do not move from WSS to the PS Reporting DB.

Also, if you delete a field from the "out of the box" template you will receive an event log error saying something like this" The PS Reporting DB cant find a field from the WSS site". That tells me that Microsoft have hard coded these fields. AUTSCH.

Furthermore if you create new lists in the Project Workspace, these will not move into the PS Reporting DB. That means any information maintained in Project Work spaces cannot be reported against UNLESS you have a Reporting Services to WSS wrapper. I think that is very sad. We are in the process of developing a wrapper that will allow us to report against the PS Reporting DB and WSS Workpace Data.

so be aware out there, Reporting against the Project Server DB is excellent and easy. Reporting against WSS data is painful.