EPMSource

In the first part of this series, we looked at using Playbooks to move demand management assets from one server to another. Whilst Playbooks is a very useful tool, it does have one limitation in that it moves all the chosen settings over, so if you wish to export a single EPT and its PDPs, you’re out of luck.

Luckily Microsoft released another tool as part of its ‘solution starters’ series that allows the selective exporting and importing of demand management assets. The solution starter consists of two components, the DMExport utility that is used to export the settings and the DMImport code and scripts that contains all that is needed to turn the exported items into a SharePoint 2010 feature for installation.

As with all solution starters, there are two components available, the compiled code mainly ready for deployment in your environment, and the full source code so you can see how it actually works and use it as the basis for future enhancements. For most solution starters, the documentation is either sufficient or the solution simple enough to install and use. Unfortunately in the case of the DMImport /DMExport solution starter, the documentation is a bit lacking and you need to do a fair bit to get it up and running, so hence this post

1. Download

Before we start, you will need to download both the Deployment and Source zip files from the solution starter site. The starters are updated on a very regular basis, so for the purposes of this post, I am using the release from late December 2010.

2. Install and use the DMExport Utility

In order to export the demand management assets, it is necessary to install a small utility on the server you wish to export from. Whilst the solution starter provides the source, the easiest way is to use the neatly packaged setup.exe that is provided in the deployment project. This will install the DMExport utilise to the c:\Program Files\Microsoft\DMExport directory ready for use.

Just like Playbooks, when you first run the DMExport tool it will request the address of the Project Server instance to script the configuration settings out of. Once a connection is established the GUI will display the various demand management items that can be selected and this is where the real advantage of the tool comes in.

Unlike playbooks where you script all the workflow settings, the DMExport tool allows you to choose what you wish to export and will attempt to determine the relevant associated assets to include. Now from my experience the autocomplete is pretty good but has one flaw, it forgets to associate the workflow status PDP’s (Proposal Stage Status in the above screenshot), which if you don’t include, the restore will fail as it is a mandatory field when creating an Enterprise Project Type.

Before hitting the export button, make sure to populate the ‘Feature Name’ which will eventually become the name of the SharePoint feature that is used to install the assets on the target server and to set the location that the data will be exported to.

Once the export process has completed, you should see a set of files like those below in your export directory.

These files contain the relevant data for the items selected for export and will eventually be turned into a SharePoint feature.

3. Building the DMImport components

The DMImport component consists of two parts, an import library that reads the exported files from above and updates the relevant components within Project Server 2010, and some scripts used to build a SharePoint feature that is used to deploy the assets. The feature when activated calls the import library via a feature receiver.

Before the DMImport scripts can be used it is necessary to build the import library and register it on the destination server. The source code zip file contains everything needed to build the import library. Simply open the project in Visual Studio 2010 and build the project. Once the build is complete the following DLL should be created:

To register the DLL, use the gacutil.exe file located in the DMImport\Deployment directory of the source zip file and the following command:

Gacutil.exe /i <path>\Microsoft.SDK.Project.Samples.ImportDemandManagement.dll

4. Build the feature

Creating the feature itself that contains all the demand management assets is relatively simple. To start, copy the files created in the export location to a new directory on the restore server as well as the contents of the \Solution Starters\EPT_Export_Import\DMImport\Deployment directory .

To build the feature, in the SharePoint 2010 Management Shell, enter the following command:

./InstallFeature.ps1 <directory of the exported assets, including a trailing />

This will kick off the PowerShell script and generate the feature by copying the exported files and building a feature.xml that pulls them all together and associates the feature receiver built above.

If all goes well you should see something like this in the output:

The red error is fine, it is caused by running the script in a SharePoint Management shell instead of a plain old PowerShell shell.

On seeing the ‘Done.’ above, the feature will be generated and installed on the destination server. To check, go to Site Actions > Site Collection Features on the destination server and the newly generated feature will be there awaiting activation.

You may notice the associated image is broken, there looks to be a bug in the script which I haven’t looked at yet.

5. Activate and use the feature

The demand management assets will not be installed until the feature is activated. On clicking Activate, SharePoint will activate the feature receiver and via the import library create the relevant assets inside Project Server. The activation process can take a while to complete, especially if you are importing a lot of assets. In addition, the first time you activate the feature it will create a special logging document library on the destination server that is used to store logging information and a set of XML files describing the changes made.

The latter are very important if you later choose to deactivate the feature as they outline how to roll back the changes and return the Project Server back to how it was before activation.

Again, the documentation is not exactly clear on the library, but for reference it is created at http://<server>/<projectserverinstancename>/DMImport.

6. Deactivating and removing the feature

