Returning Reporting Database Values in the Project Workspace

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.

Advertisements
Returning Reporting Database Values in the Project Workspace

Adding Filtered Project Dashboards to PDPs (Part 2)

In this post, we’ll take that InfoPath form we created in the last post, add it to a PDP, and link it to a PerformancePoint report to create an automatically filtered project dashboard. 

For what it’s worth, I know it kind of sounds complicated, but after you’ve done it a couple of times, it really only takes a couple of minutes to create and deploy this solution.

1) Create the PerformancePoint Report

I discussed this topic in this post.  Basically, you want to create a report, using the Project List as a background element.  Here’s a simple report that I created.

image

Make sure to right click on the graph and set it to filter the empty series.  That will force the graph to adjust the bar width for the project specifically.

2) Create the PDP

For this, I’ll create a PDP called “Project Dashboard.”  On this PDP, I’ll add three Webparts:

  1. Query String Filter – to pull the ProjUID value from the URL and pass to the InfoPath form.
  2. InfoPath Form – to pass the ProjUID value into SQL and return the Project Name – which is then passed into PerformancePoint.
  3. PerformancePoint – to display the data.

Query String Filter

I’ll add that to the page and configure it to use the ProjUID value.

image

The InfoPath Form

Next, add the InfoPath Form Webpart.  Set the Chrome to None.  Minimize the Webpart.

image

The PerformancePoint Report

Now add the PerformancePoint Report Webpart to the page.

image

Webpart Connections

Now add the appropriate connections:

  1. The Query String Filter will pass the ProjUID to the TempUID field in the InfoPath form.
  2. The Infopath form will pass the TemProjName field to the PerformancePoint Report.

Exit the edit page mode to confirm that it all works:

image

…and there you are, an automatically filtered PerformancePoint report embedded in a Project Server PDP.

Adding Filtered Project Dashboards to PDPs (Part 2)

Adding Filtered Project Dashboards to PDPs (Part 1)

I guess a combination of that last post on “fun with InfoPath” and my upcoming TechEd BI presentation prompted me to get in a bit of an InfoPath/BI rut this week….or maybe it’s just the fact that I can feel productive by blogging even when I am procrastinating my presentation prep….

Anyway, with today’s post, I wanted to discuss how we could leverage InfoPath to pull values out of the URL and then use them to filter PerformancePoint dashboards.   (See this post on filtering reports based on the active user.)

image

Why would we want to do this, you ask?  Great question.  Here’re a couple scenarios:

  1. Adding a filtered project dashboard on the Project Center PDPs.
  2. Adding a filtered project dashboard on the project workspace – without having to configure each workspace individually.  (Note that MVP Alex Burton released a customized solution that supports this for SSRS)

More specifically, let’s examine the challenge of adding a custom project dashboard to a PDP.  The main challenge here is that our primary key is the Project UID, which resides within the PDP URL, and can easily be consumed by the SharePoint Query Filter Webpart.  Unfortunately, as PerformancePoint reports do not have the Project UID as an available dimension, we can’t filter based on it.  Instead, we have to translate the Project UID from the URL into the official name of the project from the Project Server reporting database.

The workspace has a similar issue, as the workspace URL typically includes the name of the project when the project was created….which may not be the current name of the project if the scheduler or project manager has changed it.  Hence, in the workspace, we would need to map the URL to the official project name to get the parameters required to automatically filter a report.

The InfoPath solution I’ll show you today will do that.  Essentially, it runs a SQL query that takes parameters from the URL and translates them into a usable value from the SQL database.  It then passes those parameters into a PerformancePoint report.

Before we go much further, we probably also need to address potential alternatives to this approach:

  1. Custom Webpart development – as in Alex’s example.
  2. Add VBA to populate the Project UID as a custom enterprise field in Project Professional.  Add that field to the OLAP cubes as a selectable dimension.
  3. Add a custom workflow or event handler to populate the Project UID as a custom enterprise field in Project Server.  Add that field to the OLAP cubes as a selectable dimension.
  4. Use SSRS and configure it such that the Project UID is a reporting parameter.

