Extending the backstage… Integrating data and the UI
By blurg64
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:
[sourcecode language=”VB” wraplines=”false”]
Dim projectGUID As String
projectGUID = ActiveProject.GetServerProjectGuid
[/sourcecode]
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.
[sourcecode language=”vb” wraplines=”true”]
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
[/sourcecode]
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:
[sourcecode language=”vb” wraplines=”true”]
Private Sub AddWorkflowBackstageTab()
Dim backstageXML As String
backstageXML = “<customUI xmlns="“http://schemas.microsoft.com/office/2009/07/customui"">”
backstageXML = backstageXML + "
backstageXML = backstageXML + " <tab id="“TabWorkflow”” label="“Workflow”” insertAfterMso="“TabInfo”” >”
backstageXML = backstageXML + "
backstageXML = backstageXML + " <group id="“grpWorkflow”” label="“Current project workflow status”” style=””” + txtGroupStyle + “““>”
backstageXML = backstageXML + "
backstageXML = backstageXML + " <button id="“btnFeature”” label="“View workflow in PWA”” onAction="“workflowStatus”” imageMso="“ProjectWebAccessProjectDetails”"/>”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + "
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 + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + “”
ActiveProject.SetCustomUI (backstageXML)
End Sub
[/sourcecode]
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:
[sourcecode language=”vb” wraplines=”true”]
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 + "
backstageXML = backstageXML + " <tab id="“TabWorkflow”” label="“Workflow”” insertAfterMso="“TabInfo”” >”
backstageXML = backstageXML + "
backstageXML = backstageXML + " <group id="“grpWorkflow”” label="“Current project workflow status”” style=””” + txtGroupStyle + “““>”
backstageXML = backstageXML + "
backstageXML = backstageXML + " <button id="“btnFeature”” label="“View workflow in PWA”” onAction="“workflowStatus”” imageMso="“ProjectWebAccessProjectDetails”"/>”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + "
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 + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + " ”
backstageXML = backstageXML + “”
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
[/sourcecode]
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.