Should you need to roll back the changes and uninstall the feature, simply Deactivate the feature via Site Actions > Site Collection Features, then in the SharePoint Management Shell enter the following command:

Uninstall-spfeature –Identity <featureName>

Over the Christmas break there was a question in the Project forums around how to deploy the various demand management assets from Development to Test to Production in a controlled manner, i.e. not manually. When I say demand management assets I mean things like Enterprise Project Types, Workflow Stages, Workflow Phases, Project Detail Pages, Web parts, Project Custom Fields and Workflow Proxy settings, i.e. all the bits that your Demand Management workflow needs to operate correctly, but not the necessarily the workflow itself. As you can imagine when you start to develop big complex workflows for your project management process, the number of stages and PDP’s could become quite arduous to manage.

Luckily MS provide two tools that can assist in packaging and moving these assets between environments which I am going to cover off in two posts. In this one, I will be looking at a tool called Playbooks, which allows you script out all the configuration settings from one environment and import them into another. In the next post, I will look at the DMImport / DMExport solution starter and provide a walkthrough of how to build, install and use it.

Getting started

Playbooks is the de-facto tool when it comes to moving Project Server configuration settings between environments. The tool interrogates the Project Server configuration settings and scripts them out into an XML file that can then be replayed back into a restore server.

For the purposes of this post, I am going to concentrate on moving only the demand management components, and will leave the general use of the tool and the various nuances to Andrew Lavinsky’s post.

Before we are in a position to run the tool, there are a few pre-requisite steps that need to be completed on the server being backed up.

1. Save any custom project workspace templates that may be referenced in your Enterprise Project Types
2. Save any project templates you may have associated with an Enterprise Project Type
3. Determine the UIDs of the project templates, this will be needed later on to map the template UIDs to the UID’s in the restore server. This last step can be a little tricky as there is no ‘simple’ way of getting the UID of a template. The easiest way I have found is to run the following query against the Draft DB.

 select PROJ_UID as [Project GUID],
PROJ_NAME as [Project Name],
PROJ_TYPE as [Project Type]
from MSP_PROJECTs where PROJ_TYPE = 1 

Once completed, the saved workspace template and project templates need to be installed on the restore server. In the case of the workspace template, this is as simple as Site Actions > Site Settings > Solutions and uploading the various project workspace templates into the gallery. The project templates require a little more work and will need you to use Project Professional connected to the restore server and to open and subsequently save each schedule template to the restore server, not forgetting to choose the ‘Template’ type in the Save to Project Server dialog.

Once the templates have been saved into the restore server, run the above SQL Query again and you will have the new UIDs of the templates needed for mapping in the playbooks file.

Backing up

To back up the demand management settings on the source server, it is necessary to install Playbooks onto the server itself. Once started, you will need to point the tool at the backup Project Server instance, in the case below http://project.contoso.com/PWA.

The tool will then provide a list of all the settings that can be backed up for the user to select from. As we are only interested in the demand management or workflow components, check the Workflow setting in the tool as highlighted below.

You will notice that several Enterprise Data options will also be checked automatically to ensure the correct reference data is also backed up. Also remember to provide a Filename to save the configuration settings into, this file will then be moved onto the restore server. Once the backup has completed you will see something like this including a link to the log for troubleshooting if needed.

Mapping the Templates.

Before the backup can be restored, it is necessary to modify the backup file slightly to map the new project schedule template UIDs to the old ones. This should be as simple as doing a find and replace using the UIDs from the backup sever and replacing them with the corresponding UIDs from the restore server. If you don’t do this step, the restore will fail with an “EnterpriseProjectTypeInvalidProjectPlanTemplateUID” error.

Restore

After transferring the updated mapped XML file to the restore server, the restore process is very similar to that of the backup, simple select the PS instance to restore the demand management settings to, load up the mapped XML file, select ‘Replace’ as the restore strategy and click on restore. Once again, it is important that Playbooks is run on the restore server itself and not remotely, even though the tool will allow it, otherwise the restore will fail.

If all goes well and you have completed the pre-requisites successfully, the demand management assets should now be restored to the server, and visible in the server settings,  ready to be used with your workflows.

In the next post, I will look at DMImport & DMExport, a ‘solution starter’ that approaches the problem in a slightly different manner, instead of picking up all the demand management assets in one go, it allows you to selectively export the assets, so you can choose which assets you want to deploy, not all of them.

A common request from people that develop workflows within Project Server is how to create a simple approval process for a project to transition a stage gate, so I thought I would write a blog post covering off how you can achieve it with Nintex Workflow for Project Server.

The stage gate logic I am going to use, is outlined in the flow chart below.

The approval will give the user two options, either approve and move into the next stage of the project, or reject the approval and stay in Stage 1.