So yes, this technique is probably a bit contrived….but it seems to work….and it involves no code, so therefore it seems worthy of discussion.

1) Create the InfoPath Infrastructure

Your first step is to create the required InfoPath libraries and to ensure the InfoPath service is configured properly in Central Administration.  I’ll go ahead and create a form and data connection library in the main PWA site – but set them so they don’t appear on the Quick Launch bar.

For more information on getting the infrastructure set up, I’ll refer you to this post.  In fact, I’d recommend that you read through that post prior to proceeding as we’ll be using many of the same techniques.

Below you’ll see the two libraries I created, creatively named “Connections” and “Forms.”

image

2) Create The Data Connection

Next, just like in that previous post, we create the data connection.  In this case, I’ll just point the connection at the EPM_ProjectUserView and grab all of the fields.  Since I documented the screenshots in that post, I skipped a number of steps to show you the screenshot at the end.

image

Convert the connection to a file and save it to the Connections library.

image

Make sure to approve the connection – as by default it’s loaded with a status of “Pending.”

3) Create a New InfoPath Form

Using the connection I just posted, I’ll go ahead and create a new InfoPath form using the Data Connection template.

image

Note that the available fields appear both as QueryFields and DataFields.  If you don’t see that, go ahead and click the Show Details option at the bottom.  If you still don’t see that, my guess is that you may have picked the wrong form template.

The way InfoPath works – or so has been explained to me – is that we populate the query fields, trigger the query, and then based on those values will return the results in the data fields.  In this case, that makes things complicated because we could, in theory, have multiple results.  Hence, the data fields typically appear in the repeating section of the form at the bottom.

Since we only ever expect a single result, we will need to implement a workaround to solve this issue.

First off, I’ll simplify the form.  Nobody will ever see it, so it doesn’t have to be pretty.

image

Next, I create two new fields by right clicking on the MyFields label and selecting the Add Option.  I’ll call them TempUID and TempProjName.  I drag the relevant fields onto the form to make the following…. (And actually, I could probably leave the form blank as we’ll never look at it, but this helps frame the discussion, I suppose.)

image

Now we need to create a set of three rules:

  1. Rule #1: Convert the value entered into the TempUID field into a format that can be used in the SQL query.
  2. Rule #2: Run the query.
  3. Rule #3: Take the resulting value in the ProjectName field, format appropriately, and copy into the TempProjName field.

Those rules will be triggered when the TempUID field is populated.  Hence, we can put all of those rules on the single field.

Rule #1: Manipulate the ProjUID field.

As I discussed in that other post, we need to change the value entered in the TempUID field to something more usable and copy it into the queryfield ProjectUID.

image

We’re going to use the following formula in InfoPath:

concat(“{“, translate(., “abcdefghijklmnopqrstuvwxyz”, “ABCDEFGHIJKLMNOPQRSTUVWXYZ”), “}”)

…where the “.” represents the TempUID field.  This formula does two things:

  • Adds round brackets around the TempUID entry, i.e. {abcdef}
  • Converts the lower case letters to upper case, i.e. {ABCDEF}

When completed, the calculation should look something like this (noting again that the field name appears as a “.”):

image

Rule #2: Run the Query

That’s pretty straightforward.  You should now have something that looks like this:

image

Rule #3: Convert the ProjectName Field to a PerformancePoint Parameter

In this case, we’re going to convert the value that shows up in the first repeating datafield within the InfoPath form.  We need to do this for two reasons: to allow PerformancePoint to consume the data and because Webpart connections cannot use fields in repeating sections.

Since PerformancePoint is looking for the project name in the format of “[Project List].[Project Name],” we also need to convert the result set into that format.

Our third rule then will run and populate the TempProjName field.

image

The formula will look like this.

concat(“[Project List].[“, @ProjectName, “]”)

