Extending the backstage… Integrating data and the UI

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.

ADODB Reference

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..
Current Project Workflow Status

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s