Before we get to the approval part, the workflow logic needs to set a few things up. The initial stage, some status information, and to populate a workflow variable with the project name (which we will use in the approval later on).

 

This workflow makes use of a number of variables to store information read from the project as well as variables about the state of the project.

Next up, before we get into the rejection loop we are going to set the Stage Approval variable to ‘No’. This variable is important as when it is set to ‘Yes’ it will break out of the loop and continue into the next stage.

 

Once the variable is set, we can move into the loop.

 

Setting the loop up is really simple, basically we want to ensure the loop continues to loop whilst the Stage Approval variable is equal to ‘No’.

Once we are in the loop the first action is to ‘Wait for a Submit’, this ensures that we only proceed to create an approval if the user has clicked on submit for the project. Next we read in the users we wish to send the approval to, in this case we are using the ‘Read Project Security Group’ action, set to read in the Project Server ‘Portfolio Managers’ group and to store the information in the Portfolio Managers variable.

In the example above, I have also included a ‘Set Status Information’ action to update the Status Information for the user and let them know that the workflow is ‘Waiting for Approval’.

Next we need to actually set up the task. In this example, I have used a Nintex Flexi Task, which has a number of advantages over a normal to-do task:

• We can assign as many approval options as we want (not just two!!)
• There is out of the box logic to collate the results (majority, first past the post etc)
• It provides downstream options at design time for further actions Of course, just like a normal task list in Nintex, you also get the value of highly customised notifications, escalations, delegation and reminders all out of the box and really simple to configure.

For our purposes, the flexi task will be configured to assign a task to the users we retrieved into the Portfolio Managers variable earlier.

You can also see that the Task name has been configured to create tasks named ‘Stage Gate Approval : <Project Name>’ using the Project Name we retrieved into a variable earlier on in the workflow. For this blog post, I am not going to set up the other options such as Reminders and Escalation.

Next we need to configure the various actions to perform depending on the outcome of the approval.

 

As you can see above, if the task is approved, the Stage Approval variable is set to Yes, which causes the workflow to exit the loop, however if the task is rejected, the Status Information is updated to update All Approver Comments and the loop starts again, going back to wait for a Project submit.

Finally, all that is needed is to add the actions to set the next stage once we have exited the loop.

As you could imagine, the whole workflow itself can get quite long, so to that end I have uploaded an image of the whole workflow which you can download here.

So what does it look like in action?

Once a project has been created that uses the workflow, a task will be created in the Project Server Workflow Tasks list for each of the users that are part of the Portfolio Managers group. As you can see in the screenshot below, the task follows the naming convention we set up above.

Clicking on the task will bring up the default flexi task approval box (for those of us a bit more adventurous this can be customised with InfoPath 2010 to make it look as sexy as you want).

 

As you can see, the flexi task has automatically added links to the Workflow Status and Project Details pages so the reviewer can view the relevant information before they make a decision.

So as you can see, setting up a stage gate approval in NW4PS is pretty simple and this one took about five minutes to build from scratch. Of course if you are going to have many stage gate approvals you may want to consider moving the actions into an action set, or saving the approval as a snippit to make it easier to view and reuse. You could also look at extending the workflow to add some extra logic to handle those cases if the read security group action fails to determine any users, using a run if action. There are a wealth of possibilities, so get coding

Arguably one of the most exciting features in Project Server 2010 was demand management or project lifecycle management (PLM). What PLM provides is a set of tools within a Project Server 2010 instance to manage the whole project lifecycle, from the concept of project phases and stages, a mechanism to collect and display data as you move through the project and best of all, a workflow engine. Out of the box, the development of workflows for use in PLM can be quite arduous. Unlike the rest of SharePoint, workflows for PS2010 cannot be built with SharePoint Designer, instead organisations have two options:

• Leverage the Dynamic Workflow Solution Starter, a Microsoft released tool that enables simple approval type linear workflows to be built from within a web page without any code; or
• Get a .net developer to use Visual Studio 2010 to design, develop and test a bespoke workflow that meets all the organisational requirements.

However, there is a third option, enter Nintex Workflow for Project Server 2010 (NW4PS), an extension of the popular Nintex Workflow 2010 (NW2010) which brings a simplified drag and drop interface for building workflows and integration into the Project Server workflow components to users via the web browser.

So how does NW4PS stack up?

Installation

The installation of NW4PS is relatively painless and well documented. NW4PS requires two components to be installed, NW2010 which provides the base workflow functionality and NW4PS which provides the Project Server integration. Once installed and activated, two sections will be added to Central Administration where the various components of NW2010 and NW4PS can be configured.

Additionally two new options will be added to the Site Actions menu to provide end user access into NW2010 and NW4PS features.

Creating a Workflow

