01 Oct Getting at Task Notes in Excel or other reports from Project Server

<thé>

As usual, most of my posts are inspired by customer requests (I don’t sit at home thinking up scenarios to blog about regardless of what my wife thinks!), and this one turned out to be surprisingly easy to resolve, even though I initially went about it the wrong way.

The request was fairly simple, to show the contents of any task notes fields in an Excel report, alongside other task data.  I know from past experience that the Notes field is not part of the reporting database, and that the information is held as binary data.  So this means accessing the published tables (in 2013) or the published database (in 2010).  The Notes field exists in the pub.MSP_TASKS.TASK_RTF_NOTES table.

Here’s a brief view of the select statement – please note that this search all rows so ideally you’d want to check whether the TASK_RTF_NOTES field is null or not.

SELECT        dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName, pub.MSP_TASKS.TASK_RTF_NOTES
FROM            dbo.MSP_EpmProject_UserView CROSS JOIN
dbo.MSP_EpmTask_UserView CROSS JOIN
pub.MSP_TASKS

Here’s what the output from the query looks like in Studio Manager, and you can see the <Binary Data> in the TASK_RTF_NOTES field

If I pull this data into Excel using a data connection, I get the Project Name and the Task Name, but not the Notes field (fair enough I think, it’s not a text field).

So, I dug around even more, and found the following VBA in the Project 2003 (yes I wrote it right, 2003) Data Reference file (PJDB.HTM) which details how to get at the notes.  Remember in 2003 you could save the file to a Microsoft Project Database file (MDB).  For completeness I’ve included the VBA here…

Sub getRtf()

'This macro extracts RTF data from MSP_TASKS.TASK_RTF_NOTES. This data can then be written

'to a file that can be opened with Microsoft Word or displayed in a richedit control.

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim sql As String, rtf As String, cnString

'Open the MSP_TASKS table to look for TASK_RTF_NOTES

cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyProject.mpd"

sql = "select PROJ_ID, TASK_UID, TASK_RTF_NOTES " & _ "from MSP_TASKS " & _ "where TASK_RTF_NOTES is not null"

'can specify a specific PROJ_ID and TASK_UID instead

cn.Open cnString

rs.Open sql, cn 'Enumerate across the recordset looking for notes

With rs Do While Not .EOF

  rtf = StrConv(.Fields("TASK_RTF_NOTES"), vbUnicode) ' Put binary column data into text string

  Debug.Print rtf

  .MoveNext

  Loop

  .Close

End With

End Sub

So what to do now, I didn’t want to implement a version of this in Excel.  After some consultations with colleagues and peers, we implemented an elegant solution which we should have thought about in the 1st place…

We created a Project Server Task Level ECF called Task Notes, with the formula = [Notes].  This brings in the 1st 255 characters of the notes field into the Excel report, and doesn’t break Microsoft’s policy of only accessing the reporting database.

And of course, when you view this field in Project, it shows 255 characters of the Notes field.  If the notes field contains not character items, then … is displayed as can be seen from the following screen shot.

If we now build the same sort of report as we did before in SQL Server Management Studio, we get the following…

and in Excel, it looks like this…

So, kudos must go to Brian Kennemer (http://www.projectified.com/) for his inspiration.

If you need notes in SSRS, then check out this blog – http://blogs.msdn.com/b/chrisfie/archive/2008/06/04/how-to-display-microsoft-project-task-s-notes-field-in-a-report.aspx

Enjoy,  Ben.