image

You should now have these three rules in this order.

image

Publish the form to your Forms library.  When Publishing, make sure you promote the two custom fields so that they’re available for use in Webpart connections.

image

…and that’s the heavy lifting.  Next up….creating the PerformancePoint report and adding all of the pieces onto the PDP.

Adding Filtered Project Dashboards to PDPs (Part 1)

Adding Text Search to the Project Center Interface

Update 6/15/2012: See the comment at the bottom.

…well not really, but essentially I’ll be discussing how to create the functionality that does that.  My self-assigned goal in this post is to enable SharePoint search to perform a text search of Project Center content – and – based on that search allow the user to navigate directly to the appropriate project record or PDP within Project Server.

I will be showing you how to do this using External Content Types (ECT) and then modifying the results with InfoPath to render a dynamic URL from the text based results of the ECT.

This is a request that I’ve run into a couple of times both on client sites and on the online forums.  Generally, it’s driven by the fact that we have too many projects and it’s hard for someone to zero in on specifically the right project.  Hence, the user would like to simply type in the name or a relevant keyword into SharePoint search and go directly to the record.

As alternatives, note that Project Center does offer a custom filter option, so I could go create a onetime filter that filters on specific text in the project name.  In my experience, this is a bit slow and also the filter has to be removed and reset for the next project.  The other solution would be to create custom views that slice and dice the portfolio and make it easier to navigate (see this post on creating a My Projects view).  The functionality I’ll show you how to build today would augment that.

1) Reviewing the Requirements

Let’s start off by looking at how we will navigate to the PDPs when we finish this effort.  If you click on a project in Project Center, you should see a PDP such as the Project Information page.  Note that I am using the default Contoso demo image so my defualt page is actually the Proposal Workflow Status page.

Here’s Project Center – just to get us all on the same page….

image

…and the PDP….showing the current workflow status.

image

Take a look at the URL for the PDP….

http://project.contoso.com/pwa/project%20detail%20pages/proposalstagestatus.aspx?projuid=012441e0-573e-48f7-a518-1169c28fcde8

In that URL, we can see the URL of Project Server: http://project.contoso.com/pwa/project%20detail%20pages….

….the actual PDP: proposalstagestatus.aspx…

….and the unique ID for the project: projuid=012441e0-573e-48f7-a518-1169c28fcde8

What we’ll do then is create an ECT and manipulate the results within InfoPath to regenerate that target URL.

2) Create an External Content Type

I won’t spend a whole lot of time talking about creating ECTs, but basically they’re a method of bringing external data (in this case Project Server data stored in SQL) into the SharePoint fold.

To support this example, I will create an ECT with four fields: Project Name, UID, Project Description and Project Keywords.  I could, in theory, pull any field I wanted, which should then be subject to the SharePoint search indexing.

For more information on creating an ECT, I refer you to this post from last year.  Note that we will not need a filter as part of the ReadList operation.

3) Create an External List

I now create a site to throw the list on.  Nobody should actually have to navigate to the site.  We simply want it to be appear within the search results.  For this example, I am going to create a site called External under the BI Center.  As this site will need to be modified with SharePoint Designer, we may need to move it off of the main PWA site – which has been set to preclude any SharePoint Designer edits.

On this site, I create an external list and point it at the ECT we just created.

image

So far, so good.  Note that you can click on the project to show the default display form.  Note that we could also add metrics to this form, i.e. other values that typically appear within Project Center.

image

Next, we’ll modify the display form so that it will display the URL of the project record within Project Center.

4) Modify the Display Form

Modifying the default display form for external lists is a relatively simple exercise – provided you know one workaround.  I was able to find this in a blog post yesterday, although unfortunately, I didn’t make a note of which one.   Anyway, it’s usually quite easy to modify display forms for SharePoint lists.  All one has to do is click on the option in the Ribbon.

Unfortunately, that option doesn’t work in external lists.  The trick here, is to click on the button in the ribbon to modify the list in SharePoint Designer.

