How to display Microsoft Project Task’s Notes field in a report
In MS Project you have the ability to add notes to each tasks in a project plan. This data is stored as an Image Data Type in the SQL database called TASK_RTF_NOTES (note that the field TASK_NOTES is a nvarchar of size 255, thus you’ll only get the first 255 characters displayed). So how can you render the entire note field using SQL Server Reporting Services? Before giving you the answer a special thank you to Sam Brooks from Microsoft for passing along this solution:
(SQL Reporting Services code sample attached at the bottom of this post)
T-SQL Query
SELECT ProjectName,TaskName,TRTF.TASK_RTF_NOTES
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P
ON P.ProjectUID=T.ProjectUID
INNER JOIN PWA_Published.dbo.MSP_TASKS AS TRTF
ON TRTF.TASK_UID=T.TaskUID
WHERE TaskIsProjectSummary=0
AND P.ProjectName='Task RTF' --- For debugging purposes REMOVE!!!!
ORDER BY P.ProjectName, T.TaskName
Note that the TASK_RTF_NOTES is not in the Project Server Reporting database, thus we need to get it from the Published database.
Report Code
- Add a reference to System.Windows.Forms (Report –> Report Properties –> References)
- Add the following function in the report’s code (Report –> Report Properties –> Code):
‘Instantiate a rich text box control in memory
Public rtfRTB As new System.Windows.Forms.RichTextBox
‘Instantiate a stringbuilder object
Public s As New System.Text.StringBuilder()
Public Function byteArrayToString(ByVal b() As Byte) As String
Dim i As Integer
dim mystr as string
on error goto errortrap
s.length = 0
For i = 0 To b.Length - 1
Console.WriteLine(chr(b(i)))
If i <> b.Length - 1 Then
s.Append(chr(b(i)))
End If
Next
mystr = left(s.ToString, len(s.ToString)-1)
rtfRTB.rtf = mystr
return rtfRTB.text
rtfRTB.clear
exit function
errortrap:
return "n/a"
rtfRTB.clear
s.length = 0
End Function
- For the TASK_RTF_NOTES field use the following Value:
=code.byteArrayToString(Fields!TASK_RTF_NOTES.Value)
And voila!
Another way to do the same thing is to use custom assemblies, check this blog post from Bryant Likes for more information: https://blogs.sqlxml.org/bryantlikes/pages/824.aspx