The true power of Nintex Workflow for Project Server is in the simplicity of creating workflows and this is evident in the workflow designer that uses a web based drag and drop interface to create workflows.

The left hand side of the screen provides a menu of actions that can be used within the workflow, each logically grouped and searchable. To use an action, simply drag and drop it onto the design surface.

Once on the design surface, each workflow action needs to be configured, allowing the user to set the various parameters using simple dialog boxes. As you can see in the diagram below, setting the project stage is as simple as selecting it from a combo box, instead of having to dig around for a stage GUID like you have to do with Visual Studio. In addition, if as you are building the workflow you realise you have forgotten a stage, a handy link is provided to enable you to create a new stage from within the tool.

For the purpose of this post, I decided to develop a simple branching workflow that collected some information for a proposal, then performed a validation and either approved or rejected the proposal based on the a predefined value, the exact same branching workflow described in this MSDN article.

The whole process of creating the workflow by dragging and dropping the workflow actions on to the design surface, configuring each action and then saving the workflow took a little under five minutes to complete.

Deploying a workflow

With Project Server workflows that are developed in Visual Studio, it is necessary to create a workflow solution package within VS2010 and then deploy the solution into Project Server. With Nintex, the process of deploying is as simple as clicking on the Publish button.

Once published, the workflow will be available in the EPT configuration screen to associate it with a specific Enterprise Project Type.

Running a workflow

Running a NW4PS workflow is exactly the same as you would expect for a traditional Project Server workflow, simply create a new project of the configured EPT and the workflow will kick off. As you would expect, the Project Detail Page (PDP) infrastructure of Project Server will kick in and the various pages will be displayed as configured for the current stage. Similarly, the normal PDP workflow overview page will continue to show the status of the workflow in a table, however Nintex also provides a handy visualisation for the workflow which makes it simple to see where a particular project is in the workflow process.

Ok, so what else can you do?

The true benefit of NW4PS other than the reduced time to develop and ease of deployment is the sheer number of preconfigured workflow actions you can draw onto make the workflow as functional as possible. Such scenarios as:

• Sending weekly reminders to Project Managers to complete their status reports whilst the project is in execution;
• Creating tasks within the project by using a workflow controlled PSI call;
• Alerting stakeholders that are not users of PS via an email of upcoming tasks / milestones;
• Performing queries against the PS Reporting Database and use the data returned within the workflow; and
• Link document approval workflows into PS workflows, ensuring that documents are approved before the PS workflow progresses are all possible, really taking Project Server Project Lifecycle Management capabilities to the next level.

Disclosure: I am an employee of OBS, a member of the Nintex group. However, this did not inform this post, I really do think it’s a great product

In the previous two posts, we covered off how to extract the workflow data from the Project Server Reporting database, and the basics of how to extend the Backstage view. In this post I will cover off how to merge the two together, and to discuss some of the limitations and problems I ran into.

For the proof of concept I wanted the backstage view to render dynamic information about the project workflow in the backstage, checking out MSDN and Technet I found some fantastic resources to assist, including Customizing the Office 2010 Backstage View for Developers and it’s associated code . I have always found the best way to learn something was to look at some form of reference and then experiment.

In dissecting the example above, I found numerous references to things called callbacks, these are methods that are called when the backstage is rendered. They are particularly useful if you want to incorporate dynamic information such as the workflow status. Now after a significant amount of experiment I had absolutely no luck in getting any of these callbacks to work in the beta, so decided to try something a little bit different (I intend to revisit these callbacks once Project has RTM’d and hopefully there is a little more reference material available).

As the XML that renders the backstage is built programmatically in Project, I figured it would be possible to inject the data retrieved containing the workflow status directly into the XML. This does have the limitation that the data displayed in the backstage will only be as fresh as when the XML is built. If you intend on using this approach you need to take this limitation into account and ensure its relevant for the data you are displaying.

Getting the data programmatically

In the previous post I discussed the code required in order to retrieve the workflow status information from the reporting database. In order to make that data available we need to read the data into a record set using ADODB and point the code at the correct database to run against. To do this we first need to get the GUID of the current project, which can be done using the following code:

    Dim projectGUID As String

    projectGUID = ActiveProject.GetServerProjectGuid

To use ADODB, you need to include a reference to ADODB in the project, or use something called late binding to bind in the ADODB libraries at runtime. For the purposes of this proof of concept, I explicitly added the the ADODB library in the project references.

Finally I added some error handling to check whether any data was returned. If no data is, the code assumes the project is not under a workflow and stops our custom workflow tab from being displayed.