image

Once the list is open in SharePoint Designer, select the option to modify the form in InfoPath.

image

This will launch InfoPath.

image

Now, let’s create a new field that will store the calculated URL.  To do this, right click on MyFields in the Fields dialog box on the right and create a new field called URL.  Set it as a hyperlink field type.

image

Drag the new field over to the form.  I also tidied up the form a bit by removing the UniqueID and cleaning up some of the label names.  I also converted the Description text box to a multiline display.

image

Now right click on the newly created field.  We’re going to modify the default value to display the appropriate URL.

image

Select the Properties option, then the button to edit the default calculation.

image

Once there, I’ll add the following custom formula:

image

Hit Ok, and publish the form back up into the InfoPath list.  You may have to save the template somewhere prior to publishing.  It doesn’t matter where you save it.

image

5) Reviewing the Results

Let’s take a look at the results.  To do that, navigate back to your external list.  Refresh it for good measure.  Click on one of the projects to review the results:

image

Click on the URL to confirm it navigates back to the appropriate place in Project Center.

image

You now have an extended version of the Project Center navigation structure accessible via enterprise search that allows users to easily find projects.  You may want to investigate reconfiguring your search and perhaps adding a custom scope to hit the external list, but that’s a topic for another blog.

  image

Adding Text Search to the Project Center Interface

Making a Read Only PDP with InfoPath Forms

The genesis of this post lay in a conversation I had with a client.  The goal of the discussion was to identify a method to lock down specific project detail pages (PDP) within Project Server.  I figure that there’s probably a number of ways to accomplish this, but since I had been playing with InfoPath lately, this seemed to be the most logical choice.

From a process standpoint, the locked down PDP would enable a PMO to set specific fields at the project initiation, and then either prohibit or strongly discourage project managers to modify these fields.  If you’ve used Project Server for any time, of course, you’ll know that there is no true field level security….once a user can edit the project in Microsoft Project Professional, all of the fields may be edited.  But in some cases, perhaps where the project manager is primarily interacting with the project within the browser, this may be an effective way to control fields.

For other techniques that would probably be used in conjunction with this, I would encourage you to take a look at this post on security trimming PDPs, and some of my early posts of extending PDP pages into SharePoint lists.  At some point, I’ll probably come up with a proposed solution for leveraging SharePoint lists to provide secure extensions of Project Server data.

The Challenge

So the challenge I set before myself was to replicate a PDP page, but to make it read only.  At the end of this experiment I wanted to be able to pull up a PDP page, and have it render Project Server data without requiring any further input from the user.  My conclusion is that it’s not all that hard to accomplish this, albeit it requires a couple of tricks to get the output of the Query Filter Webpart to match the data requirements of the database query.

Preparing the SharePoint Site

The first thing we need to do is to add two libraries to our main PWA site: a data connection library and a forms library.  The data connection library will contain the required UDC file to link our form to the Project Server database.  The forms library will contain the form for our PDP pages.

You’ll find the options to create both libraries after selecting the View All Site Content option under Site Settings, then clicking Create and Libraries.

image

As a useful trick, you may note an InfoPath form maps to a specific content type within the form library.  If you plan on deploying this solution with different InfoPath forms, you’ll probably want to enable content type management within the forms library and then create a couple of extra content types.

Once you’ve created those two libraries, you’ll probably want to confirm that InfoPath is in fact configured within your SharePoint farm.  To do that, you’ll have to navigate to the Central Admin site.  Click on the General Application Settings.

image

Click on Configure InfoPath Forms Services.  Assuming that this does not violate your organizational security policies, go ahead and check the last three check boxes.  Click OK.

image

Close Central Admin.

Creating the Data Connection

To create the data connection, let’s open a blank form in InfoPath Designer.

image

On the Data tab, click on the Data Connections button.

image

Follow the default options to create a new data connection file to receive data from a SQL database.  For this example, I will be using the EPMProject_UserView table from the Reporting database.

