Returning Reporting Database Values in the Project Workspace

Andrew Lavinsky

Just a quick add on to yesterday’s post on leveraging InfoPath to create automatically filtered PerformancePoint reports on PDPs.  I figured that I’d extend the same approach to the workspace.

For the workspace, we can simply pull the URL of the workspace, then map it to the ProjectWorkspaceInternalHref field within the Reporting database.  Once that’s done, we can return any value linked to that project record for all of your filtering or field population needs.

You’ll need to create a Form library on the workspace, but can share the same connection string we created in yesterday’s post.  I’m not sure if you can share a single form across workspaces – but I bet it’s possible, and if so, that would ease some of the administrative effort around this solution.

Essentially, it’s pretty much the same approach I took yesterday…except with the following:

I created a TempURL field.  Instead of relying on the SharePoint Query String Filter to populate it, I just set the value to default to the site URL.  (Which in theory, I probably could have done to pull the ProjUID from the PDP URL as well).

image

The problem there is that the site URL appears like this:

http://project.contoso.com/PWA/Company%20Portal%20Database%20Migration/

…but it’s stored in the database like this…

http://project.contoso.com/PWA/Company Portal Database Migration

…so when we copy it from the TempURL to the ProjectWorkspaceInternalHref queryfield, we need to set the rule to truncate the trailing “/” and swap out the “%20” with actual spaces.  This can be done using the following formula:

translate(substring(TempURL, 1, string-length(TempURL) – 1), "%20", " ")

image

From there, it’s just a matter of taking the same approach as in yesterday’s post.  Run the query, copy the results into a custom field, and leverage that custom field for all new Webpart connections.

Returning Reporting Database Values in the Project Workspace

5 thoughts on “Returning Reporting Database Values in the Project Workspace”

  1. Bob O'Reilly says:

    Thanks for a great tool. Am having one small issues is that not just any “20%”s are being replaced but all instances of “2”, “0” or “%”. As my Farm includes 2010, this causes some problems as sp2010 is shortened to sp1. Am i missing something in the formula?

    Reply

    1. Probably not. Sounds like an error on my side. You may have to get creative with the formula, i.e. truncate the last X characters to get the project name, strip out the %20, and then concatenate the results with “http://projectserver/pwa/”. In Excel, it wouldn’t be too hard, and I know InfoPath can handle it.

      Reply
      1. Bob O'Reilly says:

        Thanks, I have been looking at ways to solve this in my spare time but am yet to come up with any. THe solution above works for example if ther eis only one space in the project name AND there are no 0,2 or % in the name. Its a shame Infopath is so limiting in this regard and it seems as if custom code will be required

      2. I think this formula will handle this issue:
        concat(SharePointSiteCollectionUrl(); translate(substring(substring-after(SharePointSiteUrl(); SharePointSiteCollectionUrl()); 1; string-length(substring-after(SharePointSiteUrl(); SharePointSiteCollectionUrl())) – 1); “%20″; ” “))

Leave a comment