Sub qrySQLDatabase()

    ' First do a lookup to get the current project GUID
    Dim projectGUID As String

    projectGUID = ActiveProject.GetServerProjectGuid

    'Assume the project is in Project Server unless we get an error.
    blnWorkflowRecords = True
    Dim wfRS As ADODB.Recordset
    Set wfRS = New ADODB.Recordset

    ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=PWA_moss.contoso.com80_Reporting;Data Source=demo2010a;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DEMO2010A;Use Encryption for Data=False;Tag with column collation when possible=False"

    ' Now we have the Project GUID, we need to query the reporting database to get the workflow status for that guid

    sqlQry = "select  epmp.ProjectName, " & _
            "epmwfs.StageName," & _
            "epmwfs.StageDescription, " & _
            "epmwsi.StageInformation, " & _
            "epmwfst.StageStateDescription " & _
            "from MSP_EpmWorkflowStatusInformation epmwsi " & _
            "inner join MSP_EpmWorkflowStage epmwfs on epmwsi.StageUID = epmwfs.StageUID " & _
            "inner join MSP_EpmWorkflowStatusType epmwfst on epmwsi.StageStatus = epmwfst.StageStatusID " & _
            "inner join MSP_EpmProject epmp on epmwsi.ProjectUID = epmp.ProjectUID " & _
            "where epmwsi.ProjectUID = '" + projectGUID + "' " & _
            "and (StageEntryDate is not null and StageCompletionDate is null) " & _
            "order by StageOrder"

    wfRS.Open sqlQry, ConnectionString, adOpenForwardOnly, adLockReadOnly, adCmdText

    If (wfRS.BOF Or wfRS.EOF) Then

        'No records are returned, so assume that the schedule is not in Project Server
        blnWorkflowRecords = False

    Else
        ' HACK
        ' move the values into the strings and append a space on the end.
        ' If there is an empty string the backstage won't render.

        txtProjectName = wfRS(0).Value + " "
        txtWorkflowStatus = wfRS(4).Value + " "
        txtWorkflowTitle = wfRS(1).Value + " "
        txtWorkflowDesc = wfRS(2).Value + " "
        txtWorkflowErr = wfRS(3).Value + " "

    End If

    wfRS.Close

    If Not wfRS Is Nothing Then Set wfRS = Nothing

End Sub

You’ll notice above that once the data has been retrieved it is extracted into a number of txt variables such as txtProjectName, txtWorkflowStatus etc which will be used to inject the data into the XML for the backstage. Unfortunately the backstage doesn’t handle empty strings very well, so I have appended a space to each variable to make sure no null strings are injected.

Injecting into the backstage

The process of injecting the variables into the backstage is incredibly easy, all that needs to be done is to break up the XML building and insert the various variables as per below:

Private Sub AddWorkflowBackstageTab()

    Dim backstageXML As String

    backstageXML = "<customUI xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">"
    backstageXML = backstageXML + " <backstage>"
    backstageXML = backstageXML + "  <tab id=""TabWorkflow"" label=""Workflow"" insertAfterMso=""TabInfo"" >"
    backstageXML = backstageXML + "    <firstColumn>"
    backstageXML = backstageXML + "      <group id=""grpWorkflow"" label=""Current project workflow status"" style=""" + txtGroupStyle + """>"
    backstageXML = backstageXML + "        <primaryItem>"
    backstageXML = backstageXML + "          <button id=""btnFeature"" label=""View workflow in PWA"" onAction=""workflowStatus"" imageMso=""ProjectWebAccessProjectDetails""/>"
    backstageXML = backstageXML + "        </primaryItem>"
    backstageXML = backstageXML + "         <topItems>"
    backstageXML = backstageXML + "             <labelControl id=""workflowStatus"" label=""Current status: " + txtWorkflowStatus + """/>"
    backstageXML = backstageXML + "             <labelControl id=""filler""  label="" "" />"
    backstageXML = backstageXML + "             <labelControl id=""currentError"" label=""" + txtWorkflowErr + " "" />"
    backstageXML = backstageXML + "             <labelControl id=""filler2""  label="" "" />"
    backstageXML = backstageXML + "             <labelControl id=""currentTitle""  label=""" + txtWorkflowTitle + """ />"
    backstageXML = backstageXML + "             <labelControl id=""currentDesc""  label=""" + txtWorkflowDesc + """ />"
    backstageXML = backstageXML + "         </topItems>"
    backstageXML = backstageXML + "       </group>"
    backstageXML = backstageXML + "    </firstColumn>"
    backstageXML = backstageXML + "  </tab>"
    backstageXML = backstageXML + " </backstage>"
    backstageXML = backstageXML + "</customUI>"

    ActiveProject.SetCustomUI (backstageXML)

End Sub

Pulling it all together

Finally before the workflow tab can be used, a few pieces of logic need to be added to make sure it displays only when there is data, or when the client is connected to the Project Server instance we are querying the reporting database of.  As you can see in the backstage XML, I also included a button that takes the user off to the Project Web Access Workflow status page, so it was necessary to create a subroutine to perform this.  Finally I added some logic to set the backstage group to show the yellow warning style  if the workflow status had the word ‘Waiting’ within it. The full final code looks like this:

Public txtWorkflowStatus As String
Public txtWorkflowTitle As String
Public txtWorkflowDesc As String
Public txtWorkflowErr As String
Public txtProjectName As String
Public txtGroupStyle As String
Public blnWorkflowRecords As Boolean

Private Sub AddWorkflowBackstageTab()

    Dim backstageXML As String

    backstageXML = "<customUI xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">"
    backstageXML = backstageXML + " <backstage>"
    backstageXML = backstageXML + "  <tab id=""TabWorkflow"" label=""Workflow"" insertAfterMso=""TabInfo"" >"
    backstageXML = backstageXML + "    <firstColumn>"
    backstageXML = backstageXML + "      <group id=""grpWorkflow"" label=""Current project workflow status"" style=""" + txtGroupStyle + """>"
    backstageXML = backstageXML + "        <primaryItem>"
    backstageXML = backstageXML + "          <button id=""btnFeature"" label=""View workflow in PWA"" onAction=""workflowStatus"" imageMso=""ProjectWebAccessProjectDetails""/>"
    backstageXML = backstageXML + "        </primaryItem>"
    backstageXML = backstageXML + "         <topItems>"
    backstageXML = backstageXML + "             <labelControl id=""workflowStatus"" label=""Current status: " + txtWorkflowStatus + """/>"
    backstageXML = backstageXML + "             <labelControl id=""filler""  label="" "" />"
    backstageXML = backstageXML + "             <labelControl id=""currentError"" label=""" + txtWorkflowErr + " "" />"
    backstageXML = backstageXML + "             <labelControl id=""filler2""  label="" "" />"
    backstageXML = backstageXML + "             <labelControl id=""currentTitle""  label=""" + txtWorkflowTitle + """ />"
    backstageXML = backstageXML + "             <labelControl id=""currentDesc""  label=""" + txtWorkflowDesc + """ />"
    backstageXML = backstageXML + "         </topItems>"
    backstageXML = backstageXML + "       </group>"
    backstageXML = backstageXML + "    </firstColumn>"
    backstageXML = backstageXML + "  </tab>"
    backstageXML = backstageXML + " </backstage>"
    backstageXML = backstageXML + "</customUI>"

    ActiveProject.SetCustomUI (backstageXML)

End Sub

Private Sub Project_Open(ByVal pj As Project)

    ' Initialise the boolean that indicates if there are workflow records returned
    ' in the SQL query of qrySQLDatabae
    blnWorkflowRecords = True
    qrySQLDatabase

    ' Check to see if there are any workflow data returned, or as our connection settings are hardcoded
    ' make sure we are connecting to the correct Project Server instance.
    '
    ' If any of the above are not correct, then don't show the workflow tab.

    If (blnWorkflowRecords and Application.Profiles.ActiveProfile = "Contoso") Then

	' If the workflow status is waiting, set the group style to warning
         If (InStr(txtWorkflowStatus, "Waiting") = 0) Then
       	    txtGroupStyle = "normal"
	 Else
	    txtGroupStyle = "warning"
	 End If

	AddWorkflowBackstageTab
    End If

End Sub

Sub workflowStatus()
' Opens the project details page which will default to the workflow status
    Application.OpenServerPage (pjServerPageProjectDetails)
End Sub

Sub qrySQLDatabase()

    ' First do a lookup to get the current project GUID
    Dim projectGUID As String

    projectGUID = ActiveProject.GetServerProjectGuid

    'Assume the project is in Project Server unless we get an error.
    blnWorkflowRecords = True
    Dim wfRS As ADODB.Recordset
    Set wfRS = New ADODB.Recordset

    ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=PWA_moss.contoso.com80_Reporting;Data Source=demo2010a;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DEMO2010A;Use Encryption for Data=False;Tag with column collation when possible=False"

    ' Now we have the Project GUID, we need to query the reporting database to get the workflow status for that guid

    sqlQry = "select  epmp.ProjectName, " & _
            "epmwfs.StageName," & _
            "epmwfs.StageDescription, " & _
            "epmwsi.StageInformation, " & _
            "epmwfst.StageStateDescription " & _
            "from MSP_EpmWorkflowStatusInformation epmwsi " & _
            "inner join MSP_EpmWorkflowStage epmwfs on epmwsi.StageUID = epmwfs.StageUID " & _
            "inner join MSP_EpmWorkflowStatusType epmwfst on epmwsi.StageStatus = epmwfst.StageStatusID " & _
            "inner join MSP_EpmProject epmp on epmwsi.ProjectUID = epmp.ProjectUID " & _
            "where epmwsi.ProjectUID = '" + projectGUID + "' " & _
            "and (StageEntryDate is not null and StageCompletionDate is null) " & _
            "order by StageOrder"

    wfRS.Open sqlQry, ConnectionString, adOpenForwardOnly, adLockReadOnly, adCmdText

    If (wfRS.BOF Or wfRS.EOF) Then

        'No records are returned, so assume that the schedule is not in Project Server
        blnWorkflowRecords = False

    Else
        ' HACK
        ' move the values into the strings and append a space on the end.
        ' If there is an empty string the backstage won't render.

        txtProjectName = wfRS(0).Value + " "
        txtWorkflowStatus = wfRS(4).Value + " "
        txtWorkflowTitle = wfRS(1).Value + " "
        txtWorkflowDesc = wfRS(2).Value + " "
        txtWorkflowErr = wfRS(3).Value + " "

    End If

    wfRS.Close

    If Not wfRS Is Nothing Then Set wfRS = Nothing

End Sub

In Conclusion

Once all of the above is done, the finished backstage looked like this..

The workflow data has been successfully pulled from the server, rendered in the backstage and had business logic applied, all with a minimum of coding. Of course this is only a simple proof of concept, with a little more effort the backstage could be wired up to use the PSI, integrate with VSTO add-ins and hooked into other data sources and systems, making it an incredibly powerful addition to Project 2010.  As the solution is VBA based, it can be added to the Enterprise Global for your Project Server instance and distributed to all users via this mechanism, making it very easy to distribute and control. As for the code, well it was a proof of concept and  certainly there needs to be a few improvements around the ADODB to remove the need to add the library via the Project References, but this should be relatively minor to implement. Once Project RTM’s I will certainly be revisiting the callbacks to see if I can get them working and post an update.

In this second post covering my proof of concept to expose workflow status information in the Project backstage I will cover off some of the basics on how to extend the backstage.

Like all of the Office 2010 clients, Project’s backstage can be customised by defining the various interface elements and their associated behaviours using an XML schema that has been published by Microsoft and is available for download here.

Perhaps the easiest way to understand the XML is to view an example. The XML below defines a new tab in the backstage called “Hello World” which consists of a single group called “Hello World Example” and has a single button that when clicked will call some arbitrary code called, yes you’ve guessed it, “HelloWorld”

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
     <backstage>
      <tab id="TabHello" label=”Hello World”>
        <firstColumn>
          <group id="grpOne" label="Hello World Example" helperText="Click here for some Hello World Action">
            <primaryItem>
              <button id="firstButton" label="Hello World" onAction="HelloWorld" />
            </primaryItem>
           </group>
        </firstColumn>
      </tab>
     </backstage>
    </customUI>

The backstage also allows out of the box tabs to be extended such as the ‘Info’ or ‘Save & Send’ tabs. It is also possible to insert and position customisations within the out of the box tabs. To do so, there are a couple of special XML elements you need to be across. In the example above, the line:

<tab id=”TabHello” label=”Hello World”>

could be replaced with

<tab idMso=”TabInfo”>

to add the Hello World group and button the out of the box Info tab.

The idMso element tells the backstage that a Microsoft Office Control Id should be used, in this case the Info tab. Similarly through the addition of the insertAfterMso element in the group section, it is possible to define where our new group is inserted in the tab in relation to the other groups on the tab. Like idMso, insertAfterMso uses a ControlId to ‘navigate’ it’s way around. Microsoft have published a list of these Control Id’s for all of the Office 2010 clients that you can download here.

Thinking Binary…

In most of the Office 2010 clients, the custom XML used to define the backstage enhancements can simply be added into the document assembly and would be automatically picked up when the document was loaded. Unfortunately, as Project is still using a binary file format, this is not possible and the XML backstage definition needs to be added via code using a method called setCustomUI.

Using setCustomUI is relatively simple to implement, once the XML has been defined, all that needs to be done is to transfer the XML into a variable and to call setCustomUI to implement it.  There are a couple of things you need to be careful of when encoding the XML:

• Make sure you change all single quotes to double quotes
• The backstage will fail to render if any of the elements have the incorrect name, check them all.
• Double check that there are no empty strings, e.g. label=”””” as this will cause the backstage to fail to render.

Finally, all that is required to do is wrap all of the code up in a couple of methods so that it will added automatically when the project is opened and to wire up the button.

Private Sub AddBackstageTab()

    Dim backstageXML As String

    backstageXML = "<customUI xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">"
    backstageXML = backstageXML + " <backstage>"
    backstageXML = backstageXML + "  <tab idMso=""TabInfo"">"
    backstageXML = backstageXML + "    <firstColumn>"
    backstageXML = backstageXML + "      <group id=""grpOne"" label=""Hello World Example"" helperText=""Click here for some Hello World Action"">"
    backstageXML = backstageXML + "        <primaryItem>"
    backstageXML = backstageXML + "          <button id=""firstButton"" label=""Hello World"" onAction=""HelloWorld""/>"
    backstageXML = backstageXML + "        </primaryItem>"
    backstageXML = backstageXML + "       </group>"
    backstageXML = backstageXML + "    </firstColumn>"
    backstageXML = backstageXML + "  </tab>"
    backstageXML = backstageXML + " </backstage>"
    backstageXML = backstageXML + "</customUI>"

    ActiveProject.SetCustomUI (backstageXML)

End Sub

Private Sub Project_Open(ByVal pj As Project)
        AddBackstageTab
End Sub

Private Sub HelloWorld()
    ' Do something
End Sub

In the next post, I will cover off how to incorporate the workflow data into the backstage.

In the first of three posts covering my proof of concept to show the workflow status in the Project backstage, I will be covering off how to access the workflow data itself from within the Project Server.

Now as this was a proof of concept, I decided to go with the simple approach of getting the information directly from the Reporting database instead of calling the Project Server PSI.  There were a couple of reasons for this, firstly simplicity, accessing the data in the Reporting database via SQL and VBA is much simpler in my opinion than calling the PSI. The second reason was it appeared that calling the PSI from the client requires custom code to be developed,  and my 2010 development environment didn’t have VS installed

Workflow information in the reporting database

In the 2010 Reporting database four new tables have been added to assist in reporting against the workflows.

MSP_EPMWorkflowPhase – a lookup table consisting of the workflow phases and their associated description

MSP_EPMWorkflowStage – a lookup table consisting of the workflow stages and their associated description

MSP_EpmWorkflowStatusType – a lookup table consisting the workflow status and it’s associated language

MSP_EPMWorkflowStatusInformation – Contains information about each project and it’s associated workflow status over time.

These tables provide the core information about where a project is in a workflow, including the project stage, status and date information of when it entered and left the stage. Unfortunately what they don’t provide is an indication of any associated approvals, which makes sense when you understand that the approvals are not part of Project Server per se, but rather the underlying SharePoint platform.

To extract the current workflow status of the project from the reporting database, I used the following query:

select  epmp.ProjectName,
epmwfs.StageName,
epmwfs.StageDescription,
epmwsi.StageInformation,
epmwfst.StageStateDescription
from MSP_EpmWorkflowStatusInformation epmwsi
inner join MSP_EpmWorkflowStage epmwfs on epmwsi.StageUID = epmwfs.StageUID
inner join MSP_EpmWorkflowStatusType epmwfst on epmwsi.StageStatus = epmwfst.StageStatusID
inner join MSP_EpmProject epmp on epmwsi.ProjectUID = epmp.ProjectUID
where epmwsi.ProjectUID = {Project GUID}
and (StageEntryDate is not null and StageCompletionDate is null)

The query takes the Project GUID as a parameter, and returns the currently in progress workflow stage as determined by the StageEntryDate and StageCompletionDate fields.

For the purposes of the backstage proof of concept, I was only interested in returning the core project workflow information, but the query could easily be extended to return other information such as the Enterprise Project Type and it’s associated description.

Now we have the workflow status information as retrieved from the Project Server Reporting database, in the next post, I will discuss how we extend the Project backstage and wire up the data we retrieved.

One of the significant changes in the Office 2010 client was the introduction of the backstage view. The way it has brought common commands front and centre and combined relevant supporting information has quickly become one of those features I can’t live without and certainly something I notice immediately when I use any 2007 Office client.

In Project 2010, this is particularly noticeable, with a number of the Enterprise features that were previously buried away in the jungle of menu’s prominent on the backstage, including items such as Project Publish, Organising the Enterprise Global and accepting Status updates from your team, which is a massive improvement in usability and functionality.

Whilst looking at the backstage, I started to think it would be a great way to present information about where the project was in its lifecycle as determined by the demand management* capabilities of Project Server 2010. At a very simplistic level demand management provides a framework for the collection and display of information through a number of web pages called Project Detail Pages (PDP’s) and workflows. Each of which can be customised in order to meet the specific requirements of the organisation. Demand management is a massive topic which I will be covering in a number of posts later on.

So I decided to set out and create a proof of concept to integrate demand management workflow data in to the Project backstage. During the process I learnt a fair bit about how to extend the backstage view in Project, VBA and how to get the relevant workflow data, which I am going to share in a series of posts.

* The demand management feature of Project Server has to be one of my most favourite features of the 2010 Server product. In fact I am so taken with it, I even went on record to say so