image

Select the fields that you will require.  For this post, I’ll just go ahead and select all the fields in the view.

image

Complete the wizard.  Make sure to deselect the last checkbox on the last screen.

image

Once you get to the Data Connections dialog box, select to convert the newly created data connection to a connection file.

image

Post the connection file to the connection library created when we started this process.  Close InfoPath and navigate to the connection library.

image

As versioning is turned on within this library by default, you may need to click on the drop down arrow next to the connection file and set it to approved.

Configuring the InfoPath Form

Our next step is to create the InfoPath form.   With the data connection created, this is actually quite simple – with one minor caveat.  We need to add a mechanism to the form to manipulate the data received from the query string filter and convert it into something recognizable by the form query.

Create a new form with the Data Connection File template.  You could probably use any template, but this is the one that seemed the most appropriate when I was writing this post.

image

Connect it to the data connection created previously.

image

You should end up with a screen that looks like this.  Note the data fields on the right hand side of the screen.

image

InfoPath fields show up in two flavors: query and data.  The query fields are used to query the database….the data fields yield the results.  So our goal is to add the Project UID field to the appropriate query field, trigger the query, and show the results in the data fields at the bottom of the form.

You may wish to play with various permutations of this form, but the simplest way to emulate the PDP functionality is to delete everything but the data field box.

image

Now add a custom field called TempUID to the form data set by right clicking on the myFields item in the right hand dialog box.  This will create a field called my:TempUID.

image

Now click on the option to Manage Rules in the Ribbon.  Select the TempUID field, and click the option to set a New rule.

image

Set the rule to set the query field ProjUID whenever the TempUID field is changed. (Not the data field). The format for the data will appear as follows:

concat(“{“, translate(., “abcdefghijklmnopqrstuvwxyz”, “ABCDEFGHIJKLMNOPQRSTUVWXYZ”), “}”)

…where the “.” represents the TempUID field.  This formula does two things:

  • Adds round brackets around the TempUID entry, i.e. {abcdef}
  • Converts the lower case letters to upper case, i.e. {ABCDEF}

If you don’t add this formula, then the query will return an error stating that the ProjUID field is invalid data.  When completed, the calculation should look something like this (noting again that the field name appears as a “.”):

image

Add a second rule to the same field to query for data after the first rule is run.

image

Going back to the form, drag the desired fields to the data field box.

image

Save the form, and publish it to the form library created at the beginning.  When publishing the form, promote the TempUID field so that it may be used in Webpart connections.

image

After publishing the form, close InfoPath.  You are now ready to create the Project Detail Page.

Creating the PDP

Back in Project Server, navigate to the Project Detail Pages under Server Settings.  Add two Webparts to the page, the Query Filter web part and the InfoPath form.  Edit the Query Filter Webpart to pull the ProjUID parameter.

SNAGHTML2154f81

Configure the InfoPath form to pull the Query String Filter into the TempUID field we promoted when we published.

image

…and that’s it.  Add the PDP to your project type, and it will automatically display Project Server data whenever the page is viewed.

SNAGHTML21954ce

Yes, I admit the form is kind of ugly – and that description field got truncated, but dressing up InfoPath forms is probably a decent topic for another post.  For now, we got the data to show up – which I think is pretty good progress.

Credits

To give credit where credit is due….thanks very much to Jimmy McAnally (@jimmymcanally), a former colleague.  I ran into him at a recent Houston SharePoint User Group meeting, and he graciously spent a couple of hours helping me with a different hypothetical InfoPath question.  In the process, he ended up teaching me enough about InfoPath to get me started down this path.  Also thanks to Clayton Cobb (@warrtalon), SharePoint MVP, for graciously attempting to help me out on the online forums – despite my near-incoherence in being able to actually describe the issues I was facing.  I’ll give them credit for everything that actually works in this post and I would encourage you to blame me for any incorrect or misleading information.

Making a Read Only PDP with InfoPath Forms