Update 31/03/2011: See the following blog about how much simpler this is with SQL Server 2008 R2.

This is one of the most common gripes that I hear about Project Server’s reporting database, the fact that only the default fields in your Project workspace lists, ie Project Risks, Project Issues, etc are available to report on.

Unfortunately I have yet to come across a client who uses (for example) a percentage probability of risk, I’m guessing it’s all that PRINCE2 influence. So as a result you are usually left with two options;

1. Update the default fields to match your requirements, which actually is possible in 90% of the cases with some careful modifications to the workspace template and possibly some custom MDX measures for your cube. The biggest drawback with this one is if you want to report on new lists, such as for example a Change Register it is simply not possible without extending the RDB and cube!
   
2. Secondly you could find and purchase a third-party product that will allow you to report directly on all of the list contents of your workpace, some of these link directly into the sites, others use a staging database.
   

There is of course a third option though, do it yourself, and that is what this blog post is about.

How to access the SharePoint data?

The first question is how, and of course I should be clear as a one-time minion of the capital MS, the question must be how to do it in a supported manner. So that completely rules out direct SQL access to the databases!

Fortunately there is another way: XML.

Let me start by saying that there is a lot of information is out there on this method, so here are some links to some of the most helpful pages; at codeproject, and other blogs here and here. (That last Blog one I probably found most useful!)

Before I get into it it’s worth mentioning that my example involves using Visual Studio 2008 with the SQL Business Intelligence Design Studio (BIDS) installed. Don’t let the VS2008 bit scare you it would be possible to do much of this using Report Builder, at least once you have your data connections created, perhaps I’ll leave that for a future blog?

In summary this is what you need to do:

1. Create a SRS data source pointing to your PWA Reporting database.
   
2. Use the above data source to identify the field named "ProjectWorkspaceInternalHRef" from the "MSP_EpmProject_UserView" table for each project you want to report on.
   
3. Then use the URL indentified to dynamically construct your XML data source query by basically appending "/vti_bin/lists.asmx" to the end of the string.
   
4. Finally using XML pull down the information that you want.
   

Too easy!

Okay show me the details!

Okay so first thing you need to do after firing up Visual Studio is start a new blank Report Server Project, then:

Step 1: Create your Shared Data Source

Create a standard SQL data source pointing to your Project Server Reporting database, such as:

Step 2: Enumerate Workspace URLs and Select your Project

Firstly create a blank report with a simple SQL query to enumerate a list of all projects and their associated workspace URL:

Example query:

Then create a Parameter to prompt the user for a selection:

Name: SelectedProject

Prompt: Select Project

Select Get values from a query for Available Values:

Value field: ProjectWorkspaceInternalHRef

Label field: ProjectName

Step 3: Create your XML Data Source

Now the interesting bit, create a new non-shared datasource (Embedded connection).

Type: XML

Connection String: Expression

Credentials: Do not use credentials

Expression should be as follows:

Step 4: Now create your Dataset using XML

Now you need to create your first Dataset in this XML data source, this is where we reference the actual list item, in this example Project Risks.

Create a new dataset using the following query:

Populate the Parameters with the "listName" (note: this is case sensitive) parameter with the value "Risks":

Step 5: Select your Fields to include in the report

Now the annoying bit, under Fields you must include each field that you want to include in your report, however because we are dynamically generating the data source we cannot automatically populate this.

Note: To automatically populate this list hard code the data source to an actual workspace site then when you create the query for this dataset select Refresh Fields.

Otherwise you can manually populate these, just open the list in question in a workspace and open list settings:

Then use the Column name in the Fields list source by simply prefixing each name with ows_, for example: ows_Title

Resulting in something like this:

Step 6: Finally Create a Test Report to see the Results!

Now add a table to your report and add those columns to preview the data, you should see something like this:

Unfortunately you might notice some fields being blank, there’s actually something we forgot! One of the possible parameters in the dataset above is "viewName", that does exactly what you would think select’s the desired view, so as my example above (using the EPM2007DEMO databases with default column names) Owner, Description and Mitigation Plan are all blank, this is because the Default view configured for that list does not include them.

So you can fix this by changing the default view to include the required fields or by creating your own view and using the viewName parameter. Note though; that parameter requires the GUID of the view not it’s name, so you need to use something like http://meyerweb.com/eric/tools/dencoder/ to decode the GUID from the URL of the view. (See the above linked blog by David Wise for additional details on the parameters available)

One that is done, you should now see something more like this:

Looking good! (Almost!)

Just one last thing there, you can see the special characters in the data, for the Description and Mitigation Plan it is an easy matter of changing the ‘Placeholder Properties’ to Markup Type: HTML. However for the Owner and as you’ll find various other fields they contain internal codes used by SharePoint, these have to be removed with code, again David’s blog above has some code to do just that, or if you’re like me and didn’t quite bother to read down that far you can create your own and add it to the Report Properties – Code section:

Then just replace your Owner field name with the following expression:

Now your report should look something like this:

All Done!(Well for me at least)

What’s the catch?

There is actually a big one which you might have guessed: performance.

The above report is effectively generating the report by browsing to each page and pulling down each list item every time the report is generated. For one single project like in the example above, that’s not such an issue, but if you wanted to create a portfolio summary report for example or batch process a single project report for all active projects then performance would quickly become a problem.

Much of that can be addressed through Reporting Services itself, using caching and pre-generation, etc or better yet using SQL Integration Services (SSIS). With that and some much more interesting scripting a DTS job could be created to setup your very own staging database using the same XML methods as above! Definitely something to blog about another time…

But what about??

Yes there are lots of questions left unanswered;

1. What if someone renames the list used above?
   
2. What if someone changes / deletes the view I selected?
   
3. What if …
   

I think you can see some of the limitations here and possible also how they can be overcome (workspace template standards, etc) but I’ll leave that to you.