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:


image


(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)

image



  • 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)
image 
And voila! 
image 
Another way to do the same thing is to use custom assemblies, check this blog post from Bryant Likes for more information: http://blogs.sqlxml.org/bryantlikes/pages/824.aspx

Report TASK RTF NOTES.zip

Comments (6)

  1. bryant says:

    I thought we weren’t allow to query the published database. 😉

  2. MSDN Archive says:

    Chris,

    The above code produces below error on my SQL RS:

    Request for the permission of type ‘System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

    Any ideas how to fix this?

  3. You can clean the byte array -> string code up a bit by using the .NET System.Text class.

    As an example, I used the following script in a SQL Server Integration Services workflow to extract notes data

           If Not (Row.TASKRTFNOTES_IsNull) Then

               Dim dBytes As Byte() = Row.TASKRTFNOTES.GetBlobData(0, 999999)

               Dim strRTF As String

               Dim enc As New System.Text.ASCIIEncoding()

               strRTF = enc.GetString(dBytes)

               Dim rtfConverter As New System.Windows.Forms.RichTextBox

               rtfConverter.Rtf = strRTF

               Row.Notes = rtfConverter.Text

           Else

               Row.Notes_IsNull = True

           End If

    Thx for the starter code though! Saved a lot of work.

  4. nirtz123 says:

    I tried the code above and it is work great when i look on the report from Visual Studio 2005, but when i deploy it to reportserver and try to whatch it from the explorer it is not working, i figure that it is because it doesnt recognize the rtf function from the explorer,

    did anyone had similiar problem?

  5. bltwgs says:

    I am having the same problem as nirtz123.  It works great in Visual Studio.  After I publish up to the report server, all I get is the "n/a" returned from the errortrap.  

    Any help or least a point in the right direction would be much appreciated!

  6. Yves Z. says:

    It’s the same for me than for nirtz123 and bltwgs, it works in Visual Studio but not in the report server. Just the "n/a" appears 🙁

    When I publish the report I have noticed this message:

    "Warning : Cannot deploy data source PWA Reporting to the server because it already exists and OverwriteDataSources is not specified"

    As said above, any help or a point in the right direction would be much appreciated ! I really need this report to works.

    Thank you 🙂

Skip to main content